turbot/aws_insights

Query: vpc_security_group_unassociated_status

Usage

powerpipe query aws_insights.query.vpc_security_group_unassociated_status

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
),
sg_list as (
select
s.group_id,
case
when a.sg_id is null then false
else true
end as is_associated
from
aws_vpc_security_group as s
left join associated_sg a on s.group_id = a.sg_id
)
select
case
when is_associated then 'associated'
else 'unassociated'
end as sg_association_status,
count(*)
from
sg_list
group by is_associated;

Dashboards

The query is used in the dashboards: