Query: vpc_security_group_unrestricted_ingress_status

with ingress_sg as (
  select
    group_id
  from
    aws_vpc_security_group_rule
  where
    ( cidr_ipv4 = '0.0.0.0/0' or cidr_ipv6 = '::/0')
    and ip_protocol <> 'icmp'
    and (
      from_port = -1
      or (from_port = 0 and to_port = 65535)
    )
    and not is_egress
  group by
    group_id
)
select
 case when isg.group_id is null then 'restricted' else 'unrestricted' end as status,
 count(*)
from
  aws_vpc_security_group as sg left join ingress_sg as isg on sg.group_id = isg.group_id
group by
  status;