Lastseen devices/app/token based auth

📑 How to
,
1

I’ve seen efforts but no implementation yet, so it might be helpful for someone.

with occ user:list you see only web based login’s
I want to see the device based auth. also like the mobile app “nc-photos”

this is my solution

  1. an external .cnf for the database Connection
  2. .sh for the query

.cnf file

i created a read only user in mysql for this operation.
file placed in a corresponding protected area and secured with chmod 600

[client]
user=<username>
password=<password>
host=<hostname>
database=<database name>

.sh script

A few queries were added and the output was corrected accordingly.
Unix time format conversion.
Output in a table format.
I have selected the columns UID, name and last activity so that the user, the app and the timestamp are visible

#!/bin/bash

# MySQL database parameters

CONFIG_FILE="/path/file.cnf"
TABLE_NAME="oc_authtoken"

# Perform query

RESULT=$(mysql --defaults-file="$CONFIG_FILE" -se "SELECT uid, name, last_activity FROM $TABLE_NAME;")

# Display result

if [[ -z $RESULT ]]; then
  echo "no result"
else

# Format output

echo "UID                  APP                  last seen"
echo "------------------------------------------------------"
echo "$RESULT" | awk -F'\t' '{
    # Replace empty entries with "NULL" or another placeholder

    for (i=1; i<=NF; i++) {
        if ($i == "") {
            $i = "NULL"  # Platzhalter für leere Werte
        }
    }
    # Convert Unix timestamps to human-readable format

    if ($3 != "NULL") {
        command = "date -d @" $3 " +\"%Y-%m-%d %H:%M:%S\""
        command | getline timestamp

        close(command)
    } else {
        timestamp = "NULL"
    }

    # Output with tolerant widths

    printf "%-20s %-20s %-20s\n", $1, $2, timestamp;
}'

fi

example output

UID                  APP                  last seen
------------------------------------------------------
User1                APP1                 2025-03-18 08:15:15
User2                another app          2025-03-15 14:40:05
...
2

The → nc-who ← script can shows all logins and supports MySQL/MariaDB and PostgreSQL database backends.

Simply run nc-who monitor and select [ 2 ] to list all logins.

Example screen:

root@box:~# nc-who monitor

  This script looks up for logged in users on your Nextcloud in "/var/www/nextcloud"
  it updates at an adjustable refresh rate and lists ordered by last activity

  press [1] for cookie based logins, [2] for all, [q] to quit

h.t.h.

ernolf

3

That’s obviously even more extensive.
I need the data myself to process it further, so my script is sufficient (since no input is required) and I just wanted to share it.

But thanks for sharing, now there’s a small and a comprehensive solution :slight_smile:

1 Like
4

Did you run nc-who?

It generates a temporary file where you can grab the used SQL Query.

This is the query it works with internaly (for MySQL):

SELECT FROM_UNIXTIME(at.last_activity) AS last_activity,
        CASE
        WHEN at.type = '0' AND at.remember = '1' THEN (SELECT us.status FROM oc_user_status us WHERE us.user_id = at.uid)
        ELSE 'n.a.'
    END AS status,
    at.uid, at.name AS client
FROM oc_authtoken at 
ORDER BY at.last_activity DESC

Maybe you can use that for your solution.

Much and good luck,
ernolf