These queries help you find user memberships in the Mattermost database. Tested against schema version 5.24.0

channel-and-team-audits.sql

-- Gets all channel memberships for Open and Private channels
SELECT t.DisplayName as TeamName,
	   c.DisplayName as ChannelName,
	   c.Type, -- O is a public channel, P is a private channel
	   u.FirstName,
	   u.LastName,
	   u.Username,
	   u.Email,
	   IF (cm.LastViewedAt!=0, FROM_UNIXTIME((cm.LastViewedAt/1000)), 'Never') AS LastViewed
FROM ChannelMembers cm
	JOIN Channels c ON cm.ChannelId = c.Id
	JOIN Users u on cm.UserId = u.Id
	JOIN Teams t ON c.TeamId = t.Id
WHERE
	u.DeleteAt = 0 -- Only active users
	AND t.Name = 'a-team' -- Filter by team
ORDER BY t.name, c.name;

-- Gets all team members
SELECT t.DisplayName as TeamName,
	   u.FirstName,
	   u.LastName,
	   u.Username,
	   u.Email,
	   tm.SchemeAdmin,
	   tm.SchemeGuest
FROM Teams t
	JOIN TeamMembers tm ON tm.TeamId = t.Id
	JOIN Users u ON tm.UserId = u.Id
WHERE 
	tm.DeleteAt = 0
	AND u.DeleteAt = 0 -- Only active users
	AND t.Name = 'a-team' -- Filter by team
ORDER BY t.Name;
	
-- Gets all direct and group message channel memberships
SELECT c.DisplayName as ChannelName, -- Direct messages don't have a display name
	   c.Type, -- G is group message, D is direct message
	   u.FirstName,
	   u.LastName,
	   u.Username,
	   u.Email,
	   IF (cm.LastViewedAt!=0, FROM_UNIXTIME((cm.LastViewedAt/1000)), 'Never') AS LastViewed
FROM Channels c
	JOIN ChannelMembers cm ON cm.ChannelId = c.Id
	JOIN Users u on cm.UserId = u.Id
WHERE
	c.Type IN ('G', 'D')
ORDER BY c.name;