turbot/azure_insights

Query: network_application_gateways_for_compute_virtual_machine

Usage

powerpipe query azure_insights.query.network_application_gateways_for_compute_virtual_machine

SQL

with network_interface as (
select
vm.id,
nic.id,
nic.ip_configurations as ip_configurations
from
azure_compute_virtual_machine as vm,
jsonb_array_elements(network_interfaces) as n
left join azure_network_interface as nic on nic.id = n ->> 'id'
where
lower(vm.id) = $1
),
vm_application_gateway_backend_address_pool as (
select
p ->> 'id' as id
from
network_interface,
jsonb_array_elements(ip_configurations) as i,
jsonb_array_elements(
i -> 'properties' -> 'applicationGatewayBackendAddressPools'
) as p
)
select
lower(g.id) as application_gateway_id
from
azure_application_gateway as g,
jsonb_array_elements(backend_address_pools) as p
where
lower(p ->> 'id') in (
select
lower(id)
from
vm_application_gateway_backend_address_pool
);