Query: vpc_security_group_unrestricted_egress_status

with egress_sg as (
  select
    group_id,
    count(*)
  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 is_egress
  group by
    group_id
)
select
  case
    when esg.group_id is null then 'restricted'
    else 'unrestricted'
  end as status,
  count(*)
from
  aws_vpc_security_group as sg
  left join egress_sg as esg on sg.group_id = esg.group_id
group by
  status;