How to identify active users in Microsoft Dynamics GP

Identifying active user in Microsoft Dynamics GP is a subject that comes up all the time.  If a Dynamics GP SQL server reboot is needed, an email is sent that requires all users to get out of Dynamics GP but some users don’t log off.  So, are they actively working in Dynamics GP?  Or did they just leave GP open and go home for the day?

Here’s a query that shows you all Dynamics GP users in the Activity table, and how long they have been inactive:

SELECT SY01500.INTERID, SY01500.CMPNYNAM, Activity.USERID, Usr.USERNAME AS UserName,

DATEDIFF(minute, sysproc.login_time, GETDATE()) as LoggedInMinutes,

DATEDIFF(minute, sysproc.last_batch, GETDATE()) as InactiveMinutes ,

sysProc.spid

FROM   DYNAMICS..ACTIVITY Activity (NOLOCK)

INNER JOIN DYNAMICS..SY01400 Usr (NOLOCK) ON Activity.USERID = Usr.USERID

INNER JOIN DYNAMICS..SY01500 SY01500 ON Activity.CMPNYNAM = SY01500.CMPNYNAM

LEFT OUTER JOIN tempdb..DEX_SESSION DexSession ON Activity.SQLSESID = DexSession.session_id

LEFT OUTER JOIN master..sysprocesses sysproc ON DexSession.sqlsvr_spid = sysproc.spid AND Activity.USERID = sysproc.loginame

ORDER BY CASE WHEN sysproc.last_batch IS NULL THEN 9999999 ELSE DATEDIFF(minute, sysproc.last_batch, GETDATE()) END , Activity.USERID

If you like this tip, then you may also enjoy a subscription to our quarterly publication of Dynamics Community News.  Each edition includes technology announcements and special promotions on Microsoft Dynamics.

By: Ron Draganowski – Minnesota Microsoft Dynamics GP partner

Ron Draganowski