SQL command for `nc-who last` without the Guests app

[ernolf]

This is basicaly what hapens;
Based on some key data, such as the presence of the guests app,, an SQL query for the used database backend (MySQL or PostgreSQL) is created by the script that leads to this output.

If you append --trace to the command, you'l get the constructed Query instead of the output, You can copy and paste that into the SQL Console (nc-sql) to further analisys..

Here an exampel output (for MariaDB):

root@host:~# nc-who last --trace
==TRACE=start=
Frame 0: Function 'nc_last' in /proc/1020211/fd/63:64 (Arguments: 0)
global bool values  - do_integrity_check=_ do_update=_ dc=: trace=: do_restore_backup=_ check=_ pager=_ docker=_ rt=: aio=_ run2=: irun2=_
Database Backend: mysql
==TRACE=end===
----------
SQL QUERY:
----------
        SELECT uid, last_login
        FROM (
            SELECT users.uid,
                COALESCE(FROM_UNIXTIME(CAST(p.configvalue AS integer)), 'never') AS last_login
            FROM oc_users users
            LEFT JOIN oc_preferences p ON users.uid = p.userid AND p.appid = 'login'
        UNION ALL
            SELECT guests_users.uid,
                COALESCE(FROM_UNIXTIME(CAST(p.configvalue AS integer)), 'never') AS last_login
            FROM oc_guests_users guests_users
            LEFT JOIN oc_preferences p ON guests_users.uid = p.userid AND p.appid = 'login'
        ) combined
        ORDER BY
            CASE WHEN last_login = 'never' THEN 1 ELSE 0 END, last_login DESC;
----------

Originally posted by @ernolf in #2

---

I have entered the SQL command in PhpMyAdmin, but it comes up:
#1146 - Tabelle 'c1nextcloud.oc_guests_users' existiert nicht

This is because I have not installed the Guests App.

Please what is the SQL command without the Guests app?

Originally posted by @Sanookmakmak in #2

---

Hi @Sanookmakmak

You took the sql command from the example.

You can and should generate the sql query yourself and run it in nc-sql as I explained in the post from where you took the example code from.

---

ernolf