Ever wonder how many of your employees are currently in Web Apps? Or do you need to deploy a new version of Web Apps, but want to make sure no one is actively using it?
Most functions in Web Apps call
UIHelper.Ping(). What that does is run the stored procedure
WebServiceAccess_Update_Session which through sp_helptext we can see does this:
UPDATE WebServiceAccess SET LastActivity = CURRENT_TIMESTAMP WHERE ID = @parm1
So, we know that WebServiceAccess is the table that houses session information.
With that knowledge, we can run a query against your SYS database to find out who has active sessions, how many sessions each employee has open, and how many minutes they have been idle.
--run on your SYS database --replace 'APP_DB' with your application database select a.userrecid, REPLACE(e.emp_name, '~',', ') as name, a.id, a.lastactivity, DATEDIFF(MINUTE, a.LastActivity, CURRENT_TIMESTAMP) minutes_idle, COALESCE(b.other_sessions_open, '0') as other_sessions_open from webserviceaccess a (NOLOCK) left outer join APP_DB..pjemploy e (NOLOCK) on e.user_id = a.userrecid outer apply (select b.userrecid, count(b.userrecid) as other_sessions_open from webserviceaccess as b (NOLOCK) where b.userrecid = a.userrecid and b.id <> a.id group by userrecid ) b order by minutes_idle ---count of distinct current users select count(distinct userrecid) as activeusers from webserviceaccess ;