I was recently listening to an episode of the Unexplored Territory podcast (episode #023 – Introducing Oracle Cloud VMware Solution with Richard Garsthagen). At the end of each episode, the hosts ask the guest about a technology that should be explored. The response to this question is the first time I heard about steampipe.io. I made a note to take a look at this open source project and wanted to share my notes on getting started.
Steampipe is a SQL like query language for various plugin endpoints, such as AWS, Azure, CSV files, GCP, IMAP, LDAP, VMware vSphere, among others. Plenty of other tools exist to query these endpoints, but this is the first I’ve seen where the exact same syntax can be used to query/join all of them in one result set.
To get started, I decided I would use the prebuilt container image (Using Containers | Documentation | Steampipe). The first step was to create a new directory to store some configuration files. Also, as the processes in the container image run as a non-root user, I made this user (uid 9193) the owner of the folder. Finally, as described in the documentation, I created an alias so that sp could be entered to interact with the container.
# create configuration folder
mkdir -p /data/container/steampipe/sp/config
# make the non-root steampipe user the owner of this configuration folder
chown 9193 /data/container/steampipe/sp/config
# alias the command
alias sp="docker run \
-it \
--rm \
--name steampipe \
--mount type=bind,source=/data/container/steampipe/sp/config,target=/home/steampipe/.steampipe/config \
--mount type=volume,source=steampipe_plugins,target=/home/steampipe/.steampipe/plugins \
turbot/steampipe"Once the folder is defined and our alias is available, we are now able to install Steampipe plugins. For my example, I’m only using the VMware vSphere and CSV file plugins, but you should explore the Steampipe documentation for other available options.
root@lab-dock-14 [ /data/container/steampipe/sp ]# sp plugin install steampipe theapsgroup/vsphere csv
steampipe [====================================================================] Done
theapsgroup/vsphere [====================================================================] Done
csv [====================================================================] Done
Installed plugin: csv@latest v0.5.0
Documentation: https://hub.steampipe.io/plugins/turbot/csv
Installed plugin: steampipe@latest v0.6.0
Documentation: https://hub.steampipe.io/plugins/turbot/steampipe
Installed plugin: vsphere@latest v0.1.3
Documentation: https://hub.steampipe.io/plugins/theapsgroup/vsphereAfter installing the plugins, we should see a few spc files that were automatically configured.
root@lab-dock-14 [ ~ ]# cd /data/container/steampipe/sp/config
root@lab-dock-14 [ /data/container/steampipe/sp/config ]# ls -lh
total 16K
-rw-r--r-- 1 9193 root 1.8K Feb 12 20:34 csv.spc
-rwxr-xr-x 1 9193 root 971 Feb 12 20:34 default.spc
-rw-r--r-- 1 9193 root 50 Feb 12 20:34 steampipe.spc
-rw-r--r-- 1 9193 root 295 Feb 12 20:34 vsphere.spcThere is one spc file for each plugin. We can review these files to see the default configuration, by default the files contain some of the available syntax, but for our simple example we are going to create our own files. To start with we are going to create a very simple CSV file in the /data/container/steampipe/sp/config directory, since that path already has a bind map to /home/steampipe/.steampipe/config inside of the container. Our example CSV file will only contain two columns, one for a server_name and another for the owner. The contents of this server-owner.csv file will look like this:
server_name,owner
core-control-21,Brian Wuchner
core-vcenter01,Brian Wuchner
beet-farm-01,Dwight SchruteTo tell steampipe how to find the CSV file, we are going to create a new csv.spc file. I did this by renaming the default csv.spc (with the syntax mv csv.spc csv.spc.old) and then created a new file (with vi csv.spc). Our new file only contains the following information:
connection "csv" {
plugin = "csv"
paths = ["/home/steampipe/.steampipe/config/server-owner.csv"]
}This is a very straightforward file — it shows that we are using the CSV plugin and specifically looking at the server-owner.csv file. Lets investigate this CSV file to see how things work. First up, we will enter the interactive query mode using sp query and then do a very basic select all statement against the server-owner file. Then we will add a bit more, just to get the hang of SQL again by adding a where clause. Finally, we will .quit the query editor. The output of these commands can be seen below.
root@lab-dock-14 [ /data/container/steampipe/sp/config ]# sp query
Welcome to Steampipe v0.18.5
For more information, type .help
> select * from "server-owner"
+-----------------+----------------+---------------------------+
| server_name | owner | _ctx |
+-----------------+----------------+---------------------------+
| beet-farm-01 | Dwight Schrute | {"connection_name":"csv"} |
| core-control-21 | Brian Wuchner | {"connection_name":"csv"} |
| core-vcenter01 | Brian Wuchner | {"connection_name":"csv"} |
+-----------------+----------------+---------------------------+
> select server_name from "server-owner" where owner ilike '%dwight%'
+--------------+
| server_name |
+--------------+
| beet-farm-01 |
+--------------+
> .quitWorking with a CSV file is a basic example and we are only using it above to demonstrate how this would work. The article promised we’d cover VMware vSphere, so we will do that next.
To connect to vSphere we previously installed the theapsgroup/vsphere plugin. We now need to define a spc file that tells it where to find the vSphere environments, just like we did with the path to the CSV file before. I start by backing up the default file (mv vsphere.spc vsphere.spc.old) and then creating a new file (vi vsphere.spc). This new vsphere.spc file will have a few more required attributes than the csv.spc from earlier, as we need to pass in server name, username, password, etc. Here is the sample vsphere.spc file from my lab.
connection "vsphere_vc1" {
plugin = "theapsgroup/vsphere"
vsphere_server = "vc1.example.com"
user = "svc-vspherero@lab.enterpriseadmins.org"
password = "Real-password-here!"
allow_unverified_ssl = true
}
connection "vsphere_vc3" {
plugin = "theapsgroup/vsphere"
vsphere_server = "vc3.example.com"
user = "administrator@vsphere.local"
password = "VMware1!"
allow_unverified_ssl = true
}
connection "vsphere_t106" {
plugin = "theapsgroup/vsphere"
vsphere_server = "t106-vcsa-01.lab.enterpriseadmins.org"
user = "administrator@vsphere.local"
password = "VMware1!"
allow_unverified_ssl = true
}
connection "vmware_vsphere_all" {
plugin = "theapsgroup/vsphere"
type = "aggregator"
connections = ["vsphere_vc1","vsphere_vc3","vsphere_t106"]
}As you can see, I have three different VMware vSphere environments listed, with plugin name, server name, and other details. In addition, there is an aggregator connection that groups all of these environments together. I could have multiple aggregations defined, such as one for prod and another for development instances. Lets run a few test queries just like we did with the CSV example above.
Once we enter the interactive query mode, we will see some autocompletion samples showing what tables are available for our vSphere data. The common stuff like datastore, host, network, and VMs are present.
Lets try a couple of simple examples.
root@lab-dock-14 [ /data/container/steampipe/sp/config ]# sp query
Welcome to Steampipe v0.18.5
For more information, type .help
> select name, moref, product from vmware_vsphere_all.vsphere_host
+---------------------------------------+------------+----------------------------------+
| name | moref | product |
+---------------------------------------+------------+----------------------------------+
| euc-esx-22.lab.enterpriseadmins.org | host-25601 | VMware ESXi 7.0.3 build-20328353 |
| dr-esx-31.lab.enterpriseadmins.org | host-25572 | VMware ESXi 7.0.3 build-20328353 |
| core-esxi-33.lab.enterpriseadmins.org | host-34795 | VMware ESXi 7.0.3 build-21053776 |
| core-esxi-34.lab.enterpriseadmins.org | host-34781 | VMware ESXi 7.0.3 build-21053776 |
| euc-esx-21.lab.enterpriseadmins.org | host-25598 | VMware ESXi 7.0.3 build-20328353 |
| test-vesx-71.lab.enterpriseadmins.org | host-8 | VMware ESXi 8.0.0 build-21203435 |
| test-vesx-72.lab.enterpriseadmins.org | host-12 | VMware ESXi 8.0.0 build-21203435 |
| t106-vesx-03.lab.enterpriseadmins.org | host-105 | VMware ESXi 6.7.0 build-19195723 |
| t106-vesx-01.lab.enterpriseadmins.org | host-303 | VMware ESXi 6.7.0 build-19195723 |
| t106-vesx-02.lab.enterpriseadmins.org | host-106 | VMware ESXi 6.7.0 build-19195723 |
+---------------------------------------+------------+----------------------------------+
> select count(*) from vmware_vsphere_all.vsphere_vm
+-------+
| count |
+-------+
| 212 |
+-------+
> .quitOk, this was fun. We can now query CSV files and vSphere data as if we were using a SQL database. This is interesting, but what becomes really powerful is when we tie this together. Since our data is in the same place/format, we can do more complex SQL queries that join one or more data sources together. In the next example, we will return a list of some VM details, along with the owner information from our CSV file.
> select vsphere_vm.name, vsphere_vm.power, vsphere_vm.memory, vsphere_vm.hardware, "server-owner".owner
from vsphere_vm join "server-owner" on vsphere_vm.name = "server-owner".server_name
+-----------------+------------+--------+----------+----------------+
| name | power | memory | hardware | owner |
+-----------------+------------+--------+----------+----------------+
| beet-farm-01 | poweredOff | 1024 | vmx-11 | Dwight Schrute |
| core-control-21 | poweredOn | 4096 | vmx-15 | Brian Wuchner |
| core-vcenter01 | poweredOn | 16384 | vmx-10 | Brian Wuchner |
+-----------------+------------+--------+----------+----------------+These are just some examples to get started. If you check out the steampipe.io site, you’ll find more examples and additional plugins covering other technologies, such as net that would allow you to query an SSL certificate (among many other things), ldap which can query LDAP/Active Directory, and other plugins for many cloud providers like AWS and Azure.