Query: ibm_is_security_group_inbound_rule_sankey

with associations as (
  select
    name,
    t ->> 'name' as resource_name,
    t ->> 'id' as resource_id,
    id as group_id,
    t ->> 'resource_type' as category
  from
    ibm_is_security_group,
    jsonb_array_elements(targets) as t
  where
    crn = $1
),
rules as (
  select
    concat(
      (r -> 'remote' ->> 'cidr_block'),
      (r -> 'remote' ->> 'name'),
      (r -> 'remote' ->> 'address')
    ) as source,
    id as group_id,
    name as group_name,
    case
      when r ->> 'protocol' = 'all' then 'All Traffic'
      when r ->> 'protocol' = 'icmp' then 'All ICMP'
      when r ->> 'protocol' = 'udp'
      and r ->> 'port_min' = '1'
      and r ->> 'port_max' = '65535' then 'All UDP'
      when r ->> 'protocol' = 'tcp'
      and r ->> 'port_min' = '1'
      and r ->> 'port_max' = '65535' then 'All TCP'
      when r ->> 'port_min' is not null
      and r ->> 'port_max' is not null
      and r ->> 'port_min' = r ->> 'port_max' then concat((r ->> 'port_min'), '/', (r ->> 'protocol'))
      else concat(
        (r ->> 'port_min'),
        '-',
        (r ->> 'port_max'),
        '/',
        (r ->> 'protocol')
      )
    end as port_proto,
    case
      when (r -> 'remote' ->> 'cidr_block' = '0.0.0.0/0')
      and r ->> 'protocol' <> 'icmp'
      and (
        (r ->> 'port_min') = '1'
        and (r ->> 'port_max') = '65535'
      ) then 'alert'
      else 'ok'
    end as category
  from
    ibm_is_security_group,
    jsonb_array_elements(rules) as r
  where
    crn = $1
    and r ->> 'direction' = 'inbound'
) -- Nodes  ---------
select
  distinct concat('src_', source) as id,
  source 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 group_id as id,
  group_name as title,
  2 as depth,
  'security_group' as category,
  null as from_id,
  null as to_id
from
  rules
union
select
  distinct resource_id as id,
  resource_name || '(' || category || ')' as title,
  3 as depth,
  category,
  group_id as from_id,
  null as to_id
from
  associations -- Edges  ---------
union
select
  null as id,
  null as title,
  null as depth,
  category,
  concat('src_', source) 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,
  group_id as to_id
from
  rules