announcement

Introducing the Slack Source Plugin

Herman Schaaf • December 20, 2022

Slack is a popular communication tool used by many organizations. With the CloudQuery Slack source plugin, you can now load Slack workspace data into Postgres, Snowflake, BigQuery, SQLite, or any other destination supported by CloudQuery.

As part of its initial release, the Slack plugin supports pulling data for the following Slack resources:

users into the slack_users and slack_user_presences tables,
teams into the slack_teams table,
channels into the slack_conversations table,
messages into the slack_conversation_histories and slack_conversation_replies tables,
files into the slack_files table

Let's look at a two use cases for the Slack plugin: one serious, one fun.

Use cases #

Slack Security #

It's important to make sure that your Slack workspace is secure and that you have visibility into who has access to your workspace. The Slack source plugin can help you with this task. With our data loaded into Postgres, we can start by writing a query to find out who has access to our workspace:

select name, profile->>'email' as email from slack_users

Now, let's filter this list down to users who don't have a company email address (in our case @cloudquery.io), and check whether these users are properly restricted:

select
    name,
    profile->>'email' as email,
    is_restricted,
    is_ultra_restricted,
    is_stranger
 from slack_users
 where
     not deleted
     and not is_bot
     and not name = 'slackbot'
     and profile->>'email' not like '%cloudquery.io'

We can also cross-reference user accounts against data from the Okta plugin to see if they should still have Slack access:

select
    su.name as name,
    su.profile->>'email' as email
from slack_users su
    left join okta_users ou
        on su.profile->>'email' = ou.profile->>'email'
where
    ou.id is null
    and not su.is_bot
    and not name = 'slackbot'
    and not su.deleted

The output of the above query will show us all Slack users who don't have an active Okta account, and therefore shouldn't have access to our Slack workspace.

Custom Analytics #

The Slack plugin can also be used to build custom analytics on top of your Slack workspace data. For example, let's say we want to find out which channels are the most active in our workspace. We can do this by writing a query that counts the number of messages in each channel:

select
     c.name as channel,
     count(r.*) as messages
 from slack_conversations c
     join slack_conversation_replies r on c.id = r.channel_id
 where
     c.is_channel
     and not c.is_archived
 group by c.name
 order by messages desc

(Note: the CloudQuery plugin only collects data for channels that the bot has been added to.)

Or, perhaps we are curious about the most active users in our workspace. We can write a query that counts the number of messages sent in public channels by each user:

select
    u.name, count(h.user)
from slack_conversation_histories h
    join slack_conversation_replies r on h.ts = r.conversation_history_ts
    join slack_users u on u.id = h.user
group by u.name order by count desc

We can even break this down by day, week, or month to graph how active users are over time:

select
     u.name,
     date_trunc('day', to_timestamp(round(h.ts::float))) as day,
     count(h.user)
from slack_conversation_histories h
     join slack_conversation_replies r on h.ts = r.conversation_history_ts
     join slack_users u on u.id = h.user
group by u.name, day
order by day, count desc

Getting Started #

To get started syncing Slack data, see the Slack source plugin documentation for instructions.

What's next #

The Slack API comes with some strict rate limits that mean syncing messages from channels with a long history can take a long time. We are thinking about ways to address this, please 👍 or comment on the GitHub issue if you are interested!

We are also going to continue expanding the Slack source plugin, adding support for more resources as they become available in the Slack API. If you are interested in a specific Slack resource, feel free to raise an issue on GitHub. Or if you need some help to get started, join us over on Discord, we'd love to help.