turbot/azure_insights

Query: network_subnet_association

Usage

powerpipe query azure_insights.query.network_subnet_association

SQL

-- API Management
select
title as "Title",
type as "Type",
id as "ID",
null as link
from
azure_api_management
where
lower(virtual_network_configuration_subnet_resource_id) = $1 -- CosmosDB Account
union all
select
title as "Title",
type as "Type",
id as "ID",
null as link
from
azure_cosmosdb_account,
jsonb_array_elements(virtual_network_rules) as r
where
lower(r ->> 'id') = $1 -- Firewall
union all
select
title as "Title",
type as "Type",
id as "ID",
'/azure_insights.dashboard.network_firewall_detail?input.firewall_id=' || lower(id) as link
from
azure_firewall,
jsonb_array_elements(ip_configurations) as c
where
lower(c -> 'subnet' ->> 'id') = $1 -- Storage Account
union all
select
title as "Title",
type as "Type",
id as "ID",
'/azure_insights.dashboard.storage_account_detail?input.storage_account_id=' || lower(id) as link
from
azure_storage_account,
jsonb_array_elements(virtual_network_rules) as r
where
lower(r ->> 'id') = $1 -- SQL Server
union all
select
title as "Title",
type as "Type",
id as "ID",
'/azure_insights.dashboard.sql_server_detail?input.server_id=' || lower(id) as link
from
azure_sql_server,
jsonb_array_elements(virtual_network_rules) as r
where
lower(r -> 'properties' ->> 'virtualNetworkSubnetId') = $1 -- AppServcie Web App
union all
select
title as "Title",
type as "Type",
id as "ID",
'/azure_insights.dashboard.app_service_web_app_detail?input.web_app_id=' || lower(id) as link
from
azure_app_service_web_app
where
lower(vnet_connection -> 'properties' ->> 'vnetResourceId') = $1 -- Application Gateway
union all
select
title as "Title",
type as "Type",
id as "ID",
null as link
from
azure_application_gateway,
jsonb_array_elements(gateway_ip_configurations) as c
where
lower(c -> 'properties' -> 'subnet' ->> 'id') = $1 -- Network Security Groups
union all
select
title as "Title",
type as "Type",
id as "ID",
'/azure_insights.dashboard.network_security_group_detail?input.nsg_id=' || lower(id) as link
from
azure_network_security_group as nsg,
jsonb_array_elements(nsg.subnets) as sub
where
lower(sub ->> 'id') = $1 -- Route Tables
union all
select
title as "Title",
type as "Type",
id as "ID",
null as link
from
azure_route_table as r,
jsonb_array_elements(r.subnets) as sub
where
lower(sub ->> 'id') = $1;

Dashboards

The query is used in the dashboards: