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.

{` 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](#named-queries). | [Search paths](#search-path-prefixing) give you better control when working with multiple connections. | [Workspace management](#workspace-management) allows you to group connections and named queries together. | Even more in the [release notes](https://github.com/turbot/steampipe/blob/main/CHANGELOG.md#v040-2021-04-15). ## 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.: ```bash $ 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](#workspace-management). 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](#workspace-management) 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: ```hcl 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](/docs/managing/connections#setting-the-search-path). ## 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](#search-path-prefixing)). Details on the configuration settings possible with the `workspace.spc` file are also [in the documentation](/docs/reference/config-files#options). ## 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:

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

{` 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: ```hcl # /reports/scranton/workspace.spc options "terminal" { output = "csv" search_path_prefix = "dmi_scranton" } ``` ```hcl # /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](https://steampipe.io/community/join). For even more good stuff in v0.4.0, checkout the [full release notes on Steampipe v0.4.0.](https://github.com/turbot/steampipe/blob/main/CHANGELOG.md#v040-2021-04-15)

New! Named Queries, Search Paths and Workspaces