Script to list recently active users

ℹ️ Support
1

I wanted to be able to list recently active users, so I created this script:

#!/bin/bash
MINS=${1:-5}
TIME=$(( $(date +%s) - (( $MINS * 60 )) ))
dbname=nextcloud
dbtableprefix=oc_
SQLARGS=-t

mysql $SQLARGS $dbname -e "select uid,FROM_UNIXTIME(last_activity),name from ${dbtableprefix}authtoken where last_activity >= $TIME;"

The output looks like this:

+---------+------------------------------+---------------------------------------------------------------------------------------------------------------------+
| uid     | FROM_UNIXTIME(last_activity) | name                                                                                                                |
+---------+------------------------------+---------------------------------------------------------------------------------------------------------------------+
| user1   | 2020-03-30 11:11:53          | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36 |
| user2   | 2020-03-30 11:12:30          | Mozilla/5.0 (Windows NT 6.3; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36  |
| user3   | 2020-03-30 11:13:09          | Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:72.0) Gecko/20100101 Firefox/72.0                                  |
| user3   | 2020-03-30 11:13:12          | Mozilla/5.0 (Macintosh) mirall/2.6.4stable (build 20200303) (Nextcloud)                                             |
| user4   | 2020-03-30 11:13:15          | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/80.0.3987.132 Safari/537.36 |
| user5   | 2020-03-30 11:13:22          | Mozilla/5.0 (Windows) mirall/2.6.1 (build 12992)                                                                    |
| user6   | 2020-03-30 11:13:49          | Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36 |
+-------------------------+------------------------------+---------------------------------------------------------------------------------------------------------------------+

By default, the script shows tokens that have been active in the last 5 minutes. Add a numeric number of minutes on the command line to get more history.

For example, if you name the script “active_users.sh”, this will give you 1 day’s activity:
$ active_users.sh $(( 60 * 24 ))

The script assumes that you’re using mysql, and that you have ~/my.cnf on your system configured to allow scripted access to mysql:

$ cat ~/.my.cnf
[client]
user=oc_admin
password=blahblahblahblah

Where user and password are taken from your Nextcloud config/config.php

  • oc_admin is the value of dbuser
  • blahblahblahblah is the value of dbpassword
2 Likes