select * from cloud;

Use SQL to query AWS, Azure, GCP, etc. with Steampipe, a new open source project from Turbot.

Steampipe, a new open-source tool from Turbot allows you to ask interesting questions of your cloud, one query at time.

Which users have MFA enabled right now?

 
 select 
   user_id, 
   name,
   mfa_enabled
 from 
   github_repo_user;
                  

What security groups are open to the world?


 select 
   group_name,
   group_id
 from
   aws_vpc_security_group_rule 
 where 
   type = 'ingress'
   and cidr_ip = '0.0.0.0/0';
  

Which resources aren't tagged correctly?


 select 
   id,
   name
 from
   azure_compute_image
 where
   tags -> 'owner' is null or
   tags -> 'app_id' is null;
 

What storage volumes are not in use?


 select 
   volume_id,
   volume_type
 from 
   aws_ebs_volume
 where
   attachments is null;
  

What IAM Users have inline policies attached?


  select
    user_id,
    name
  from
    aws_iam_user
  where 
    inline_policies is not null;
 

Built for Cloud Professionals

At Turbot many of our day-to-day tasks require us to work with cloud resource metadata, sometimes as a basis for a search or filter, but also to combine with or enrich other data sources.

The existing tools we used to answer these questions were a hodgepodge of web-based consoles, CLIs, APIs, and SDKs, cobbled together with custom python or bash scripts. None of which has any level of consistency across clouds or even in a single cloud.

We needed better tooling to get our own cloud work done, so we built something to scratch that itch… and from the very first query it blew our minds. There is something very satisfying about seeing your cloud resources act like the database we always knew they were; it feels like a magic trick. Once we had it in our hands we were sure that we wanted to share it with the world, and the Steampipe open source project was born.

How does it work?

One of the goals of Steampipe was to make the first experience with it as simple as possible; we wanted zero barriers for people to download and run their first query. A lot of engineering went into how to elegantly install the dependencies and provide a first class interface to current and future plugins.

The simplicity of the user experience masks the complexity and depth of the architecture. Underlying Steampipe are three distinct software components: a CLI executable, plugin modules and a lightweight PostgreSQL background service. Data is not persisted in Postgres, instead Steampipe uses the PostgreSQL Foreign Data Wrapper to present external data as database tables. The foreign data wrapper does not directly interface with external systems, but instead relies on plugins to return data in a standard format. This approach simplifies the work needed to extend Steampipe, because PostgreSQL specific logic is encapsulated in the foreign data wrapper, while API and service-specific code resides only in the plugin.

Where do we go from here?

One of the fun things that happens when you use Steampipe every day, is that you start to wish that everything was SQL enabled.

Memes aside, we are finding that we do love using Steampipe to answer real world questions outside of the public cloud space, and those ideas are making their way into the product too. For example, our customer success group was struggling to produce simple aging reports from our Zendesk platform using the built-in reporting tools.

Today, we have an automated job that runs a query against Zendesk using Steampipe and posts it to Slack every morning; It sets the focus for the day for the entire team.


  select
    date_part('day', now() - t.created_at) as age,
    t.id,
    t.status,
    u.name as agent,
    o.name as organization,
  from
    zendesk_ticket as t,
    zendesk_user as u,
    zendesk_organization as o
  where
    t.assignee_id = u.id
  and
    t.organization_id = o.id
  and
    t.status in ('open', 'pending', 'hold');

  +-----+-----+---------+----------+------------------+
  | AGE | ID  | STATUS  | AGENT    | ORGANIZATION     |
  +-----+-----+---------+----------+------------------+
  | 43  | 373 | hold    | KKapoor  | Haymont Tires    | 
  | 41  | 374 | hold    | JHalpert | Tract Industries | 
  | 40  | 375 | hold    | KKapoor  | Dunmore HS       |
  | 22  | 376 | hold    | KKapoor  | East PA Seminary |
  | 14  | 418 | pending | JHalpert | Capital One      |
  | 5   | 435 | pending | DSchrute | Mr. Rammel       |
  | 2   | 437 | pending | JHalpert | Steamtown Mall   |
  | 0   | 439 | open    | SHudson  | Stone & Son Suit |
  | 0   | 440 | hold    | JHalpert | Larry Meyers     |
  | 0   | 441 | pending | Sudson   | Blue Cross       |
  +-----+-----+---------+----------+------------------+
 

As I write this, only 2 weeks after launch, there are 11 plugins with ~200 tables published to the Steampipe Hub: https://hub.steampipe.io, including AWS, Azure, GCP, Slack, Zendesk, Github and most recently Digital Ocean. We fully intend to keep expanding the coverage, scratching any itches we find, but we are even more excited to see where the open source community takes the project and what APIs they connect Steampipe to.

Bonus: Get started today and get some free swag!

That’s right, take 2 minutes out of your day to install Steampipe and when you run your first query let us know about it! Tweet your first query to @turbothq, or head over to the Steampipe message boards, and post some feedback there on your “First Query” (Can you tell we are excited about this?) So excited in fact, that we will handsomely reward everyone that posts #firstquery in February with Turbot and Steampipe laptop sticker swag!

Getting Started Resources