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 on 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.
Let’s break down the query:
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.
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.
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.
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.
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 (
aws_vpc_subnet) is contained within or is equal to the right-hand CIDR block (
- In simpler terms, it checks if the subnet’s CIDR block is a subset of or matches the specified
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.
Let’s break down the query:
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.
aws_kms_key: This indicates the table or data source from which the information is being retrieved.
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:
This query does the following:
- encrypted_volumes CTE: Counts the number of encrypted and total EBS volumes for each EC2 instance.
- reserved_instances CTE: Counts the number of reserved instances for each EC2 instance.
- 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:
This query does the following:
- service_task_counts CTE: Retrieves the desired and running task counts for each service in every ECS cluster.
- 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.