turbot/aws_insights

Query: vpc_security_group_unassociated_count

Usage

powerpipe query aws_insights.query.vpc_security_group_unassociated_count

SQL

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;

Dashboards

The query is used in the dashboards: