Web Apps – Current Users

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 ;
Feel free to share...

Leave a Reply

Close Menu