List of all logged in users

[Janusz]

Is it possible to get the list of all users which are currently logged in?

[nac]

Hello Janusz,

I'll have a go at this one. If your installation of MySQL/MariaDB has the JSON_EXTRACT function then you can use something like this:

Code: Select all

SELECT sus_login_name, sus_name FROM zzzzsys_user  WHERE zzzzsys_user_id IN (SELECT JSON_EXTRACT(sss_access, "$.session.zzzzsys_user_id") FROM zzzzsys_session)

If you have older versions then you could try:

Code: Select all

SELECT u.sus_login_name,u.sus_name FROM zzzzsys_user u, zzzzsys_session s WHERE INSTR(s.sss_access, u.zzzzsys_user_id)

This is not 100% fool proof as it assumes the values in zzzzsys_user.zzzzsys_user_id are globally unique but a quick test suggests it should be OK to use.

There are probably more efficient ways of doing this but these do work. Please note that the session table could contain users who are no longer active but have not logged out properly. There are ways to determine when a user in the session table was last active.

I hope this helps.

Neil

[Janusz]

Thanks a lot. The second option is working in my case.

[nac]

You are welcome.

I *think* you can find out how long ago a user was last active as well. Try this:

Code: Select all

SELECT u.sus_login_name,u.sus_name, ROUND((UNIX_TIMESTAMP()-s.sss_time)/60,0) as mins_since_active FROM zzzzsys_user u, zzzzsys_session s WHERE INSTR(s.sss_access, u.zzzzsys_user_id)

Neil

[kev1n]

You are welcome.

I *think* you can find out how long ago a user was last active as well. Try this:

Code: Select all

SELECT u.sus_login_name,u.sus_name, ROUND((UNIX_TIMESTAMP()-s.sss_time)/60,0) as mins_since_active FROM zzzzsys_user u, zzzzsys_session s WHERE INSTR(s.sss_access, u.zzzzsys_user_id)

Neil

... and to view distinct user names:

Code: Select all

SELECT
   sus_login_name, sus_name, MIN(mins_since_active) as mins_since_active
FROM (
   SELECT u.sus_login_name,u.sus_name, ROUND((UNIX_TIMESTAMP()-s.sss_time)/60,0) as mins_since_active FROM zzzzsys_user u, zzzzsys_session s WHERE INSTR(s.sss_access, u.zzzzsys_user_id) 
) T   
GROUP BY  sus_login_name, sus_name
ORDER BY `mins_since_active` ASC

[Janusz]

Thanks again for your support and additional code options. What I did on top of that I extended the "zzzzsys_session" table with one more column which I called: sss_login_time with
type: timestamp, Default: CURRENT_TIMESTAMP (added with phpMyAdmin)
so everytime new session is started the time stamp is generated by mySQL database

so as a results I have in the table following columns:
zzzzsys_session_id, sss_access, sss_time, sss_login_time

and with following code:

Code: Select all

select u.sus_login_name AS login, u.sus_name AS user, s.sss_login_time AS login_time from CTbaza.zzzzsys_user u join CTbaza.zzzzsys_session s where locate(u.zzzzsys_user_id, s.sss_access)

I have something like this in the view table:

login.....User ..... Login Time
john.....John.......2019-01-04 15:29:01
eric......Eric........2019-01-04 15:14:11

[ARWEN]

Don't forget to add this column when you update nuBuilder because it will be removed during the update process.