Table: aws_ec2_instances

WITH
  data
    AS (
      SELECT
        account_id, instance_id, count(nics->>'Status') AS cnt
      FROM
        aws_ec2_instances
        LEFT JOIN jsonb_array_elements(aws_ec2_instances.network_interfaces)
            AS nics ON true
      GROUP BY
        account_id, instance_id
    )
SELECT
  'EC2 instances should not use multiple ENIs' AS title,
  account_id,
  instance_id AS resource_id,
  CASE WHEN cnt > 1 THEN 'fail' ELSE 'pass' END AS status
FROM
  data;

SELECT
  'EC2 instances should not have a public IP address' AS title,
  account_id,
  instance_id AS resource_id,
  CASE WHEN public_ip_address IS NOT NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_ec2_instances;

WITH
  interface_groups
    AS (
      SELECT
        DISTINCT g->>'GroupId' AS id
      FROM
        aws_ec2_instances AS i,
        jsonb_array_elements(network_interfaces) AS a,
        jsonb_array_elements(a->'Groups') AS g
    )
SELECT
  'security group is not currently in use so it should be deleted' AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN interface_groups.id IS NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_ec2_security_groups
  LEFT JOIN interface_groups ON
      aws_ec2_security_groups.group_id = interface_groups.id;

SELECT
  'EC2 instances should use IMDSv2' AS title,
  account_id,
  instance_id AS resource_id,
  CASE
  WHEN metadata_options->>'HttpTokens' IS DISTINCT FROM 'required' THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_ec2_instances;

-- Find all AWS instances that are in a subnet that includes a catchall route
SELECT
  'Find all ec2 instances that have unrestricted access to the internet with a wide open security group and routing'
    AS title,
  account_id,
  instance_id AS resource_id,
  'fail' AS status
FROM
  aws_ec2_instances
WHERE
  subnet_id
  IN (
      SELECT
        a->>'SubnetId'
      FROM
        aws_ec2_route_tables AS t,
        jsonb_array_elements(t.associations) AS a,
        jsonb_array_elements(t.routes) AS r
      WHERE
        r->>'DestinationCidrBlock' = '0.0.0.0/0'
        OR r->>'DestinationIpv6CidrBlock' = '::/0'
    )
  AND instance_id
    IN (
        SELECT
          instance_id
        FROM
          aws_ec2_instances,
          jsonb_array_elements(security_groups) AS sg
          INNER JOIN view_aws_security_group_egress_rules ON id = sg->>'GroupId'
        WHERE
          ip = '0.0.0.0/0' OR ip6 = '::/0'
      );

-- Find all AWS instances that have a security group that allows unrestricted egress
SELECT
  'All ec2 instances that have unrestricted access to the internet via a security group'
    AS title,
  aws_ec2_instances.account_id,
  instance_id AS resource_id,
  'fail' AS status
FROM
  aws_ec2_instances,
  jsonb_array_elements(security_groups) AS sg
  INNER JOIN view_aws_security_group_egress_rules ON id = sg->>'GroupId'
WHERE
  ip = '0.0.0.0/0' OR ip6 = '::/0';

SELECT
  'Find all instances with a public IP address' AS title,
  account_id,
  arn AS resource_id,
  CASE WHEN public_ip_address IS NOT NULL THEN 'fail' ELSE 'pass' END AS status
FROM
  aws_ec2_instances;

WITH
  interface_groups
    AS (
      SELECT
        DISTINCT a->>'GroupId' AS group_id
      FROM
        aws_ec2_instances, jsonb_array_elements(security_groups) AS a
    )
SELECT
  'Unused EC2 security group' AS title,
  sg.account_id,
  sg.arn AS resource_id,
  'fail' AS status
FROM
  aws_ec2_security_groups AS sg
  LEFT JOIN interface_groups ON interface_groups.group_id = sg.group_id
WHERE
  interface_groups.group_id IS NULL;

SELECT
  'Stopped EC2 instances should be removed after a specified time period'
    AS title,
  account_id,
  instance_id AS resource_id,
  CASE
  WHEN state->>'Name' = 'stopped'
  AND now() - state_transition_reason_time > '30'::INTERVAL DAY
  THEN 'fail'
  ELSE 'pass'
  END
FROM
  aws_ec2_instances;

SELECT
  'Amazon EC2 instances should be managed by AWS Systems Manager' AS title,
  aws_ec2_instances.account_id,
  aws_ec2_instances.arn AS resource_id,
  CASE
  WHEN aws_ssm_instances.instance_id IS NULL THEN 'fail'
  ELSE 'pass'
  END
    AS status
FROM
  aws_ec2_instances
  LEFT JOIN aws_ssm_instances ON
      aws_ec2_instances.instance_id = aws_ssm_instances.instance_id;