Query: load_balancer_associated_resources

with backend_address_pools as (
  select
    lb.id as lb_id,
    p.id as backend_address_id,
    p.backend_ip_configurations as backend_ip_configurations
  from
    azure_lb as lb,
    jsonb_array_elements(backend_address_pools) as b
    left join azure_lb_backend_address_pool as p on lower(p.id) = lower(b ->> 'id')
  where
    p.backend_ip_configurations is not null
    and lower(lb.id) = $1
    and lb.subscription_id = split_part($1, '/', 3)
),
backend_ip_configurations as (
  select
    lb_id,
    backend_address_id,
    c ->> 'id' as backend_ip_configuration_id
  from
    backend_address_pools,
    jsonb_array_elements(backend_ip_configurations) as c
),
network_interface as (
  select
    lb_id,
    backend_address_id,
    nic.id as nic_id,
    nic.virtual_machine_id as virtual_machine_id,
    c ->> 'id' as backend_ip_configuration_id
  from
    azure_network_interface as nic,
    jsonb_array_elements(ip_configurations) as c,
    backend_ip_configurations as b
  where
    c ->> 'id' = b.backend_ip_configuration_id
)

-- Compute Virtual Machine Scale Set
select
  distinct vm_scale_set.name as "Name",
  vm_scale_set.type as "Type",
  vm_scale_set.provisioning_state as "Provisioning State",
  vm_scale_set.id as "ID",
  '/azure_insights.dashboard.compute_virtual_machine_scale_set_detail?input.vm_scale_set_id=' || lower(vm_scale_set.id) as link
from
  azure_compute_virtual_machine_scale_set as vm_scale_set,
  jsonb_array_elements(virtual_machine_network_profile -> 'networkInterfaceConfigurations') as p,
  jsonb_array_elements(p -> 'properties' -> 'ipConfigurations') as c,
  jsonb_array_elements(c -> 'properties' -> 'loadBalancerBackendAddressPools') as b
where
  lower(split_part( b ->> 'id', '/backendAddressPools' , 1)) = $1

-- Compute Virtual Machine
union all
select
  name as "Name",
  type as "Type",
  provisioning_state as "Provisioning State",
  id as "ID",
  '/azure_insights.dashboard.compute_virtual_machine_detail?input.vm_id=' || lower(id) as link
from
  azure_compute_virtual_machine
where
  lower(id) in (select lower(virtual_machine_id) from network_interface)