Steampipe AWS One-Liners

Here are some useful Steampipe queries for AWS

Need to know how to install Steampipe – see my blog post here.

What is Steampipe AWS?

Turbot’s Steampipe AWS tools are great for understanding your Cloud and SaaS Infrastructure. You can ask any question about these platforms from users, assets, identity, access logs and even deployed assets. This makes it an effective way to understand everything about your Cloud

Steampipe uses an abstraction layer to give you access to your cloud providers and SaaS applications the same way you would with a database.

To use Steampipe, you first need to install some plugins. These plugins will allow Steampipe to interact with various cloud services and retrieve the data necessary for your query. A full list of available plugins can be found at https://steampipe.io/.

The Steampipe plugin can be installed on AWS using the command line. If it is already installed, you can update it by running the command again. After installation, the plugin will use default credentials stored in ~/.aws/credentials.

Want Instances Do I Have Running in AWS?

Use this simple query to learn what Instances are currently running in AWS

This SQL query is designed to retrieve information about Amazon EC2 (Elastic Compute Cloud) instances from the AWS (Amazon Web Services) environment using Steampipe.

select
  region,
  instance_id,
  instance_state,
  instance_type,
  title
from 
  aws_ec2_instance;

Let’s break down the query:

select Clause:

• region: This will retrieve the AWS region in which the EC2 instance is located.
• instance_id: This will retrieve the unique identifier for the EC2 instance.
• instance_state: This will retrieve the current state of the EC2 instance (e.g., running, stopped, terminated).
• instance_type: This will retrieve the type of the EC2 instance (e.g., t2.micro, m5.large). The instance type determines the hardware of the host computer used for the instance.
• title: This will retrieve the title or name of the EC2 instance, if it has been set.

from Clause:

• aws_ec2_instance: This indicates the table or data source from which the information is being retrieved. In the context of Steampipe, this isn’t a traditional database table but rather a representation of the EC2 instances in your AWS environment.

In summary, this query will provide a list of EC2 instances in your AWS environment, showing their region, unique ID, current state, instance type, and title (if set).

Find CIDRs Within CIDRs

This SQL query is designed to retrieve information about Amazon VPC (Virtual Private Cloud) subnets from the AWS environment using Steampipe, specifically focusing on subnets that fall within a specified CIDR block.

select
  title as subnet,
  cidr_block
from
  aws_vpc_subnet
where
  cidr_block <<= '<CIDR>';

select Clause:

• title as subnet: This retrieves the title or name of the VPC subnet and renames the column in the result set to “subnet”.
• cidr_block: This retrieves the CIDR block associated with the VPC subnet.

from Clause:

• aws_vpc_subnet: This indicates the table or data source from which the information is being retrieved. In the context of Steampipe, this isn’t a traditional database table but rather a representation of the VPC subnets in your AWS environment.

where Clause:

• cidr_block <<= '<CIDR>': This is a filter condition.
• The operator <<= is a containment operator in CIDR notation.
• It checks if the left-hand CIDR block (cidr_block from aws_vpc_subnet) is contained within or is equal to the right-hand CIDR block (<CIDR>).
• In simpler terms, it checks if the subnet’s CIDR block is a subset of or matches the specified <CIDR>.

List KMS Keys that are not automatically rotated

This SQL query is designed to retrieve information about AWS KMS (Key Management Service) keys, specifically focusing on keys that do not have key rotation enabled.

select
  id,
  key_rotation_enabled
from
  aws_kms_key
where
  not key_rotation_enabled;

Let’s break down the query:

select Clause:

• id: This retrieves the unique identifier of the KMS key.
• key_rotation_enabled: This retrieves the status of key rotation for the KMS key, indicating whether it’s enabled or not.

from Clause:

• aws_kms_key: This indicates the table or data source from which the information is being retrieved.

where Clause:

• not key_rotation_enabled: This is a filter condition. It checks for KMS keys where key rotation is not enabled. In other words, it filters out keys that have key rotation enabled and only returns those where it’s disabled.

EC2 Instance Encryption and Reservation Overview

Here’s a query that lists all EC2 instances, indicates whether they have encrypted EBS volumes, and checks if they’re part of reserved instances:

WITH encrypted_volumes AS (
  SELECT
    instance_id,
    COUNT(*) FILTER (WHERE encrypted) AS encrypted_volume_count,
    COUNT(*) AS total_volume_count
  FROM
    aws_ec2_instance
    JOIN aws_ebs_volume ON aws_ec2_instance.id = aws_ebs_volume.instance_id
  GROUP BY
    instance_id
),

reserved_instances AS (
  SELECT
    instance_id,
    COUNT(*) AS reserved_instance_count
  FROM
    aws_ec2_reserved_instance
  GROUP BY
    instance_id
)

SELECT
  e.instance_id,
  e.encrypted_volume_count,
  e.total_volume_count,
  COALESCE(r.reserved_instance_count, 0) AS reserved_instance_count,
  CASE
    WHEN e.encrypted_volume_count = e.total_volume_count THEN 'All Volumes Encrypted'
    ELSE 'Not All Volumes Encrypted'
  END AS encryption_status,
  CASE
    WHEN COALESCE(r.reserved_instance_count, 0) > 0 THEN 'Reserved Instance'
    ELSE 'On-Demand Instance'
  END AS instance_type
FROM
  encrypted_volumes e
  LEFT JOIN reserved_instances r ON e.instance_id = r.instance_id
ORDER BY
  e.instance_id;

This query does the following:

1. encrypted_volumes CTE: Counts the number of encrypted and total EBS volumes for each EC2 instance.
2. reserved_instances CTE: Counts the number of reserved instances for each EC2 instance.
3. Main Query: Combines the data from the two CTEs to provide a comprehensive view of each EC2 instance’s encryption and reservation status.

The result will show each EC2 instance ID, the count of its encrypted EBS volumes, its total EBS volumes, its reserved instance count, its encryption status, and its instance type (reserved or on-demand).

This query provides a holistic view of the security (through encryption) and cost-effectiveness (through reserved instances) of your EC2 instances.

ECS Cluster Health and Task Distribution Overview

Here’s a query that lists all ECS clusters, their associated services, and the task counts:

WITH service_task_counts AS (
  SELECT
    cluster_name,
    service_name,
    desired_count,
    running_count
  FROM
    aws_ecs_service
)

SELECT
  c.cluster_name,
  c.status,
  c.registered_container_instances_count,
  c.running_tasks_count,
  COALESCE(s.service_name, 'No Active Services') AS service_name,
  COALESCE(s.desired_count, 0) AS desired_task_count,
  COALESCE(s.running_count, 0) AS running_task_count,
  CASE
    WHEN COALESCE(s.desired_count, 0) = COALESCE(s.running_count, 0) THEN 'Match'
    ELSE 'Mismatch'
  END AS task_status
FROM
  aws_ecs_cluster c
  LEFT JOIN service_task_counts s ON c.cluster_name = s.cluster_name
ORDER BY
  c.cluster_name, s.service_name;

This query does the following:

1. service_task_counts CTE: Retrieves the desired and running task counts for each service in every ECS cluster.
2. Main Query: Combines the data from the CTE with the ECS cluster details to provide a comprehensive view of each cluster’s status, the services within it, and the task counts.

The result will show each ECS cluster’s name, its status, the number of registered container instances, the total running tasks, the associated services, the desired and running task counts for each service, and a status indicating if the desired and running task counts match.

This query provides a holistic view of the health and status of your ECS clusters, services, and tasks, ensuring that services are running as expected and tasks are adequately distributed.

Find S3 buckets that are publicly accessible:

select bucket from aws_s3_bucket where acl ->> 'allUsers' = 'READ' or acl ->> 'allUsers' = 'WRITE';

List all S3 buckets that don’t have versioning enabled:

select name from aws_s3_bucket where not versioning_enabled;