Query: virtual_network_ingress_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",
    name as vnet_name
  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,
    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