Query: network_security_group_egress_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' = 'Outbound'
      and lower(id) = $1
      )

    -- Nodes  ---------

  select
    distinct concat('src_',cidr_block) as id,
    cidr_block as title,
    3 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,
    2 as depth,
    'port_proto' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
    distinct trim((split_part(nsg.id, '/', 9)), '""')  as id,
    trim((split_part(nsg.id, '/', 9)), '""') as title,
    1 as depth,
    'network_security_group' as category,
    null as from_id,
    null as to_id
  from
    azure_network_security_group as nsg
    inner join rules as r on lower(nsg.id) = lower(r.id)

  union
  select
      distinct id as id,
      category || '/' || title as title,
      0 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