Query: ibm_is_security_group_outbound_rule_sankey

 with associations as (
   select
     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' = 'outbound'
       )

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

   select
     distinct concat('src_',source) as id,
     source 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 group_id as id,
     group_name as title,
     1 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,
     0 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