I see some useful reporting tools in the system console for seeing active users, but we are looking for a way to identify inactive users (so we may deactivate their accounts if they aren’t actively using Mattermost). Am I overlooking a way to identify this type of reporting?
1 Like
Hi @user2019,
These are all the statistics we currently have: https://docs.mattermost.com/administration/statistics.html. What you’re proposing sounds like a good feature request.
Could you kindly pass that feature request to the development team? Thanks
I would love to see a way to report on inactive users as well. Was a feature request ever submitted? I can’t seem to find anything in the open (or closed) github issues.
I do suppose that It might be possible to develop a custom plugin that would query the database for users who haven’t signed in before a designated timestamp, and return the values of matching users. Just a thought…
Hi, everyone.
The fastest way to query for inactive users is to pull the information directly from the database as mentioned by @XxLilBoPeepsxX:
SELECT * FROM Users WHERE DeleteAt != 0;
In terms of reporting, I believe that you would like to see this in the Statistics page. I have created an internal request to follow up if we have any plans on implementing this any time in the future.
1 Like
Exposing this somehow via the REST interface would also be useful, potentially much more useful than simply reporting some stats within the application. For my use-case, a way to pull lists of users inactive for N days (or whatever time resolution seems practical to developers) would be extremely useful, so that I can then flag those users, notify them they’re pending deletion, or whatever.
While I’m on it… a way to identify channels that haven’t had recent activity could also be useful, but I’m betting that’s an entirely separate can of worms (and a different feature request).
1 Like
In terms of reporting, I believe that you would like to see this in the Statistics page.
Because the statistics page is just counts of things, you’re wrong about where I (and probably others) would like to see this. Inactive user reporting is most useful for managing those users (notifying/disabling/deleting specific users). For that reason it would seem to me that it would be better implemented as a search criteria in the System Console → Users section. Personally I’d be happy with a REST call that could give me a list of all users inactive for N days or more, though.
SELECT * FROM Users WHERE DeleteAt != 0;
I see nothing in the results of this query that would indicate last activity for a user. The schema isn’t very specific about the uses of each table column, but it seems to me tracking last activity of a user would at least require a join with the Posts table, and probably with a few others as well if you wanted to catch meta-activity like channel joins.
Here is a bit of a further dive into the command that @ahmaddanial provided, with some additions.
The mattermost instance uses Epoch time to store time values, and in the Status table I have found that the user ID as well as the Epoch timestamp of their last activity are contained.
Utilizing the SQL CLI command as follows, you can write all UserId and LastActivityAt values to a file, for further analysis as explained further in this post.
SELECT UserId,LastActivityAt FROM Status INTO OUTFILE '/tmp/test4.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
In the command above, you are outputting the contents of the two aforementioned tables into the file /tmp/test4.csv, where each row queried from the database contains the User ID as well as the Epoch time formatted stamp of the last time they were active.
Here is an example of the output you should expect to see from your SQL query, as well as a sample of data from my own Mattermost Instance, which we will be working with a bit later in this post.
Query output:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.22 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use mattermost;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT UserId,LastActivityAt FROM Status INTO OUTFILE '/tmp/output.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';
Query OK, 43 rows affected (0.00 sec)
mysql>
And now for the information which was written to the file:
14mntogm5ff67jp75j15p1qnnr,1601015368224
1t8eejqpm3gkxr95o51y7zqweo,1600805793921
433r8gsyffrepczprymek6ecky,1605988064332
45exxzp9dj8hpgp65feqwii54a,1601850386879
4zt4bykmt7gqdyizmmmmeftjme,1603373266208
5nxnfcrtpfdf5fsjznmbrzdc1e,1603122003488
79kzmd6ds78gjmujnkfzmqtb8h,1605473281051
8gigbncfft8htn8aoyosyiem9w,1605645891797
8sg9cqztxjngdnbbbwhw643eae,1601256715987
95zjx1su7fyzjrbopxqz4ms1we,1600645344475
d9s6yrfpk3bfme8h1ad4zwgzia,1603869451323
deqoroxry3fj7c3acw6gtky6iw,1604026827343
So now we have the User ID’s and Epoch formatted timestamp of the time each user was last active.
At this point, your probably thinking “great, where is he going with this, I can’t even understand all those numbers, they make no sense,” and that’s alright, me too, seriously! 
Now we have a nice CSV file which is great for importing into Google Sheets!
Using a file transfer protocol such as SFTP or FTP, or simply copy/paste into a CSV file that is located on the machine you are going to open a new Google Spreadsheet on (if you have fewer user, I only have about 100, so it wasn’t a huge task to do)
Epoch Time is the amount of seconds since the Epoch, or the date 1970-01-01T00:00:00Z. Knowing this, lets import into our google sheet. At this point, as you continue to read this post, please refer to this demonstration spreadsheet that I have created, which can be accessed here.. This is going to be extremely helpful in explaining the concept without needing to take a large amount of screenshots, which is very time consuming, and I strongly recommend viewing this document.
Refer to Column D, and take note of the function indicated in orange at the top of the column. This function has been individually applied to each row in the column, and each of the columns, which is obviously a lot of work.
Lets take a look at the math of the function, where B2 equals 1601015368224
=B2/1000/60/60/24 + DATE(1970,1,1)
We want to end up converting the timestamp from the database into days since Epoch Zero.
We know that there are 1000 milliseconds in a second, so we first divide the value by 1000.
1601015368224/1000=1601015368.22
We now have the date the user was last active in seconds
Now, we convert to minutes. We know that there are 60 seconds in a minute, so we divide by 60:
1601015368.22 / 60=26683589.4703
Now that we have the number of minutes since the last time the user was active, we need to convert this number to hours, again, dividing by 60, as there are 60 minutes in an hour:
26683589.4703 / 60 =4 44726.491172
Now, we know there are 24 hours in a day, so we take the number of hours, and divide by 24:
444726.491172 / 24 = 18530.2704655
So, in the end of all this division, we know that the user was last active roughly 18530 days after Epoch Zero. But that still doesn’t answer our question, does it? We need to know what day is 18530 days after Epoch Zero. So lets do it!
This is the point where we’re all going to pause and be thankful for technology because my fingers are starting to ache quite a bit from typing for the last two hours doing research and writing this post 
To sum it up, you add the number of days, which we just computed with math, to the date of Epoch Zero: 1970-01-01, which can easily be done in Google Sheets, in a function, as you can see referenced in the D1 Row. I used google to check my math, and sure enough, adding the number of days that we calculated here, to Epoch Zero, exactly matched the last active date!
(I tried to upload images it wouldn’t let me, I think my post is getting too long )
Anyways, we’re at the point where we have a formula function that we know works, but who wants to copy and paste the same thing by hand every single time that you upload new data? Exactly, nobody!
That’s where the Google Sheets Array function comes in handy (reference row E1 in the google sheet)
Using this function, we simply specify the first and last row of the data we want to apply our function to, and the data between the start and end will all be processed as well!
As a result, you now have the UserID’s and human-formatted dates of last activity for your report, and can use conditional formatting to highlight all users not active before a specific date, therefore singling out specific inactive users! (See sheet two, called Final Output in which I have setup conditional formatting to show all users not active within the last month)
I hope this helps, and I’m thinking I’m going to develop a further plugin of some form that will do this automatically!
Please feel free to ask any additional questions or let me know what you would like to see in a plugin/integration, as I’m most certainly open to suggestions!
1 Like
Hi
I’m also interested in this, and have come to the same solutions as you guys. I use a psql select to obtain user information including the last update date and the last activity date:
SELECT users.username, users.firstname, users.lastname, users.email, to_timestamp(users.createat/1000) AS createat, to_timestamp(users.updateat/1000) AS updateat, to_timestamp(sessions.lastactivityat/1000) AS LastActivityAt from users left join sessions on sessions.userid = users.id order by users.username, lastactivityat;
I hoped that using this information I could decide which users I could delete, but alas, when analyzing the output I found a colleague with an empty lastactivityat field, and an updateat from last month, that I had been chatting with on mattermost just yesterday.
If I automate this, as I intend to, he would have been unjustly removed.
Am I doing something wrong? Or is lastactivityat not the right place to look for the information we need? Has anyone figured this out?
BTW, apparently DeleteAt != 0 gives users that have been deactivated by the administrator, it has nothing to do with real user activity (or with “deleted” users).
I haven’t seen a user before that doesn’t have the lastactivityat entry in the database, that’s very interesting. Was this person deactivated or something along those lines previously?
The solution that I had posted previously in this topic should work for all Mattermost instances, from my understanding. Maybe trying that could help as well?
I just realized I was taking lastactivityat from the sessions table, while your solution uses the status table. I tried to get my data from status and, sure enough, all users have data on the lastactivityat column.
The user I mentioned was never removed or deactivated. Actually, he’s new and has been using our mattermost installation for just a month or so. There are a few other users with an empty lastactivityat field on the sessions table, but all of them have proper dates on the status table, so that is what I’ll use.
Thank you!
Glad to be able to help! I hope it goes well for you, and let me know if there is anything I can provide further assistance with!
1 Like
I’m reviving this old thread with some information I’ve learned, in case anyone comes looking for this later.
Since an actual feature to disable idle users doesn’t seem forthcoming, I wrote a tool for our site will auto-expire users from our system after they’ve been inactive for some number of weeks. I found some nuances in the database that could trip up anyone trying to reproduce this if they’re not careful.
The first big red flag is that there are some users in our database that have no record in the status table. 4.62% of my users (both active and deleted) fall into this camp. It’s not clear to me why this should be possible, or what it signifies. My best guess is that these are users who successfully created an account but never actually logged in, but that’s based entirely on speculation. The implication is that a simple JOIN of the user and status tables doesn’t give you a complete picture of user activity, as it would leave these users out of the result. I chose to work around these by considering user.updateat if the user had no row in the status table.
The second red flag is that user.updateat can be newer than the corresponding status.lastactivityat value. This is more speculation, but I suspect this indicates login activity without new posts. If you wanted ‘idleness’ to be reset by any activity at all, then you need to consider this value as well.
Since I was writing a bunch of python to do all the work for this anyway, the simplest solution for me was to collect the data I need using two separate queries of the database, merge the result into a Python structure for decision-making, and then do all of the deactivation work through the API.
These are my two queries:
SELECT
users.id,
users.username,
users.firstname,
users.lastname,
users.email,
(users.createat/1000) AS created,
(users.updateat/1000) AS updated
FROM
users
WHERE
users.deleteat = 0
ORDER BY users.username;
SELECT
users.id,
(status.lastactivityat/1000) AS lastactivity
FROM
users, status
WHERE
users.deleteat = 0 AND
status.userid = users.id
ORDER BY users.username;
I then merged those results into instances of a custom Python class User with instance variables for each of the important datapoints. The database stores date values as milliseconds since unix epoch (Jan 1 1970 00:00:00 UTC), and the queries above convert these to seconds.
To check idleness, generate a reasonable epoch value for your idle threshold and do simple numeric comparisons. A rough sketch in python of making the ‘idle’ decision looks like this:
def user_is_idle(user: User, idle_weeks: int) -> bool:
now = datetime.now(tz=UTC)
then = now - timedelta(weeks=idle_weeks)
# cast the result into epoch seconds
threshold = int(then.strftime("%s"))
if user.last_activity and user.last_activity > user.updated:
# user has a status.lastactivityat and it's the most recent
# timestamp
key_value = user.last_activity
else:
# either the user has no `status` row, or `status.lastactivityat`
# is older than `user.updateat`
key_value = user.updated
return key_value < threshold
The rest is a matter of either using the API or mmctl to delete the idle users. I would go this route rather than directly modifying the db for safety reasons. Using supported methods for modifying users ensures that you can’t miss steps or corrupt your data due to your own errors … anything that goes wrong at that point should be a reportable bug in Mattermost itself.
2 Likes
Thank you so much for circling back with the info, Matthew!
I’m not a python pro nor a newbie so… would there be an option to get a running version for this, ofc censored with placeholders 
This script looks so awesome and would do me such a big favor to deactivate inactive users