Page 1 of 1

List of all logged in users

PostPosted: Fri Jan 04, 2019 9:22 pm
by Janusz
Is it possible to get the list of all users which are currently logged in?

Re: List of all logged in users

PostPosted: Fri Jan 04, 2019 10:46 pm
by 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

Re: List of all logged in users

PostPosted: Fri Jan 04, 2019 11:03 pm
by Janusz
Thanks a lot. The second option is working in my case.

Re: List of all logged in users

PostPosted: Fri Jan 04, 2019 11:32 pm
by 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

Re: List of all logged in users

PostPosted: Sat Jan 05, 2019 1:35 am
by kev1n
nac wrote: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

Re: List of all logged in users

PostPosted: Sat Jan 05, 2019 5:47 am
by 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

Re: List of all logged in users

PostPosted: Sun Jan 06, 2019 11:59 pm
by ARWEN
Don't forget to add this column when you update nuBuilder because it will be removed during the update process.