Query: ibm_is_security_group_unrestricted_outbound_count

with outbound_sg as (
  select
    id,
    count(*)
  from
    ibm_is_security_group,
    jsonb_array_elements(rules) as r
  where
    (r -> 'remote' ->> 'cidr_block' = '0.0.0.0/0')
    and r ->> 'protocol' <> 'icmp'
    and (
      r ->> 'port_min' = '1'
      and r ->> 'port_max' = '65535'
    )
    and r ->> 'direction' = 'outbound'
  group by
    id
)
select
  'Unrestricted Outbound (Excludes ICMP)' as label,
  count(*) as value,
  case
    when count(*) = 0 then 'ok'
    else 'alert'
  end as type
from
  ibm_is_security_group as sg
where
  sg.id in (
    select
      id
    from
      outbound_sg
  );