turbot/azure_insights

Query: network_application_gateways_for_app_service_web

Usage

powerpipe query azure_insights.query.network_application_gateways_for_app_service_web

SQL

with application_gateway as (
select
g.id as id,
backend_address ->> 'fqdn' as app_host_name
from
azure_application_gateway as g,
jsonb_array_elements(backend_address_pools) as pool,
jsonb_array_elements(pool -> 'properties' -> 'backendAddresses') as backend_address
)
select
lower(g.id) as application_gateway_id
from
azure_app_service_web_app as a,
jsonb_array_elements(a.host_names) as host_name,
application_gateway as g
where
lower(g.app_host_name) = lower(trim((host_name :: text), '""'))
and lower(a.id) = $1;