navigating-mattermost-database.md

Navigating The Mattermost Database

NOTE: I highly recommend using a SQL client like pgAdmin or MySQL Workbench

Update: If you want to find out how many people have logged into your Mattermost server in the last 90 days this query is much more perfomant than the one using the Audits table:

MySQL

SELECT COUNT(*)
FROM Users u 
	JOIN Status s 
	ON s.UserId = u.Id 
WHERE s.LastActivityAt > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 90 DAY)) * 1000;

PostgreSQL

SELECT COUNT(*)
FROM Users u 
	JOIN Status s 
	ON s.UserId = u.Id 
WHERE s.LastActivityAt > ROUND((EXTRACT(epoch from (NOW() - INTERVAL '90 day')))) * 1000 ;

---

Behind the scenes, Mattermost is powered by a relational database management system, or RDBMS. Whether it’s MySQL or PostgreSQL, an RDBMS stores data in tables, which have rows and columns. But the real power of an RDBMS is the ability to write SQL queries that can give you powerful reporting features if you know how to query it correctly.

By the end of this series, you will be able to: - Search Mattermost records by timestamps - Find specific records by ID number - Use SQL joins to connect tables together to generate reports

NOTE All of these queries are non-destructive, i.e. they only use SELECT Doing any destructive queries against the Mattermost database (UPDATE,INSERT, etc.) is extremely discouraged because it will probably corrupt your database. If you want to query

If you need to make changes to Mattermost, like deleting a user or updating a channel name, do it via the System Console, the command line interface, or the Mattermost API. This will let Mattermost make sure all of the possible changes, and will log the request in the Mattermost log, the proxy log, and the Audits table, which is covered in Part 3.

Part 0 - Connecting to the Mattermost database

Whether you use MySQL or PostgreSQL, the easiest way to query the Mattermost database is with a client for that platform. For PostgreSQL you can use pgAdmin, and for MySQL you can use MySQL Workbench. There are numerous other clients, both paid, free, and open source that you can use, so try one out until you

For safety, these reports should be run on a user with limited permissions. Here’s how to create a user:

MySQL

CREATE USER 'mm_reporting'@'%' IDENTIFIED BY 'really_secure_password';
GRANT SELECT ON mattermost.* TO 'mm_reporting'@'%';

PostgreSQL

1. Log into Postgres as the Postgres user

sudo su postgres

psql

2. Run these commands:

GRANT CONNECT ON DATABASE mattermost TO mm_reporting;
GRANT USAGE ON SCHEMA public TO mm_reporting;

3. For each table you want that user to have access to, run this:

GRANT SELECT ON <Tablename> TO mm_reporting;

4. Exit psql by typing \q

5. Verify the user can connect by running this:

psql —username=mm_reporting mattermost

Note: If Mattermost and your RDBMS are on the same machine, follow the instructions from our installation documentation to allow access from outside your machine, or (even better) set up an SSH tunnel to the server to encrypt the communication to the database.

Part 1 - Mattermost Timestamps

Because of the real-time nature of Mattermost, timestamps in the system are stored using the milliseconds since the Unix Epoc. This is because storing only the seconds would mean that posts that come in during the same second wouldn’t be ordered correctly. But it can make reading the time difficult.

Getting a human-readable time

To get a human-readable time out of them, use the following methods

MySQL

SELECT FROM_UNIXTIME((FieldName/1000));

PostgreSQL

> SELECT to_timestamp((FieldName/1000));

So to find the creation date for users, you’d run this query:

MySQL

SELECT UserName, FROM_UNIXTIME((CreateAt/1000)) AS CreationDate FROM Users;

Result +-------------------+--------------------------+ | UserName | CreationDate | +-------------------+--------------------------+ | joe | 2018-12-19 19:07:16.2000 | | gloria.allen | 2018-12-07 16:12:44.0350 | | denise.washington | 2018-12-07 16:12:44.5980 | | paul_work | 2018-03-26 17:45:30.3220 | | admin | 2018-12-19 19:00:39.2710 | +-------------------+--------------------------+

PostgreSQL

SELECT UserName, to_timestamp((CreateAt/1000)) AS CreationDate FROM Users;

Check your database engine’s documentation for options to create specific date formats.

Getting the current time

There are two different ways of getting the timestamp value from your database. The easiest is used for precision down to one second:

MySQL

> SELECT UNIX_TIMESTAMP() * 1000;

PostgreSQL

SELECT ROUND((EXTRACT(epoch from NOW())))

But if you need millisecond precision, use these:

MySQL

ROUND(UNIX_TIMESTAMP() + MICROSECOND(NOW(4))/1000)

PostgreSQL

SELECT ROUND((EXTRACT(epoch from NOW()) * 1000))

You can use these to find out how old a record is. For instance, you can use this query to see how long ago teams were created:

MySQL

SELECT DisplayName, ((UNIX_TIMESTAMP() * 1000) - CreateAt)/1000 AS SecondsSinceCreation FROM Teams

PostgreSQL

SELECT DisplayName, (ROUND((EXTRACT(epoch from NOW()) * 1000)) - CreateAt)/1000 AS SecondsSinceCreation FROM Teams

Getting a specific time

To get a specific time

MySQL

UNIX_TIMESTAMP('2018-11-01 00:00:00') * 1000

PostgreSQL

ROUND((EXTRACT(epoch from (TIMESTAMP '2018-11-01 00:00:00')))) * 1000	

Getting a relative time

Getting a relative time is similar to getting the timestamp, but you have to specify the date. Here are examples of how to get a timestamp for the date 30 days ago:

MySQL

UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY) * 1000

PostgreSQL

ROUND((EXTRACT(epoch from (NOW() - INTERVAL '30 day')))) * 1000

So to find the users who were created more than 30 days ago, you’d run these queries.

MySQL

SELECT Username, Email FROM Users WHERE CreateAt < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY)) * 1000

PostgreSQL

SELECT Username, Email FROM Users WHERE CreateAt < ROUND((EXTRACT(epoch from (NOW() - INTERVAL '30 day')))) * 1000 

In the next installment of this series, I’ll talk about how Mattermost ID attributes work and how you can use the database to link posts, channels, teams, and users together.

The Audits Table

Nearly all interactions with Mattermost are stored in the Audits table, from user logins to webhook access. In addition to the timestamp, it stores the following information:

— The ID of the user making the request (if any) — The URI of the request, e.g. /api/v4/users/login — Any extra information about the request, such as whether authentication was successful — The IP address that sent the request — The ID of the user’s session (if any)

As you can see, this is really helpful information for diagnosing issues. For example, if a webhook is receiving too many requests you can check this table for the IP address, and then use the firewall on the Mattermost Server to block that IP address from sending requests while you figure out what’s going on.

Mattermost ID fields

Just about everything Mattermost does, from getting a request

In this post I’ll give you an overview of some of the tables in Mattermost that have useful information for SQL queries, and give you some tips on formatting them.

Users

One common question is how to view all active and inactive users in Mattermost. This query uses the

SELECT 
	Username, 
	Email, 
	CASE AuthService
		WHEN '' then 'Email'
		ELSE AuthService
	END as Auth_Service 
	CASE (DeleteAt > 0 && DeleteAt < UNIX_TIMESTAMP() * 1000)
		WHEN 0 then 'Active'
		WHEN 1 then 'Inactive'
	END as Status
FROM Users

Audits

Putting it together

Normalization

Because Mattermost’s databases are normalized, they don’t create duplicate information, and instead relate records with each other using key fields. This creates a single, [immutable] field that lets other parts of Mattermost link to each other.

These ID fields look like this: asjdfklsjkgljkgleion

And the relationships in the Mattermost database are shown by putting the data structure name before ID. For example, TeamId, or UserId.

Linking tables with joins

The best way to use ID fields in queries is in JOINs. This can let you pull different, related information from the Mattermost database with one query.

A fairly common task that you can do with the Audits table is to show users who haven’t logged in for a certain time period. Logins are stored in the Audits database as records with the action of '/api/v4/users/login' and the extrainfo of ’success’. Here’s the query you’d use to find the last time every user logged in successfully in Mattermost.

MySQL

SELECT u.UserName, u.Email, FROM_UNIXTIME((lastlogin.LastLogin/1000)) as last_login
FROM Users u
INNER JOIN
    (SELECT UserId, MAX(CreateAt) as LastLogin FROM Audits WHERE Audits.action = '/api/v4/users/login' AND Audits.extrainfo LIKE 'success%' GROUP BY UserId) lastlogin 
ON u.Id = lastlogin.UserId

PostgreSQL

SELECT u.UserName, u.Email, to_timestamp((lastlogin.LastLogin/1000)) as last_login
FROM Users u
INNER JOIN
    (SELECT UserId, MAX(CreateAt) as LastLogin FROM Audits WHERE Audits.action = '/api/v4/users/login' AND Audits.extrainfo LIKE 'success%' GROUP BY UserId) lastlogin
ON u.Id = lastlogin.UserId

Thanks for this, most useful.

Another example query is a basic Channels report. This one produces a list of team/channel/status/creator/members/messages/when it was last active (days since and date)

		SELECT Teams.DisplayName AS TeamName, Channels.Id AS ChanId, Channels.DisplayName AS ChanName, Channels.Type AS ChanType,  FROM_UNIXTIME(Channels.LastPostAt / 1000) AS Last_Activity, Channels.DeleteAt AS DeletedDate,
		CONCAT(Users.Firstname, ' ', Users.LastName) AS CreatorName,
		Channels.TotalMsgCount, DATEDIFF(NOW(),FROM_UNIXTIME(Channels.LastPostAt / 1000)) AS ActiveTheseDaysAgo
		FROM Channels
		JOIN Teams ON Channels.TeamID = Teams.Id
		JOIN Users ON Channels.CreatorId = Users.Id
		ORDER BY TeamName,ChanName
		```