turbot/azure_insights

Query: virtual_network_ingress_rule_sankey

Usage

powerpipe query azure_insights.query.virtual_network_ingress_rule_sankey

SQL

with subnets as (
select
s ->> 'name' as "subnet_name",
s -> 'properties' ->> 'addressPrefix' as addressPrefix,
s ->> 'id' as "subnet_id",
s -> 'properties' -> 'networkSecurityGroup' ->> 'id' as "networkSecurityGroup",
name as vnet_name
from
azure_virtual_network,
jsonb_array_elements(subnets) as s
where
lower(id) = $1
),
network_security_group as (
select
id,
default_security_rules,
security_rules,
s
from
azure_network_security_group as sg,
jsonb_array_elements(subnets) as s
),
network_security_group_rule as (
select
network_security_group.id as nsgid,
(default_security_rules || security_rules) as all_rules,
subnets.subnet_id as "subnet_id",
subnets.subnet_name as "subnet_name",
subnets.addressPrefix as addressPrefix,
subnets.vnet_name as vnet_name
from
subnets
left join network_security_group on network_security_group.s ->> 'id' = subnets.subnet_id
),
data as (
select
subnet_name,
subnet_id,
nsgid,
vnet_name,
addressPrefix,
sip as cidr_block,
r -> 'properties' -> 'priority' as rule_priority,
r ->> 'name' as rule_name,
r -> 'properties' ->> 'access' as rule_action,
case
when r -> 'properties' ->> 'access' = 'Allow' then 'Allow '
else 'Deny '
end || case
when (r -> 'properties' ->> 'protocol' = '*') then 'All Traffic'
when (r -> 'properties' ->> 'protocol' = 'ICMP') then 'All ICMP'
when (r -> 'properties' ->> 'protocol' = 'UDP') then 'All UDP'
when (r -> 'properties' ->> 'protocol' = 'TCP')
and (
dport in ('22', '3389', '*')
or (
dport like '%-%'
and (
(
split_part(dport, '-', 1) :: integer <= 3389
and split_part(dport, '-', 2) :: integer >= 3389
)
or (
split_part(dport, '-', 1) :: integer <= 22
and split_part(dport, '-', 2) :: integer >= 22
)
)
)
) then 'All TCP'
else concat('Procotol: ', r -> 'properties' ->> 'protocol')
end as rule_description
from
network_security_group_rule,
jsonb_array_elements(all_rules) as r,
jsonb_array_elements_text(
r -> 'properties' -> 'sourceAddressPrefixes' || (r -> 'properties' -> 'sourceAddressPrefix') :: jsonb
) as sip,
jsonb_array_elements_text(
r -> 'properties' -> 'destinationPortRanges' || (r -> 'properties' -> 'destinationPortRange') :: jsonb
) dport
where
r -> 'properties' ->> 'direction' = 'Inbound'
) -- CIDR Nodes
select
distinct cidr_block as id,
cidr_block as title,
'cidr_block' as category,
null as from_id,
null as to_id
from
data -- Rule Nodes
union
select
concat(
trim((split_part(nsgid, '/', 9)), '""'),
'_',
rule_name
) as id,
concat(rule_priority, ': ', rule_description) as title,
'rule' as category,
null as from_id,
null as to_id
from
data -- NSG Nodes
union
select
distinct concat (trim((split_part(nsgid, '/', 9)), '""')) as id,
concat (trim((split_part(nsgid, '/', 9)), '""')) as title,
'nsg' as category,
null as from_id,
null as to_id
from
data -- Subnet Nodes
union
select
distinct split_part(subnet_id, '/', 10) || '/' || trim((split_part(subnet_id, '/', 11)), '""') as id,
split_part(subnet_id, '/', 10) || '/' || trim((split_part(subnet_id, '/', 11)), '""') as title,
'subnet' as category,
null as from_id,
null as to_id
from
data -- ip -> rule edge
union
select
null as id,
null as title,
rule_action as category,
cidr_block as from_id,
concat(
trim((split_part(nsgid, '/', 9)), '""'),
'_',
rule_name
) as to_id
from
data -- rule -> NSG edge
union
select
null as id,
null as title,
rule_action as category,
concat(
trim((split_part(nsgid, '/', 9)), '""'),
'_',
rule_name
) as from_id,
concat (trim((split_part(nsgid, '/', 9)), '""')) as to_id
from
data -- nsg -> subnet edge
union
select
null as id,
null as title,
'attached' as category,
concat (trim((split_part(nsgid, '/', 9)), '""')) as from_id,
split_part(subnet_id, '/', 10) || '/' || trim((split_part(subnet_id, '/', 11)), '""') as to_id
from
data

Dashboards

The query is used in the dashboards: