steampipe plugin install slack

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

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;

Schema for slack_access_log

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
countbigintNumber of sequential logins from this device.
countrytextCountry the login originated from, if available. Often null.
date_firsttimestamp with time zoneDate of the first login in a sequence from this device.
date_lasttimestamp with time zoneDate of the last login in a sequence from this device.
iptextIP address the login came from.
isptextISP the login originated from, if available. Often null.
regiontextRegion the login originated from, if available. Often null.
user_agenttextUser agent of the device used for login.
user_idtextUnique identifier of the user
user_nametextName of the user.
workspace_domaintextThe domain name for the workspace.

Export

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