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:

$  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:

$  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:

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:

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:

> .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:

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

2. Create the unqualified SQL for the report:

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 |
+----------------+------------------+---------------------+--------+
    

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.