Query: network_security_group_ingress_rule_sankey

with associations as (
  -- NICs
  select
    ni.title as title,
    'nsg' as category,
    ni.id as id,
    nsg.id as nsg_id
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(nsg.network_interfaces) as nic
    left join azure_network_interface as ni on lower(ni.id) = lower(nic ->> 'id')
  where
    lower(nsg.id) = $1 -- Subnets
  union
  select
    s.title as title,
    'subnet' as category,
    s.id as id,
    nsg.id as nsg_id
  from
    azure_network_security_group as nsg,
    jsonb_array_elements(nsg.subnets) as subnets
    left join azure_subnet as s on lower(s.id) = lower(subnets ->> 'id')
  where
    lower(nsg.id) = $1
),
rules as (
  select
    sip as cidr_block,
    id,
    case
      when (r -> 'properties' ->> 'protocol' = '*') then 'All Traffic'
      when (r -> 'properties' ->> 'protocol' = 'icmp') then 'All ICMP'
      when sport is not null
      and dport is not null
      and sport = dport then concat(sport, '/', r -> 'properties' ->> 'protocol')
      else concat(sport, '-', dport, '/', r -> 'properties' ->> 'protocol')
    end as port_proto,
    type,
    case
      when sip in (
        '*',
        '0.0.0.0',
        '0.0.0.0/0',
        'Internet',
        'any',
        '<nw>/0',
        '/0'
      )
      and (r -> 'properties' ->> 'protocol') <> 'icmp'
      and (
        sport = '*'
        or (
          sport :: integer = 0
          and dport :: integer = 65535
        )
      ) then 'alert'
      else 'ok'
    end as category
  from
    azure_network_security_group,
    jsonb_array_elements(default_security_rules || security_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,
    jsonb_array_elements_text(
      case
        when jsonb_array_length(r -> 'properties' -> 'sourcePortRanges') > 0 then (r -> 'properties' -> 'sourcePortRanges')
        else jsonb_build_array(r -> 'properties' -> 'sourcePortRange')
      end
    ) as sport
  where
    r -> 'properties' ->> 'direction' = 'Inbound'
    and lower(id) = $1
) -- Nodes  ---------
select
  distinct concat('src_', cidr_block) as id,
  cidr_block as title,
  0 as depth,
  'source' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct port_proto as id,
  port_proto as title,
  1 as depth,
  'port_proto' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct id as id,
  category || '/' || title as title,
  3 as depth,
  category,
  trim((split_part(nsg_id, '/', 9)), '""') as from_id,
  null as to_id
from
  associations -- Edges  ---------
union
select
  null as id,
  null as title,
  null as depth,
  category,
  concat('src_', cidr_block) as from_id,
  port_proto as to_id
from
  rules
union
select
  null as id,
  null as title,
  null as depth,
  category,
  port_proto as from_id,
  trim((split_part(id, '/', 9)), '""') as to_id
from
  rules