How to Identify Channels Converted from Group Messages

Problem:
There is a need to determine whether a specific Mattermost channel was originally created as a standard private channel or if it was converted from a group message. This distinction can be important for troubleshooting, auditing, or usage analysis.

Solution

Mattermost stores conversion information in the database. Specifically, the Posts table logs system events. When a group message is converted into a channel, the event type system_gm_to_channel is recorded. Using this, you can run queries to identify converted channels versus channels created as standard private channels.

SQL Queries

SELECT c.id
FROM Channels c
WHERE 
c.id IN (
    SELECT DISTINCT channelid
    FROM Posts
    WHERE type = 'system_gm_to_channel'
);

This query finds channel IDs for channels that were converted from group messages

*In case you need to add a filter for channel type, you can use the type column in channels table. Below are channel types in mattermost--
 
- O = Open/Public Channel (ChannelTypeOpen)
- P = Private Channel (ChannelTypePrivate)
- D = Direct Message (ChannelTypeDirect)
- G = Group Message (ChannelTypeGroup)