Steampipe Is Sql Magic

Up And Running In Minutes

I tried Steampipe out for the first time today.

Follow Steampipe On Twitter

I’m seriously impressed.

I built a project go-aws-ami-metrics last year to test out some Go that would iterate through instances and AMIs to build out aging information on the instances.

I used it to help me work through how to use the AWS SDK to iterate through regions, instances, images, and more.

In 15 mins I just solved the equivalent issue in a way that would benefit anyone on a team. My inner skeptic was cynical, thinking this abstraction would be problematic and I’d be better served by just sticking with the raw power of the SDK.

It turns out this tool already is built on the SDK using the same underlying API calls I’d be writing from scratch.

First example: DescribeImage

This is the magic happening in the code.

1
2
3
4
5
6

 resp, err := svc.DescribeImages(&ec2.DescribeImagesInput{
  Owners: []*string{aws.String("self")},
 })
 for _, image := range resp.Images {
  d.StreamListItem(ctx, image)
 }

This is the same SDK I used, but instead of having to build out all the calls, there is a huge library of data already returned.

1
2
3
4
5
6
7
8
9

 req, publicImages := client.DescribeImagesRequest(&ec2.DescribeImagesInput{
  Filters: []*ec2.Filter{
   {
    Name:   aws.String("is-public"),
    Values: []*string{aws.String("true")},
   },
  },
 },
 )

There is no need to reinvent the wheel. Instead of iterating through regions, accounts, and more, Steampipe allows this in plain old SQL.

• EC2 Instances
• that use AWS Owned Images
• and use an image that created greater than n period
• and want the aging in days

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT
 ec2.instance_id,
 ami.name,
 ami.image_id,
 ami.state,
 ami.image_location,
 ami.creation_date,
 extract('day' FROM now()) - extract('day' FROM ami.creation_date) AS creation_age,
 ami.public,
 ami.root_device_name
FROM
 aws_ec2_ami_shared AS ami
 INNER JOIN aws_ec2_instance AS ec2 ON ami.image_id = ec2.image_id
WHERE
 ami.owner_id = '137112412989'
  AND ami.creation_date > now() - INTERVAL '4 week'

There are plugins for GitHub, Azure, AWS, and many more.

You can even do cross-provider calls.

Imagine wanting to query a tagged instance and pulling the tag of the work item that approved this release. Join this data with Jira, find all associated users involved with the original request, and you now have an idea of the possibility of cross-provider data Steampipe could simplify.

Stiching this together is complicated. It would involve at least 2 SDK’s and their unique implementation.

I feel this is like Terraform for Cloud metadata, a way to provide a consistent experience with syntax that is comfortable to many, without the need to deal with provider specific quirks.

Query In Editor

• I downloaded the recommended TablePlus with brew install tableplus.
• Ran steampipe service start in my terminal.
• Copied the Postgres connection string from the terminal output and pasted into TablePlus.
• Pasted my query, ran, and results were right there as if I was connected to a database.

AWS Already Has This

AWS has lots of ways to get data. AWS Config can aggregate across multiple accounts, SSM can do inventory, and other tools can do much of this.

AWS isn’t easy. Doing it right is hard. Security is hard.

Expertise in building all of this and consuming can be challenging.

🎉 Mission accomplished!

Experience

I think Steampipe is offering a fantastic way to get valuable information out of AWS, Azure, GitHub, and more with a common language that’s probably the single most universal development language in existenance: SQL.

One of the goals of Steampipe since we first started envisioning it is that it should be simple to install and use - you should not need to spend hours downloading pre-requisites, fiddling with config files, setting up credentials, or pouring over documentation. We’ve tried very hard to bring that vision to reality, and hope that it is reflected in Steampipe as well as our plugins.

Providing a cli with features like this is incredible.

• execute
• turn into an interactive terminal
• provide prompt completion to commands
• a background service to allow connection via IDE

The Future

The future is bright as long as truncate ec2_instance doesn’t become a thing. 😀

Further Resources

If you want to explore the available schema, check out the thorough docs.

• AWS Tables List shows 212 tables of metadata currently available.
• Use Named queries to build a library of easy queries to call on demand such as ami.aging_instances.
• Use Mods to download a set of named queries and controls to validate things like security and compliance.
• Query multiple connections at once
• Design Principles