New! Filter and export controls, plus lots of new mods and plugins. →
Announcement

New! Named Queries, Search Paths and Workspaces

Learn more about the v0.4.0 release of the open source Steampipe CLI.

Steampipe Team
9 min. read - April 16, 2021

What is Steampipe?

SQL is an expressive language for asking questions of structured data. Steampipe, an open-source project from Turbot, enables cloud pros (e.g. software developers, operations engineers and security teams) to query their favorite cloud services with SQL.

Steampipe's intuitive command line interface (CLI) makes it easy to ask questions of your cloud resources and services. Traditional tools and custom scripts that provide visibility into these services are cumbersome, inconsistent across providers and painful to maintain. Steampipe provides a consistent, explorable and interactive approach to solve this problem across an ever-growing list of cloud services.

steampipe cli
>
select
region,
instance_state as state,
instance_type as type
from
aws_ec2_instance;

+-----------+---------+-----------+
| region    | state   | type      |
+-----------+---------+-----------+
| eu-west-1 | running | t3.medium |
| eu-west-2 | running | m5a.large |
| us-east-1 | running | t3.large  |
+-----------+---------+-----------+
    

New in Release v0.4.0

tl;dr Steampipe now supports named queries. | Search paths give you better control when working with multiple connections. | Workspace management allows you to group connections and named queries together. | Even more in the release notes.

Named Queries

Sometimes we develop queries interactively and explore data, but in many situations, you will write a query that you will want to re-run in the future. Up to now we have had to save these queries in some sort of pastebin or write scripts to pass the queries via the CLI. Starting with v0.4.0, Steampipe now elegantly works with named queries.

Named queries are stored as .sql files:

bash
$ cd ~/my_queries
$ cat my_query.sql
select
name,
email,
created_at
from
zendesk_user
where
created_at >= NOW() - interval '60 day';
$

Run from the shell

The CLI now allows you to pass a SQL file into the steampipe query command:

bash
$ steampipe query my_query.sql

+----------------+------------------+---------------------+
| name           | email            | created_at          |
+----------------+------------------+---------------------+
| Dwight Schrute | dschrute@dmi.com | 2005-03-24 21:30:19 |
| Jim Halpert    | jhalpert@dmi.com | 2005-03-24 21:30:23 |
| Pam Beesly     | pbeesly@dmi.com  | 2005-03-24 21:30:25 |
| Michael Scott  | bestboss@dmi.com | 2005-03-24 21:30:14 |
| Stanley Hudson | shudson@dmi.com  | 2005-03-24 21:42:20 |
+----------------+------------------+---------------------+
    

You can run multiple SQL files by passing a glob or a space separated list of file names to the command e.g.:

$ steampipe query my_query_1.sql my_query_2.sql
$ steampipe query *.sql

Run from the CLI

You can also use named queries from the CLI. Steampipe will create a query object for every .sql file in the root of your workspace directory. As you start to type query... a dynamic list of available named queries will show up in the auto-complete list, making them easier to find and recall:

Once the query.name is submitted the saved SQL statement will be executed and results displayed, just as if it had been typed:

steampipe cli
>
query.my_query_1

+----------------+------------------+---------------------+
| name           | email            | created_at          |
+----------------+------------------+---------------------+
| Dwight Schrute | dschrute@dmi.com | 2005-03-24 21:30:19 |
| Jim Halpert    | jhalpert@dmi.com | 2005-03-24 21:30:23 |
| Pam Beesly     | pbeesly@dmi.com  | 2005-03-24 21:30:25 |
| Michael Scott  | bestboss@dmi.com | 2005-03-24 21:30:14 |
| Stanley Hudson | shudson@dmi.com  | 2005-03-24 21:42:20 |
+----------------+------------------+---------------------+
    

Workspace file watching

While the Steampipe service is running (or whenever you have an active interactive CLI session) it will watch your current workspace folder for changes to existing .sql files and for any new files added. When a change is detected the service will automatically add or update the available named queries without requiring a restart.

Search Path Prefixing

Steampipe allows you to have multiple connections available using the same plugin. This is very useful when you want to run queries against multiple environments that need different credentials (e.g., multiple AWS accounts). Here is an aws.spc with multiple accounts using different AWS CLI profiles:

connection "dmi_scranton" {
plugin = "aws"
profile = "scranton_prod"
regions = ["us-east-2"]
}
connection "dmi_stamford" {
plugin = "aws"
profile = "stamford_prod"
regions = ["us-east-1"]
}

Prior to v0.4.0 you would need to use the fully qualified name to query from both connections:

steampipe cli
>
select
vpc_id,
title
from
dmi_scranton.aws_vpc;

+-----------------------+---------+
| vpc_id                | title   |
+-----------------------+---------+
| vpc-0fbfa42ae706388ce | scr-vpc |
+-----------------------+---------+
    
>
select
vpc_id,
title
from
dmi_stamford.aws_vpc;

+-----------------------+---------+
| vpc_id                | title   |
+-----------------------+---------+
| vpc-0056428b154701f55 | stm-vpc |
+-----------------------+---------+
    

Now, with search path prefixing the query can be simplified to use unqualified SQL syntax:

steampipe cli
> .search_path_prefix dmi_scranton
> select
vpc_id,
title
from
dmi_scranton.aws_vpc;

+-----------------------+---------+
| vpc_id                | title   |
+-----------------------+---------+
| vpc-0fbfa42ae706388ce | scr-vpc |
+-----------------------+---------+
    
> .search_path_prefix dmi_stamford
> select
vpc_id,
title
from
aws_vpc;

+-----------------------+---------+
| vpc_id                | title   |
+-----------------------+---------+
| vpc-0056428b154701f55 | stm-vpc |
+-----------------------+---------+
    

For more in-depth discussion of search paths checkout our awesome documentation.

Workspace Management

Workspaces in Steampipe provide a way to localize and override your global configuration settings. By default, the local directory you launched Steampipe from becomes the context for your workspace, but now with v0.4.0 you can specify your workspace using the --workspace option when launching Steampipe interactively or as a service.

Using the workspace feature you can point Steampipe to a directory with a workspace.spc file where terminal options can be set (like the Search Path). Details on the configuration settings possible with the workspace.spc file are also in the documentation.

Putting it All Together

Named queries, workspaces and search paths are each great features, but combining them yeilds something really special. For example, consider a use case where we want to run a daily report looking for users who have not been active in the last 30 days. Steampipe now makes it trivial to accomplish this across all our AWS accounts:

1. Create a workspace directory structure:

bash
$ mkdir /reports
$ cd /reports
$ mkdir scranton stamford

2. Create the unqualified SQL for the report:

This query will find users with unused/infrequently used passwords:

steampipe cli
>
select
user_name,
password_enabled,
password_last_used,
age(date(current_timestamp), date(password_last_used)) as age
from
aws_iam_credential_report
where
password_enabled and (
password_last_used is null
or (date(current_timestamp) - date(password_last_used)) > 90
);

+----------------+------------------+---------------------+--------+
| user_name      | password_enabled | password_last_used  | age    |
+----------------+------------------+---------------------+--------+
| dwight_schrute | true             |                     |        |
| kelly_kapoor   | true             |                     |        |
| ryan_howard    | true             |                     |        |
| pam_beesly     | true             | 2020-07-16 04:49:39 | 9 mons |
+----------------+------------------+---------------------+--------+
    

Save it to: /reports/unused_pwd.sql

3. Configure the workspaces:

# /reports/scranton/workspace.spc
options "terminal" {
output = "csv"
search_path_prefix = "dmi_scranton"
}
# /reports/stamford/workspace.spc
options "terminal" {
output = "csv"
search_path_prefix = "dmi_stamford"
}

4. Schedule the cron jobs:

$ crontab -e
~ 0 7 * * * steampipe query /reports/unused_pwd.sql --workspace /reports/scranton >> /reports/scr_unused_pwd.csv
~ 0 7 * * * steampipe query /reports/unused_pwd.sql --workspace /reports/stamford >> /reports/stm_unused_pwd.csv

Those queries will now both run every morning (7a) and put the results into separate CSV files.

Yes, we think that is really cool too!

The combination of named queries, search paths and workspaces will open up a whole world of automation options for Steampipe users and we would love to hear how you plan on using them! If you would like to talk with us about how you are using Steampipe or just hang out with our developers, please consider joining our new Slack workspace.

For even more good stuff in v0.4.0, checkout the full release notes on Steampipe v0.4.0.