Table: slack_access_log

Access log of logins to the Slack workspace. Logins are grouped into sequential runs from the same device using the count field.

Currently limited to the first 5,000 records.

NOTE: slack_access_log requires a paid Slack plan.

Examples

List all logins

select
  count(*)
from
  slack_access_log

All logins by a specific user

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

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

Number of unique users by day

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

Count of logins by OS

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;

.inspect slack_access_log

Logins to Slack, grouped by User, IP and User Agent.

Name	Type	Description
count	bigint	Number of sequential logins from this device.
country	text	Country the login originated from, if available. Often null.
date_first	timestamp without time zone	Date of the first login in a sequence from this device.
date_last	timestamp without time zone	Date of the last login in a sequence from this device.
ip	text	IP address the login came from.
isp	text	ISP the login originated from, if available. Often null.
region	text	Region the login originated from, if available. Often null.
user_agent	text	User agent of the device used for login.
user_id	text	Unique identifier of the user
user_name	text	Name of the user.

turbot/slack