turbot/aws_compliance

Query: vpc_security_group_associated_to_eni

Usage

powerpipe query aws_compliance.query.vpc_security_group_associated_to_eni

SQL

with associated_sg as (
select
count(sg ->> 'GroupId'),
sg ->> 'GroupId' as secgrp_id
from
aws_ec2_network_interface,
jsonb_array_elements(groups) as sg
group by sg ->> 'GroupId'
)
select
distinct s.arn as resource,
case
when a.secgrp_id = s.group_id then 'ok'
else 'alarm'
end as status,
case
when a.secgrp_id = s.group_id then s.title || ' is associated with ' || a.count || ' ENI(s).'
else s.title || ' not associated to any ENI.'
end as reason
, region, account_id
from
aws_vpc_security_group as s
left join associated_sg as a on s.group_id = a.secgrp_id;

Controls

The query is being used by the following controls: