SQL has always been a data
lingua franca, allowing hard data to be extracted from different domains. This is why I was specifically intrigued by the way
Steampipe can read app data through SQL. It has many plug-ins to convert internal app data to nice SQL tables.
In this post, I’ll look at the
Slack plug-in, which I’ll connect and then use on a live workspace. However, we know before we start that there will be quite a bit of effort to prepare the access to what is a third-party app from Slack’s viewpoint. This is the downside of attempting to open up an application’s data.
What I like about Steampipe is that you can prepare a sensible query before you attach it to a system. This is useful for consultants who may have limited access to systems, so they need to bring a reasonably wide set of tools around with them.
There is a
CLI available for my version of macOS, and I’m quite happy to use its interface.
As usual, I use
Warp for my shell, though Steampipe has its own CLI that will take precedence later on. After three minutes of updating Homebrew, I installed Steampipe directly:
👁 Image
Then, a quick version check to make sure the installation worked:
👁 Image
Then, I installed the Slack plug-in:
👁 Image
Preparing To Interrogate Slack
There are several reasons why you might want to quiz an organization’s Slack, especially if you are dropping in on a team and need to establish a
community of practice (CoP), or simply want to get a feel for which users are most active, or whether certain issues (or ticket numbers) are being discussed.
But first of all, how do we connect to it? Fortunately, Slack has a way of granting tokens within scopes for apps to use. I used this method way back when I looked at
Dark, the serverless backend tool. It was somewhat tricky then, so I was hoping it had become a little simpler. However, this process was only slightly smoother.
I tried to do this using the Mac version of the Slack app, but couldn’t. However, it is a straightforward process from the website. Check in to your target Slack workspace, then go to
api.slack.com/apps.
From here, we can create a new app, select “from scratch,” and then give it a name. The “app” is what Slack is referring to as the third-party access service from Steampipe:
👁 Image
We can then select permissions and gain our scoped token access. I’m avoiding any admin-related scopes, and making sure to include “team,” “users,” “groups,” etc.:
👁 Image
You can always return to this section, add any missing scopes and reinstall the app.
This will allow the app to retrieve basic information from Slack. Finally, we’ll install our new tool and its OAuth tokens to the workspace. Be sure to make a copy of your long User OAuth Token:
👁 Image
As usual, Slack will show you its warning that it is requesting access (or a reason that it cannot).
Make sure you see the tick next to “Install your app” on the progress list:
👁 Image
Now go back to your shell and add that token into Steampipe’s Slack configuration file:
👁 Image
Querying the Data
Now we are finally ready to see what we can do in Steampipe itself. Yay!
We’ll access the CLI’s query mode, and immediately will want to review the list of available tables (note that autocompletion suggestions are provided):
👁 Image
(Press Ctrl+D on a blank line, or use the
.exit command.)
Here is the result:
==> slack
+---------------------------+---------------------------------------------------------+
| table | description |
+---------------------------+---------------------------------------------------------+
| slack_access_log | Logins to Slack, grouped by User, IP and User Agent. |
| slack_connection | Information about the connection to the Slack workspace.|
| slack_conversation | Unified interface to all conversation like things.. |
| slack_conversation_member | Retrieve members of a conversation. |
| slack_emoji | Slack emoji installed in the workspace. |
| slack_group | Slack workspace user groups. |
| slack_search | Search slack for anything using a query. |
| slack_user | Slack workspace users. |
+---------------------------+---------------------------------------------------------+
To get information about the columns in a table, run
.inspect {connection}.{table}.
Before continuing on, try this command to confirm whether you have a connection:
> select * from slack_user;
Make sure you get some useful data back. If not, check that the installation is complete or whether the access tokens are sufficient.
Before we query in anger, let’s take a quick look at the
slack_user table:
First, I want to see which users are not bots and are not deleted, as well as who has updated their account to use two-factor authentication:
👁 Image
For some reason, the Slackbot is not a bot! But I can see that two people may need a security reminder, if that was my concern.
Now let’s look at
slack_search table, which can zoom in more accurately on information:
👁 Image
Note that
channel is a JSON type, which at first would seem a bit problematic. However, you can use the
->> operator to extract text. You must specify the query in the
where clause to query this table.
Here is a quick search for mentions of “ChatGPT” in the workspace’s channels:
👁 Image
But we might want a bit more information on when that was mentioned:
👁 Image
Using the
slack_conversation table, we can find out about popular channels by counting the members:
select name,num_members from slack_conversation where
num_members is not null
order by num_members desc
limit 5;
Because we are using SQL, we can of course get much more focused queries between tables, depending on what you need to find out.
Hopefully, you can see that Steampipe is a useful tool for retrieving valuable data, and that this Slack plug-in provides a good example of what we can get out. Having a prepared list of useful SQL queries can save you time if you only have limited access to a system — a good way to get the data you need in a flexible format of your choice.
