turbot/azure_insights

Query: network_application_gateways_for_compute_virtual_machine_scale_set

Usage

powerpipe query azure_insights.query.network_application_gateways_for_compute_virtual_machine_scale_set

SQL

with application_gateway_backend_address_pool as (
select
lower(b ->> 'id') as backend_address_pool_id
from
azure_compute_virtual_machine_scale_set as s,
jsonb_array_elements(
virtual_machine_network_profile -> 'networkInterfaceConfigurations'
) as p,
jsonb_array_elements(p -> 'properties' -> 'ipConfigurations') as c,
jsonb_array_elements(
c -> 'properties' -> 'applicationGatewayBackendAddressPools'
) as b
where
lower(s.id) = $1
)
select
lower(g.id) as application_gateway_id
from
azure_application_gateway as g,
jsonb_array_elements(backend_address_pools) as p
left join application_gateway_backend_address_pool as pool on lower(pool.backend_address_pool_id) = lower(p ->> 'id')
where
pool.backend_address_pool_id is not null