Query: network_subnet_association

-- 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;