Table: slack_conversation - Query Slack Conversations using SQL

Slack is a popular communication tool used by teams for real-time messaging, archiving and search for modern teams. It organizes team conversations in open channels. The conversations in Slack are organized in channels, private groups, and direct messages.

Table Usage Guide

The slack_conversation table provides insights into conversations within Slack. As a team manager or a member, explore conversation-specific details through this table, including the type of conversation, topic, purpose, and members involved. Utilize it to uncover information about conversations, such as their purpose, the members involved, and the specific details of each conversation.

Examples

Conversations shared with external workspaces

Discover the segments that are shared with external workspaces within your Slack conversations. This can be useful to understand the extent of your organization's collaboration with external entities.

select
  id,
  name,
  is_shared
from
  slack_conversation
where
  is_ext_shared;

select
  id,
  name,
  is_shared
from
  slack_conversation
where
  is_ext_shared = 1;

Most popular conversations

Discover the most engaged discussions by identifying the top five conversations with the highest number of members. This can help in understanding user engagement and participation trends within your Slack workspace.

select
  name,
  num_members
from
  slack_conversation
where
  num_members is not null
order by
  num_members desc
limit
  5;

select
  name,
  num_members
from
  slack_conversation
where
  num_members is not null
order by
  num_members desc
limit
  5;

The #general channel (whatever it is called)

select
  *
from
  slack_conversation
where
  is_general;

select
  *
from
  slack_conversation
where
  is_general = 1;

Get conversation by ID

select
  *
from
  slack_conversation
where
  id = 'C02GC4A7Q';

select
  *
from
  slack_conversation
where
  id = 'C02GC4A7Q';

All private channel and group conversations

Explore the private discussions taking place within channels and groups on Slack. This query is useful for administrators who want to monitor the content and frequency of private conversations for compliance or community management purposes.

select
  name,
  created,
  is_channel,
  is_group,
  is_private
from
  slack_conversation
where
  is_private
  and (
    is_channel
    or (
      is_group
      and not is_mpim
    )
  )
order by
  name;

select
  name,
  created,
  is_channel,
  is_group,
  is_private
from
  slack_conversation
where
  is_private = 1
  and (
    is_channel = 1
    or (
      is_group = 1
      and is_mpim = 0
    )
  )
order by
  name;

Schema for slack_conversation

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
created	timestamp with time zone		Time when the conversation was created.
creator	text		ID of the user who created the conversation.
id	text	=	ID of the conversation.
is_archived	boolean		If true, the conversation has been archived.
is_channel	boolean		If true, the conversation is a public channel inside the workspace.
is_ext_shared	boolean		If true, the conversation is shared with an external workspace.
is_general	boolean		If true, this is the #general public channel (even if it's been renamed).
is_group	boolean		If true, the conversation is a private channel.
is_im	boolean		If true, the conversation is a direct message between two individuals or a user and a bot.
is_member	boolean		If true, the user running this query is a member of this conversation.
is_mpim	boolean		If true, this is an unnamed private conversation between multiple users.
is_org_shared	boolean		If true, the conversation is shared between multiple workspaces within the same Enterprise Grid.
is_pending_ext_shared	boolean		If true, the conversation hopes is awaiting approval to become is_ext_shared.
is_private	boolean		If true, the conversation is privileged between two or more members.
is_shared	boolean		If true, the conversation is shared across multiple workspaces. See also is_ext_shared.
name	text		Name of the conversation.
name_normalized	text		Name of the conversation normalized into simple ASCII characters.
num_members	bigint		Number of members in the conversation. Not set if the conversation is individual messages between fixed number of users.
purpose	text		Purpose of the conversation / channel.
purpose_creator	text		User who created the purpose for the conversation.
purpose_last_set	timestamp with time zone		Time when the purpose was last set.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
topic	text		Topic of the conversation / channel.
topic_creator	text		User who created the topic for the conversation.
topic_last_set	timestamp with time zone		Time when the topic was last set.
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_conversation