Table: slack_access_log - Query Slack Access Logs using SQL

Slack Access Logs are a record of user activity and actions within a Slack workspace. They provide detailed information about the events that occur in the workspace, such as user logins, file uploads, message postings, and other activities. These logs can be used for auditing purposes, troubleshooting, and analyzing user behavior.

Table Usage Guide

The slack_access_log table provides insights into user activity within a Slack workspace. As a system administrator or security analyst, you can explore detailed information about events in your workspace through this table, including user logins, file uploads, message postings, and more. Utilize it to audit user actions, troubleshoot issues, and analyze user behavior for security and compliance purposes.

Important Notes

• slack_access_log requires a paid Slack plan.

Examples

List all logins

Explore the volume of logins by counting all entries in the access log. This can be useful for assessing user activity and identifying potential security concerns.

select
  count(*)
from
  slack_access_log;

select
  count(*)
from
  slack_access_log;

All logins by a specific user

Discover the instances where a specific user has logged in, helping you to monitor user activity and identify any unusual patterns. This can be useful for auditing purposes or to detect potential security breaches.

select
  user_name,
  ip,
  user_agent,
  date_first
from
  slack_access_log
where
  user_name = 'jim.halpert'
order by
  date_first;

select
  user_name,
  ip,
  user_agent,
  date_first
from
  slack_access_log
where
  user_name = 'jim.halpert'
order by
  date_first;

IP addresses used by a specific user

Discover the segments that a specific user has accessed by analyzing their IP addresses. This query can be used to monitor user activity and identify any unusual patterns, enhancing security measures.

select
  user_name,
  ip,
  sum(count)
from
  slack_access_log
where
  user_name = 'jim.halpert'
group by
  user_name,
  ip
order by
  sum desc;

select
  user_name,
  ip,
  sum(count)
from
  slack_access_log
where
  user_name = 'jim.halpert'
group by
  user_name,
  ip
order by
  sum(count) desc;

Number of unique users by day

Explore the frequency of unique user activity on a daily basis. This can help in understanding user engagement trends and peak usage times.

select
  date(date_first) as day,
  count(distinct user_name)
from
  slack_access_log
group by
  day;

select
  date(date_first) as day,
  count(distinct user_name)
from
  slack_access_log
group by
  day;

Count of logins by OS

Determine the frequency of logins from different operating systems, allowing you to understand user preferences and tailor your platform's compatibility accordingly.

with count_by_os as (
  select
    user_agent,
    count,
    case
      when user_agent ilike '%android%' then 'Android'
      when user_agent ilike '%ios%' then 'iOS'
      when user_agent ilike '%macintosh%' then 'MacOS'
      when user_agent ilike '%windows%' then 'Windows'
      else 'Other'
    end as os
  from
    slack_access_log
)
select
  os,
  sum(count)
from
  count_by_os
group by
  os
order by
  sum desc;

with count_by_os as (
  select
    user_agent,
    count,
    case
      when user_agent like '%android%' then 'Android'
      when user_agent like '%ios%' then 'iOS'
      when user_agent like '%macintosh%' then 'MacOS'
      when user_agent like '%windows%' then 'Windows'
      else 'Other'
    end as os
  from
    slack_access_log
)
select
  os,
  sum(count)
from
  count_by_os
group by
  os
order by
  sum(count) desc;

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- slack

You can pass the configuration to the command with the --config argument:

steampipe_export_slack --config '<your_config>' slack_access_log