turbot/azure_insights

Query: compute_virtual_machines_for_network_load_balancer

Usage

powerpipe query azure_insights.query.compute_virtual_machines_for_network_load_balancer

SQL

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
),
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
)
select
lower(id) as virtual_machine_id
from
azure_compute_virtual_machine
where
lower(id) in (
select
lower(virtual_machine_id)
from
network_interface
)