Simplify SQL queries to your AWS API operations using Steampipe and AWS plugin

Steampipe, an open-source, zero-ETL tool built by AWS Partner Turbot, is a welcome addition for DevOps practitioners who use SQL to query their AWS Cloud API operations. Instead of writing and managing scripts, now you can use Steampipe’s AWS plugin to perform SQL queries. This helps you gather API data directly from Steampipe or any Postgres or SQLite database. The standalone nature of the AWS plugin combined with the “no data storage needed” nature of zero-ETL helps you optimize costs, identify security misconfigurations, and export data—all in a fraction of the time required with manual scripting operations.

Steampipe offers these key features:

• Direct API queries for translating API responses into SQL tables, providing real-time data access.
• Support for multiple databases, including extensions for both PostgreSQL and SQLite.
• An extensive set of plugins with over 450 predefined tables covering a wide range of AWS APIs.
• A standalone command line interface (CLI) tool that extracts data from AWS APIs without needing a database.

In this blog post, we introduce you to a set of code examples that show you how to query your AWS APIs. We include queries about monitoring your AWS resources with PostgreSQL, integrating AWS data with SQLite, and using Steampipe’s CLI tool for exporting AWS data.

Prerequisites

To get started with Steampipe for your AWS environment, follow these steps:

1. Install the Steampipe engine.
2. Run the following command to download the latest AWS plugin from the Steampipe Hub registry:  $ steampipe plugin install aws. For more information, see Installing Plugins.
3. Configure one or more connections using your preferred type of credentials. For more information, see Configuring AWS Credentials.

Practical examples

Example 1: Monitoring AWS resources with PostgreSQL

A. Use Steampipe to monitor Amazon EC2 instances, gather instance types, and public IP addresses without setting up a separate monitoring tool.

```sql
select instance_id, instance_type, state, public_ip_address
from aws_ec2_instance
where state="running";
```

B. Automate security checks by querying security group configurations.

```sql
select group_name, description, inbound_rules
from aws_security_group
where vpc_id = 'YOUR-VPC-ID';
```

C. Find out which Amazon RDS instances are not authenticated using IAM.

```sql
select db_instance_identifier, iam_database_authentication_enabled
from aws_rds_db_instance
where not iam_database_authentication_enabled;
```

D. Query AWS data from multiple accounts and regions.

```sql
connection "aws_dev" {
plugin  = "aws"
profile = "aws_dev"
regions = ["us-east-1", "us-west-2"]
}

connection "aws_prod" {
plugin  = "aws"
profile = "aws_prod"
regions = ["us-*"]
}

connection "aws_all" {
plugin      = "aws"
type        = "aggregator"
connections = ["aws_dev", "aws_prod"]
}

select
*
from
aws_all.aws_account
```

As Figure 1 shows, the Steampipe Foreign Data Wrapper (FDW) layer uses its multi-threaded core to fetch results from all connections in parallel.  It then immediately streams back the results. Caching is also enabled in the architecture. For example, running an aggregate query preloads the cache for subsequent multi-connection queries and for each individual connection.

Figure 1: Steampipe Foreign Data Wrapper layer

Example 2: Integrating AWS data with SQLite

A. Query AWS resources using SQLite for lightweight, portable data analysis, for example to list EC2 instances and their associated security groups.

```sql
select ec2.instance_id, ec2.instance_type, sg.group_name, sg.description
from aws_ec2_instance ec2
join aws_security_group sg on ec2.vpc_id = sg.vpc_id;
```

B. Monitor Amazon S3 bucket activity.

```sql
select bucket_name, event_time, event_name
from aws_s3_bucket_activity
where event_time > '2024-01-01';
```

Example 3: Using Steampipe’s CLI tool for exporting AWS data

A. Extract data from AWS APIs without needing a database. For example, you can export a list of Amazon EC2 instances to a CSV file.

```bash
steampipe_export_aws aws_ec2_instance > ec2_instances.csv
```

B. Generate a JSON file of Amazon S3 bucket details.

```bash
steampipe_export_aws aws_s3_bucket --output json > s3_buckets.json
```

Conclusion

Steampipe’s zero-ETL engine with its AWS plugin offers a powerful and flexible solution for querying AWS APIs using SQL. We hope these examples offer a simplified and faster approach to data access and integration for your common AWS use cases.

To get started in your own environment, visit the Steampipe website and explore available plugins on the Steampipe Hub.

About the authors