Steampipe

In these Friday editions, I try to drop resources that you can (if you so choose) really dig into over the weekend, and if you're a fan of SQL or even just a fan of normalizing access to diverse resources, then I dare you (double dog, in fact) not to dig into Steampipe [GH].

Steampipe's primary tag line is select * from cloud; and that really doesn't do it justice, though I do prefer their secondary tag line: "Query like it's 1992". Fundamentally, Steampipe lets you use SQL to query, well, anything. And, all those anythings can be joined just like you would tables in a traditional database.

It's easier to explain by showing how it works. It runs on everything but folks on proper OSes can do:

$ brew tap turbot/tap
$ brew install steampipe

to have Steampipe ready to go in seconds.

Let's say we want to see if one of our fav datasci companies has stood up any new domains. We could go to crt.sh and enter in posit.co in the search box to get an HTML table of responses. Or, we could stay at our warm and comfy command line and do:

$ steampipe plugin install crtsh

to install the crt.sh Steampipe plugin and then fire up the query REPL:

$ steampipe query

and drop some SQL in it:

with raw_domains as (
  -- Search for any certificates matching posit.co
  select distinct
    jsonb_array_elements_text(dns_names) as domain
  from
    crtsh_certificate
  where
    query = 'posit.co'
)
select
  *
from
  raw_domains
where
  domain like '%posit.co'
order by
  domain;

to get:

+-------------------------------+
| domain                        |
+-------------------------------+
| endcustomeragreement.posit.co |
| orders.posit.co               |
| posit.co                      |
| solutions.posit.co            |
| www.posit.co                  |
+-------------------------------+

(darnit, still no dailies.posit.co).

There are many plugins to choose from over at the hub and they all install in seconds. Let's lookup an IP address with IPStack:

$ steampipe plugin install ipstack

Now, since IPStack requires an API key, we have to put that in ~/.steampipe/config/ipstack.spc (every plugin gets a config file) to be able to do a query:

select
  ip,
  country_code,
  region_name,
  latitude
from
  ipstack_ip
where
  ip = '86.75.30.9';

+------------+--------------+---------------+------------------+
| ip         | country_code | region_name   | latitude         |
+------------+--------------+---------------+------------------+
| 86.75.30.9 | FR           | Île-de-France | 48.8602294921875 |
+------------+--------------+---------------+------------------+

You don't need to use the REPL, and can also get results in other formats like JSON or CSV. Here, we'll use the net plugin to pull Twitter's TLS certificate, get the output in JSON and then use some jq on it:

$ steampipe query \
  --output json \
  "select * from net_certificate where domain = 'twitter.com'" | jq ' .[] | .organization'
"Twitter, Inc."

(yes, we could have just selected the organization in the SQL).

Under the hood, Steampipe uses postgres, and you can write your own plugins (I'll be making one for GreyNoise over the weekend).

You can use queries to make dirt simple dashboards with Steampipe (hit the link for a robust set of examples). Using HCL syntax, just put your queries into cards of various types (charts, tables, etc), set the widths (in grid units; Steampipe uses a 12 item grid like many other CSS frameworks), add optional hyperlinks (Steampipe supports jq JSON templates), and you can see the status of any queryable resources in mere minutes without installing any more tools.

Steampipe's extensive collection of cloud provider plugins means you can check on the status of all your resources. Better still, you can make sure they're compliant against many predefined benchmarks, such as CIS, NIST, HIPAA and more. Plus, you can define your own.

As if all that weren't enough to keep you engaged, you can package up dashboards, benchmarks, queries, and controls into Steampipe mods, which are version-stamped, shareable collections. You can get started with mods by using one of the pre-built ones.

But we're not done yet!

If you fire up:

$ steampipe service start

you can talk directly to Steampipe's underlying postgres database, which means you can run all the same queries from your fav language (Rust^WR^WSmalltalk), thanks to Postgres' Foreign Data Wrapper. Furthermore, you can use Steampipe's built-in web server to also serve up your dashboards with:

$ steampipe service start --dashboard

There are great developer docs, so there's nothing stopping you from making your own service connector, extensive references to help you figure out how to do anything in Steampipe, and a great community (hit up the "Community" menu at the top of any Steampipe site page).

Make sure to drop links to what you built in the comments.

Leave a comment

Web Archiving: Why Care About The Past

The 17th anniversary of Hurricane Katrina hitting the U.S. Gulf Coast was just a few days ago (it made landfall August 29, 2005). Michele Weigle, one of the members of Old Dominion University's Web Science and Digital Libraries Research Group (ODU WSDL), was originally from Louisiana, and has many family members in that area. The storm had huge emotional and kinetic impacts on the lives of everyone in the region.

You may think resources such as the Wayback Machine are just for looking up presently broken links or saving bad tweets of various politicians and celebs, but you can use these memento resources to go back in time and live the experience in the contemporary context, which turns out to be pretty powerful, especially if you put them together like Michele and her colleagues did with Katrina.

I recommend dropping to the bottom of the page and letting the slide deck autoplay for the best experience.

Perhaps Michele's story will inspire you to make something similar to connect back to the past in a way you haven't before.

Share

FIN

Just two resources (Steampipe should keep you busy enough 😎). Have a great weekend! ☮