Hello all! I am working on a couple of Mattermost plugins, one of which is an enhancement to user activity monitoring (still will be GDPR compliant, this is more about inactive users, ranking users with most messages, etc). One of the features that I’ve seen asked about multiple times here that I’m working on is a way to see inactive users, so that you can remove them from your installation. While I’m not entirely finished with the plugin yet, I do have a simple one-line bash script that you can run easily from your Linux command line, which will provide you the user ID and human-formatted date of the last time of activity. Here’s the script (Note that you will need to fill in a few fields)
mysql -u <user> -p<password> -e 'use mattermost;SELECT userId AS "User ID", FROM_UNIXTIME(LastActivityAt / 1000,"%Y-%m-%d") AS "Last Active At" FROM Status;'
All you need to do with the above code is put it into a bash script ending in .sh, fill in the username and password of a permitted database user, and depending on your environment, change the file permissions to allow execution. Once you run the script, you will end up with output such as this:
+----------------------------+----------------+
| User ID | Last Active At |
+----------------------------+----------------+
| 14mntogm5ff67jp75j15p1qnnr | 2020-09-25 |
| 1ecnhaegtjb47gu8am3t4fhjho | 2021-01-05 |
| 1t8eejqpm3gkxr95o51y7zqweo | 2020-09-22 |
| 433r8gsyffrepczprymek6ecky | 2020-11-21 |
| 45exxzp9dj8hpgp65feqwii54a | 2020-10-04 |
| 4zt4bykmt7gqdyizmmmmeftjme | 2020-10-22 |
| 5nxnfcrtpfdf5fsjznmbrzdc1e | 2020-10-19 |
| 79kzmd6ds78gjmujnkfzmqtb8h | 2020-12-19 |
| 8gigbncfft8htn8aoyosyiem9w | 2020-11-17 |
| 8o3kuxrbxfye8mmmt4cs31pfpc | 2021-01-11 |
| 8sg9cqztxjngdnbbbwhw643eae | 2020-09-27 |
| 95zjx1su7fyzjrbopxqz4ms1we | 2020-09-20 |
| d9s6yrfpk3bfme8h1ad4zwgzia | 2020-12-03 |
| deqoroxry3fj7c3acw6gtky6iw | 2020-10-29 |
| drinshk1p7ye3mzo33ri8pei4e | 2020-11-07 |
+----------------------------+----------------+
In addition to the above script, if your looking for a CSV to import into a spreadsheet to make date sorting easier, you can convert the output of the script to CSV by adding the following to the command line when you execute the script: | sed 's/\t/,/g' > out.csv, which will output the same data into the file out.csv and will look like this:
User ID,Last Active At
14mntogm5ff67jp75j15p1qnnr,2020-09-25
1ecnhaegtjb47gu8am3t4fhjho,2021-01-05
1t8eejqpm3gkxr95o51y7zqweo,2020-09-22
433r8gsyffrepczprymek6ecky,2020-11-21
45exxzp9dj8hpgp65feqwii54a,2020-10-04
4zt4bykmt7gqdyizmmmmeftjme,2020-10-22
5nxnfcrtpfdf5fsjznmbrzdc1e,2020-10-19
79kzmd6ds78gjmujnkfzmqtb8h,2020-12-19
8gigbncfft8htn8aoyosyiem9w,2020-11-17
8o3kuxrbxfye8mmmt4cs31pfpc,2021-01-11
8sg9cqztxjngdnbbbwhw643eae,2020-09-27
95zjx1su7fyzjrbopxqz4ms1we,2020-09-20
d9s6yrfpk3bfme8h1ad4zwgzia,2020-12-03
deqoroxry3fj7c3acw6gtky6iw,2020-10-29
drinshk1p7ye3mzo33ri8pei4e,2020-11-07
ecszo1yxctdsukfhnsmfy99d4e,2020-09-26
gupenadzjfy9mb4hharkcp77qh,2020-09-21
h4euek54fiycpjtfkfztsx75jh,2020-10-17
I hope this is useful, and I’m looking forward to finally completing and releasing some of the projects I’ve been working on! Thanks!