Control: Network Load Balancer with Non-existent Backend

powerpipe control run azure_thrifty.control.network_load_balancer_with_nonexistent_backend

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

with valid_backend_pools as (
  select distinct id from azure_lb_backend_address_pool
)
select
  r.id as resource,
  case
    when r.backend_address_pool_id is not null and not exists (
      select 1 from valid_backend_pools 
      where id = r.backend_address_pool_id
    ) then 'alarm'
    else 'ok'
  end as status,
  case
    when r.backend_address_pool_id is not null and not exists (
      select 1 from valid_backend_pools 
      where id = r.backend_address_pool_id
    ) then r.name || ' in load balancer ' || r.load_balancer_name || ' references non-existent backend pool.'
    else r.name || ' references valid backend pool.'
  end as reason
  
  , r.resource_group
  , display_name as subscription
from
  azure_lb_rule as r,
  azure_subscription as sub
where
  sub.subscription_id = r.subscription_id;