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^W
R^W
Smalltalk), 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.
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.
FIN
Just two resources (Steampipe should keep you busy enough 😎). Have a great weekend! ☮