Query: virtual_network_egress_rule_sankey

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"
  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
  from
    subnets
    left join network_security_group on network_security_group.s ->> 'id' = subnets.subnet_id
),
data as (
  select
    subnet_name :: text,
    subnet_id,
    nsgid,
    addressPrefix,
    sip as cidr_block,
    r -> 'properties' -> 'priority' as rule_priority,
    r ->> 'name' as rule_name,
    r -> 'properties' ->> 'access' as rule_action,
    to_char((r -> 'properties' -> 'priority') :: numeric, 'fm00000') as priority_padded,
    case
      when r -> 'properties' ->> 'access' = 'Allow' then 'Allow '
      else 'Deny '
    end || case
      when (r -> 'properties' ->> 'protocol' = '*') then 'All Traffic'
      when (r -> 'properties' ->> 'protocol' = 'UDP') then 'All UDP'
      when (r -> 'properties' ->> 'protocol' = 'ICMP') then 'All ICMP'
      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(
      case
        when jsonb_array_length(r -> 'properties' -> 'destinationPortRanges') > 0 then (r -> 'properties' -> 'destinationPortRanges')
        else jsonb_build_array(r -> 'properties' -> 'destinationPortRange')
      end
    ) as dport,
    jsonb_array_elements_text(
      case
        when jsonb_array_length(r -> 'properties' -> 'sourceAddressPrefixes') > 0 then (r -> 'properties' -> 'sourceAddressPrefixes')
        else jsonb_build_array(r -> 'properties' -> 'sourceAddressPrefix')
      end
    ) as sip
  where
    r -> 'properties' ->> 'direction' = 'Outbound'
) -- Subnet Nodes
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,
  'vswitch' as category,
  null as from_id,
  null as to_id,
  0 as depth
from
  data -- ACL Nodes
union
select
  distinct 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,
  1 as depth
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,
  2 as depth
from
  data -- CIDR Nodes
union
select
  distinct cidr_block as id,
  cidr_block as title,
  'cidr_block' as category,
  null as from_id,
  null as to_id,
  3 as depth
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,
  null as depth
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,
  null as depth
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,
  null as depth
from
  data