Overview

We are going to talk about the command line tool steampipe (website) and how to use it to easily pull data from any cloud providers (here, AWS) into a bash script.

Note: This content technical level expects some experience with using AWS CLI and bash scripting.

Overview
Table of Contents
The Situation
Basic Steampipe Queries
Lets Pull it into Bash
Quick Lookups
In Summary

The Situation

Have you ever wanted to more easily get information and status about your current AWS resources into a bash script? While Amazon does provide a command line tool for accessing your AWS account, sometimes you need to execute multiple queries and merge the data together in programming logic. Steampipe is a tool that maps your AWS resources to database-like schemas and then allows you to query it using queries written in SQL Lets dive into some examples.

Basic Steampipe Queries

Here is a simple Steampipe CLI query to show 3 EC2 instances along with their state:

$ steampipe query "select instance_id, instance_state from aws_ec2_instance limit 3"
+---------------------+----------------+
| instance_id         | instance_state |
+---------------------+----------------+
| i-02af95ae1b3d1b120 | running        |
| i-0e08e2f24314c34ea | stopped        |
| i-07a58563325e43d33 | stopped        |
+---------------------+----------------+

Normally, if you’d like to show the name of the instance (the Name tag) with the AWS CLI you might do something like:

aws ec2 describe-instances \
    --filters Name=tag-key,Values=Name \
    --query 'Reservations[*].Instances[*].{Instance:InstanceId,AZ:Placement.AvailabilityZone,Name:Tags[?Key==`Name`]|[0].Value}' \
    --output table

…which certainly works, but the Steampipe version is much easier to remember the SQL syntax and craft together the query:

$ steampipe query "select placement_availability_zone as az, instance_id, tags ->> 'Name' as Name  from aws_ec2_instance limit 3"
+------------+---------------------+-------------------------------------------+
| az         | instance_id         | name                                      |
+------------+---------------------+-------------------------------------------+
| us-east-1c | i-02af95ae1b3d2b120 | web-db-blue-qa-001                        |
| us-east-1c | i-0e08e2f24414c34ea | RCMWEBSTAGE3RJ                            |
| us-east-1c | i-07a28533325e43d33 | jmeter-server-good-001                    |
+------------+---------------------+-------------------------------------------+

Lets Pull it into Bash

Ok, now that we have some basic Steampipe queries down, lets work on pulling it into a bash script looping through the results to act on the data. If you’re comfortable with bash, this is probably the most important step; getting the data to where I can manipulate it.

The easiest way is to tell Steampipe to output its results in a CSV format, and then read that into individual variables. The command line option is --output csv .

Here is a script I use every day to show me all EC2 instances and the state of the virtual machines.

For this query

steampipe query "select region, instance_id, instance_state, 
             instance_type, private_ip_address, tags ->> 'Name' as Name
       from  aws_ec2_instance 
       order by name, instance_state" --output csv

This is what the results look like in CSV

region,instance_id,instance_state,instance_type,private_ip_address,name
us-west-2,i-0fa43b9a06619e2b4,running,m5a.large,172.31.19.161,jump-runner-3
us-west-2,i-0624bac744df6af45,running,t2.micro,172.31.46.14,test_server

In bash you can use a while loop setting the Internal Field Separator (IFS) to a comma and specifying the variables. My bash read line looks like this:

while IFS=, read -r region id state type ip name; do

And the data is read into the while loop using the < <(command) syntax thusly: (details in final script)

You can see that the variables I chose line up with the incoming data. As steampipe doesn’t have (as of this writing) a method of ignoring the header row, you can remove it with the tail command, piping it into tail -n +2

done < <(steampipe query goes here)

In my final version, I add color because its easier on the eyes when parsing a large amount of data. Here is what the final output looks like:

and the full code for the sp_ec2.sh script:

#/bin/bash

normal=$(tput sgr0)
red=$(tput setaf 1)
green=$(tput setaf 2)
yellow=$(tput setaf 3)
blue=$(tput setaf 4)

while IFS=, read -r region id state type ip name; do

    test -z "$id" && break

    scolor=$normal
    case $state in
        running) scolor=$green ;;
        stopped) scolor=$red ;;
        pending) scolor=$blue ;;
        stopping) scolor=$yellow ;;
    esac

    printf "%-10s %-31s ${scolor}%-10s${normal} %-20s %-11s ${yellow}%-15s${normal}\n" \
                      "$region" "${name:0:31}" "$state" $id "$type" "$ip" 

done < <(STEAMPIPE_CACHE=false steampipe query "select region, instance_id, instance_state, 
            instance_type, private_ip_address, tags ->> 'Name' as Name
      from  aws_ec2_instance 
      order by name, instance_state" --output csv | tail -n +2)

Quick Lookups

A quick way to lookup the names of the AWS resources (tables) is to use the .tables command:

> .tables
 ==> aws
+--------------------------------------------------------------+--------------------------------------------------------------------+
| table                                                        | description                                                        |
+--------------------------------------------------------------+--------------------------------------------------------------------+
| aws_accessanalyzer_analyzer                                  | AWS Access Analyzer                                                |
| aws_account                                                  | AWS Account                                                        |
| aws_acm_certificate                                          | AWS ACM Certificate                                                |
| aws_amplify_app                                              | AWS Amplify App                                                    |
| aws_api_gateway_api_key                                      | AWS API Gateway API Key                                            |
| aws_api_gateway_authorizer                                   | AWS API Gateway Authorizer                                         |
...

And once you find the table you want, you can use .inspeect <table> command to view its columns:

> .inspect aws_ec2_instance
+--------------------------------------------+--------------------------+-------------------------------------------------------------------------------------------------------------------------+
| column                                     | type                     | description                                                                                                             |
+--------------------------------------------+--------------------------+-------------------------------------------------------------------------------------------------------------------------+
| _ctx                                       | jsonb                    | Steampipe context in JSON form, e.g. connection_name.                                                                   |
| account_id                                 | text                     | The AWS Account ID in which the resource is located.                                                                    |
| akas                                       | jsonb                    | Array of globally unique identifier strings (also known as) for the resource.                                           |
| arn                                        | text                     | The Amazon Resource Name (ARN) specifying the instance.                                                                 |
| block_device_mappings                      | jsonb                    | Block device mapping entries for the instance.                                                                          |

In Summary

Everyone has a tool of choice and for me, one of the main ones is bash scripting. When working with AWS I’ve found that the easiest way to get the data you want is to use Steampipe, so one of my goals was to get the two working together as seamlessly as possible. I hope that some of the code snippets and explanations here have helped you along your journey. Good luck!