Table: slack_group - Query Slack Groups using SQL

Slack Groups are a feature within the Slack communication platform that allows users to create specific groups for targeted discussions. These groups can be created for various purposes, such as departmental communication, project-specific discussions, or even casual chat rooms. The groups can be public or private, and they can have any number of members.

Table Usage Guide

The slack_group table provides insights into the groups within a Slack workspace. As a workspace administrator, you can explore group-specific details through this table, including group name, purpose, and privacy status. Utilize it to manage and monitor the groups in your workspace, such as identifying inactive groups, tracking the purpose of each group, and ensuring the correct privacy settings are in place.

Examples

List all groups (includes deleted)

Explore which Slack groups have been deleted and how many users were in each group before deletion. This can help in understanding user participation and engagement levels across different groups.

select
  id,
  name,
  date_delete,
  user_count
from
  slack_group;

select
  id,
  name,
  date_delete,
  user_count
from
  slack_group;

List groups that are currently active

Identify the active groups within your Slack workspace, along with their user counts. This can help in assessing the active collaboration spaces and their scale within your organization.

select
  id,
  name,
  user_count
from
  slack_group
where
  deleted_by is not null;

select
  id,
  name,
  user_count
from
  slack_group
where
  deleted_by is not null;

List all groups a user is a member of

Discover the various groups that a specific user is associated with. This can be particularly useful to understand the user's roles and responsibilities within the organization.

select
  g.id,
  g.name
from
  slack_group as g,
  slack_user as u
where
  g.users ? u.id
  and u.email = 'dwight.schrute@dundermifflin.com';

select
  g.id,
  g.name
from
  slack_group as g,
  slack_user as u
where
  json_extract(g.users, u.id) is not null
  and u.email = 'dwight.schrute@dundermifflin.com';

List all user group membership pairs

Explore the relationships between user groups and their members in your Slack workspace. This query can be used to understand group composition, identify potential overlaps, and ensure appropriate access and permissions.

select
  g.name as group_name,
  u.email as user_email
from
  slack_group as g
  left join lateral jsonb_array_elements_text(g.users) as gu on true
  left join lateral (
    select
      id,
      email
    from
      slack_user
  ) as u on u.id = gu
order by
  g.name,
  u.email;

select
  g.name as group_name,
  u.email as user_email
from
  slack_group as g,
  json_each(g.users) as gu
  left join (
    select
      id,
      email
    from
      slack_user
  ) as u on u.id = gu.value
order by
  g.name,
  u.email;

Schema for slack_group

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
auto_type	text		The auto_type parameter can be admin for a Workspace Admins group, owner for a Workspace Owners group or null for a custom group.
created_by	text		User who created the group.
date_create	timestamp with time zone		Date when the group was created.
date_delete	timestamp with time zone		Date when the group was deleted.
date_update	timestamp with time zone		Date when the group was last updated.
deleted_by	text		User who deleted the group.
description	text		Description of the group.
handle	text		The handle parameter indicates the value used to notify group members via a mention without a leading @ sign.
id	text		ID of the group.
is_external	boolean		True if the group is external facing.
is_user_group	boolean		True if this is a user group.
name	text		Name of the group.
prefs	jsonb		The prefs parameter contains default channels and groups (private channels) that members of this group will be invited to upon joining.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
team_id	text		Team ID the group is defined in.
updated_by	text		User who last updated the group.
user_count	bigint		Number of users in the group.
users	jsonb		List of users (IDs) in the group.
workspace_domain	text	=, !=, ~~, ~~, !~~, !~~	The 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_group