List of all logged in users

Questions related to using nuBuilder Forte.

List of all logged in users

Postby Janusz » Fri Jan 04, 2019 9:22 pm

Is it possible to get the list of all users which are currently logged in?
Janusz
 
Posts: 179
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: List of all logged in users

Postby nac » Fri Jan 04, 2019 10:46 pm

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
nac
 
Posts: 46
Joined: Wed Dec 13, 2017 7:58 am
Location: Aberdeen, UK

Re: List of all logged in users

Postby Janusz » Fri Jan 04, 2019 11:03 pm

Thanks a lot. The second option is working in my case.
Janusz
 
Posts: 179
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: List of all logged in users

Postby nac » Fri Jan 04, 2019 11:32 pm

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
nac
 
Posts: 46
Joined: Wed Dec 13, 2017 7:58 am
Location: Aberdeen, UK

Re: List of all logged in users

Postby kev1n » Sat Jan 05, 2019 1:35 am

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
kev1n
 
Posts: 191
Joined: Mon Oct 15, 2018 2:13 am

Re: List of all logged in users

Postby Janusz » Sat Jan 05, 2019 5:47 am

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
Janusz
 
Posts: 179
Joined: Fri Dec 28, 2018 10:11 pm
Location: Krakow, Poland

Re: List of all logged in users

Postby ARWEN » Sun Jan 06, 2019 11:59 pm

Don't forget to add this column when you update nuBuilder because it will be removed during the update process.
ARWEN
 
Posts: 48
Joined: Thu Nov 01, 2018 2:31 pm


Return to General



cron