Control: Unused EC2 security groups should be removed

Description

This AWS control checks that security groups are attached to AWS Elastic Compute Cloud (AWS EC2) instances or to an elastic network interface. The control will fail if the security group is not associated with an AWS EC2 instance or an elastic network interface.

Usage

Run the control in your terminal:

powerpipe control run aws_compliance.control.vpc_security_group_unused

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run aws_compliance.control.vpc_security_group_unused --share

SQL

This control uses a named query:

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

Control: Unused EC2 security groups should be removed

Description

Usage

SQL

Tags