The queries Mattermost 5.37.0 uses to genreate the System Statistics page in the System Console

system-stats.sql

-- Total Active Users (non-bots)
SELECT COUNT(DISTINCT u.Id) 
FROM Users AS u 
	LEFT JOIN Bots ON u.Id = Bots.UserId 
WHERE u.DeleteAt = 0 
	AND Bots.UserId IS NULL;

-- Total Teams
SELECT COUNT(*) FROM Teams 
WHERE DeleteAt = 0;

-- Total Channels
SELECT COUNT(Id) AS Value 
FROM Channels 
WHERE Type = 'O' OR Type = 'P';


-- Total Public Channels
SELECT COUNT(Id) AS Value 
FROM Channels 
WHERE Type = 'O';

-- Total Private Channels
SELECT COUNT(Id) AS Value 
FROM Channels 
WHERE Type = 'P';

-- Total Posts
SELECT COUNT(p.Id) AS Value 
FROM Posts p;

-- Total Sessions
SELECT COUNT(*)
FROM Sessions 
WHERE ExpiresAt > (UNIX_TIMESTAMP() * 1000);

-- Total Commands
SELECT COUNT(*) 
FROM Commands 
WHERE DeleteAt = 0;

-- Total Incoming Webhooks
SELECT COUNT(*)
FROM IncomingWebhooks 
WHERE DeleteAt = 0;

-- Total Outgoing Webhooks
SELECT COUNT(*)
FROM OutgoingWebhooks 
WHERE DeleteAt = 0;

-- Monthly Active Users
SELECT COUNT(*) 
FROM Status AS s 
	LEFT JOIN Bots ON s.UserId = Bots.UserId 
	LEFT JOIN Users ON s.UserId = Users.Id 
WHERE LastActivityAt > (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000)
	AND Bots.UserId IS NULL 
	AND Users.DeleteAt = 0;

-- Total Users including bots
SELECT COUNT(Id) 
FROM Users 
WHERE DeleteAt > 0;

-- Daily Active users
SELECT COUNT(*) 
FROM Status AS s 
	LEFT JOIN Bots ON s.UserId = Bots.UserId 
	LEFT JOIN Users ON s.UserId = Users.Id 
WHERE LastActivityAt > (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) * 1000) 
	AND Bots.UserId IS NULL 
	AND Users.DeleteAt = 0;

-- Monthly Active Users
SELECT COUNT(*) 
FROM Status AS s 
	LEFT JOIN Bots ON s.UserId = Bots.UserId 
	LEFT JOIN Users ON s.UserId = Users.Id 
WHERE LastActivityAt > (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000) 
	AND Bots.UserId IS NULL 
	AND Users.DeleteAt = 0;

-- Posts with hashtags
SELECT COUNT(p.Id) AS Value
FROM Posts p 
WHERE p.Hashtags <> '';

-- Posts with file attachments
SELECT COUNT(p.Id) AS Value 
FROM Posts p 
WHERE (p.FileIds <> '' OR p.Filenames <> '');

-- Total Posts by day for the last 30 days
SELECT DATE(FROM_UNIXTIME(Posts.CreateAt/1000)) AS Name, 
	COUNT(Posts.Id) AS Value
FROM Posts 
WHERE Posts.CreateAt <= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) * 1000)
	AND Posts.CreateAt >= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000)
GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt/1000))
ORDER BY Name DESC
LIMIT 30

-- Total Posts from Bots by day for the last 30 days
SELECT DATE(FROM_UNIXTIME(Posts.CreateAt/1000)) AS Name, 
	COUNT(Posts.Id) AS Value
FROM Posts 
	INNER JOIN Bots ON Posts.UserId = Bots.Userid 
WHERE Posts.CreateAt <= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) * 1000)
	AND Posts.CreateAt >= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000)
GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt/1000))
ORDER BY Name DESC
LIMIT 30;


-- Active Users With Posts by day for the last 30 days
SELECT DISTINCT DATE(FROM_UNIXTIME(Posts.CreateAt/1000)) AS Name, 
	COUNT(DISTINCT Posts.UserId) AS Value
FROM Posts 
WHERE Posts.CreateAt >= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 DAY)) * 1000) 
	AND Posts.CreateAt <= (UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000)
GROUP BY DATE(FROM_UNIXTIME(Posts.CreateAt/1000))
ORDER BY Name DESC
LIMIT 30;