Control: Network Load Balancer with Duplicate Rules

powerpipe control run azure_thrifty.control.network_load_balancer_with_duplicate_rules

powerpipe login
powerpipe control run azure_thrifty.control.network_load_balancer_with_duplicate_rules --share

with duplicate_rules as (
  select
    frontend_ip_configuration_id,
    frontend_port,
    protocol,
    count(*) as rule_count
  from
    azure_lb_rule
  group by
    frontend_ip_configuration_id,
    frontend_port,
    protocol
  having
    count(*) > 1
)
select
  r.id as resource,
  case
    when dr.rule_count is not null then 'alarm'
    else 'ok'
  end as status,
  case
    when dr.rule_count is not null then r.name || ' in load balancer ' || r.load_balancer_name || ' has duplicate frontend configuration (Port: ' || r.frontend_port || ', Protocol: ' || r.protocol || ').'
    else r.name || ' has unique frontend configuration.'
  end as reason,
  r.resource_group,
  display_name as subscription
from
  azure_lb_rule as r
  left join duplicate_rules as dr on r.frontend_ip_configuration_id = dr.frontend_ip_configuration_id
  and r.frontend_port = dr.frontend_port
  and r.protocol = dr.protocol,
  azure_subscription as sub
where
  sub.subscription_id = r.subscription_id;