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
      and subscription_id = split_part($1, '/', 3)
  ),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