About Steampipe

Steampipe organizes your cloud metadata into tables and fields that are easily discoverable and readable.

It is the universal interface to APIs. You can SQL to query cloud infrastructure, SaaS, code, logs, and more.

Painlessly join live cloud configuration data with internal or external data sets to create new insights.

Plugins available on Steampipe

Steampipe has various plugins ranging from public cloud, SaaS to Software Development & Internet. Some of the plugins I see myself using a lot is aws, github, docker. Guess what, they have plugin for twitter and Reddit too!

How to start using Steampipe

You can find detail installation instructions on their Github based on where you would like to install steampipe, locally on Linux based host or MacOS.
I used AWS Cloud Shell to interact with Steampipe in the following way:

Install Steampipe

curl -s -L https://github.com/turbot/steampipe/releases/latest/download/steampipe_linux_amd64.tar.gz | tar -xzvf -

Install aws plugin
```
./steampipe plugin install aws
```
Initiate query
```
./steampipe query
```
I could use the steampipe query to get me the list of S3 buckets (as the screenshot suggests, there weren't any on my account) and get details for EC2 instances, one of them was in Running and other in Terminated state.
It also has an interactive way of querying which is full quite helpful to autocomplete your queries by pressing tab

To get CPU utilization metric on hourly measure for the EC2 instances:

select * from aws_ec2_instance_metric_cpu_utilization_hourly

| instance_id  | metric_name    | namespace | average           | maximum          | minimum   | sample_count | sum                | unit    | timestamp   | partition | region    | account_id   | 
| i-abcd | CPUUtilization | AWS/EC2   | 2.357547108929308 | 3.5593220338983  | 2.13114754098358 | 60           | 141.45282653575848 | Percent | 2022-10-28T08:55:00Z | aws  | us-east-1 | xxxxx | 
| i-pqrs | CPUUtilization | AWS/EC2   | 2.830628979262431 | 22.1311475409836 | 1.50000000000001 | 50           | 141.53144896312156 | Percent | 2022-10-28T07:55:00Z | aws | us-east-1 | xxxxx |

To get a detail understanding on the available tables for corresponding plugin you could always refer their thorough documentation - AWS Tables.
To come out of the query results -> ESC + :q and to come out of the query prompt -> ctrl+d.

Viewing Twitter Data using Steampipe Cloud

Create steampipe cloud account
Create new workspace

Provide twitter bearer token from your twitter developer profile for authentication

I used a simply query to get few public metrics of my profile using the below query:

select name, username, public_metrics  from twitter_user
where username='piece_of_irony'

I could easily get the list of my retweets using the below query:

select text, created_at from twitter_user_tweet
where user_id='123xxxxx'
and retweeted is not null

Isn't it quite interesting how Steampipe brings the data together at the comfort of SQL queries? No complex integrations needed, simple to use and configure!

Another example with Github plugin

Add new connection on Steampipe for Github, add the Personal Access Token (PAT) generated from your GitHub profile to connect.
Running this below query got me search results that had 'hacktoberfest' in the code from public repositories. You can tweak and filter more specifically if you wish to.

select * from github.github_search_code
where query = 'hacktoberfest'
limit 100;

I had a good time exploring Steampipe. If you do too or actively use it, share your experience in the comments.

Cheers!

Steampipe: Getting Started