Batch Queries
Steampipe queries can provide valuable insight into your cloud configuration, and the interactive client is a powerful tool for ad hoc queries and exploration. Often, however, you will write a query that you will want to re-run in the future, either manually or perhaps as a cron job. Steampipe allows you to save your query to a file, and pass the file into the steampipe query
command.
For example, lets create a query to find S3 buckets where versioning is not enabled. Paste the following snippet into a file named s3_versioning_disabled.sql
:
selectname,region,account_id,versioning_enabledfromaws_s3_bucketwherenot versioning_enabled;
We can now run the query by passing the file name to steampipe query
steampipe query s3_versioning_disabled.sql
You can even run multiple sql files by passing a glob or a space separated list of file names to the command:
steampipe query *.sql
Query output formats
By default, the output format is table
, which provides a tabular, human-readable view:
+-----------------------+---------------+-----------+| vpc_id | cidr_block | state |+-----------------------+---------------+-----------+| vpc-0de60777fdfd2ebc7 | 10.66.8.0/22 | available || vpc-9d7ae1e7 | 172.31.0.0/16 | available || vpc-0bf2ca1f6a9319eea | 172.16.0.0/16 | available |+-----------------------+---------------+-----------+
You can use the --output
argument to output in a different format. To print your output to json, specify --output json
:
$ steampipe query "select vpc_id, cidr_block,state from aws_vpc" --output json[{"cidr_block": "10.66.8.0/22","state": "available","vpc_id": "vpc-0de60777fdfd2ebc7"},{"cidr_block": "172.31.0.0/16","state": "available","vpc_id": "vpc-9d7ae1e7"},{"cidr_block": "172.16.0.0/16","state": "available","vpc_id": "vpc-0bf2ca1f6a9319eea"}]
To print your output to csv, specify --output csv
:
$ steampipe query "select vpc_id, cidr_block,state from aws_vpc" --output csvvpc_id,cidr_block,statevpc-0de60777fdfd2ebc7,10.66.8.0/22,availablevpc-9d7ae1e7,172.31.0.0/16,availablevpc-0bf2ca1f6a9319eea,172.16.0.0/16,available
Redirecting the output to CSV is common way to export data for use in other tools, such as Excel:
steampipe query "select vpc_id, cidr_block,state from aws_vpc" --output csv > vpcs.csv
To use a different delimiter, you can specify the --separator
argument. For example, to print to a pipe-separated format:
$ steampipe query "select vpc_id, cidr_block,state from aws_vpc" --output csv --separator '|'vpc_id|cidr_block|statevpc-0bf2ca1f6a9319eea|172.16.0.0/16|availablevpc-9d7ae1e7|172.31.0.0/16|availablevpc-0de60777fdfd2ebc7|10.66.8.0/22|available
Named Queries
Steampipe also allows you to run named queries defined in mods.
Creating your own named queries is simple. First, you need to create a mod for your queries:
mkdir my-queries
By default, Steampipe looks for mods in the current directory, though you can specify a different directory with the --workspace-chdir
argument. Lets change to that directory and initialize the mod:
cd my-queriessteampipe mod init
Steampipe will create a query object for every .sql
file in your mod directory, though you can also define query resources using HCL in .sp
files if you prefer.
If you have not done so already, create the s3_versioning_disabled.sql
file from the previous example in your mod folder.
You can now run your query by name in an interactive session or from from command line.
Start the interactive query shell:
steampipe query
You can run your query by name:
query.s3_versioning_disabled
Your saved queries even show up in the auto-complete list, making them easier to find and recall!
You can even run the named query in batch mode:
steampipe query "query.s3_versioning_disabled"
Steampipe makes it easy to build a library of custom queries that you can effortlessly recall and re-use!