Query: vpc_security_group_unassociated_count

with associated_sg as (
  select
    sg ->> 'GroupId' as sg_id,
    sg ->> 'GroupName' as sg_name
  from
    aws_ec2_network_interface,
    jsonb_array_elements(groups) as sg
)
select
  count(*) as value,
  'Unassociated' as label,
  case
    count(*)
    when 0 then 'ok'
    else 'alert'
  end as type
from
  aws_vpc_security_group s
  left join associated_sg a on s.group_id = a.sg_id
where
  a.sg_id is null;