Query: ibm_is_security_group_unrestricted_inbound_status

with inbound_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' = 'inbound'
    group by
      id
)
select
 case when i.id is null then 'restricted' else 'unrestricted' end as status,
 count(*)
from
  ibm_is_security_group as sg left join inbound_sg as i on sg.id = i.id
group by
  status;