Tips and Tricks #3 – Dynamics GP User Idle Time

Recently, I posted a question on the Dynamics GP forums asking how I could programmatically find the idle time of all my users.  For a company that purchases as few GP licenses as possible, this is invaluable in helping to determine who may have forgotten to log out (thus taking up other users licenses).  I would like to thank Sivakumar Venkataraman for his solution that worked perfectly.

As posted on the Dynamics GP forums, the following SQL query will show you the idle time of all users logged into GP.  The IDLE_TIME field is what you are looking for.

https://community.dynamics.com/forums/p/30149/51131.aspx#51131

SELECT CASE WHEN S.session_id IS NULL THEN ‘Missing DEX_SESSION’

ELSE ”

END MISSING_SESSION,

CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1

THEN ‘Idle for ‘ + LTRIM(RTRIM(STR(DATEDIFF(mi, P.last_batch, GETDATE())))) + ‘ minutes.’

ELSE ”

END AS IDLE_TIME_DESC,

CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1

THEN DATEDIFF(mi, P.last_batch, GETDATE())

ELSE 0

END AS IDLE_TIME,

A.USERID,

A.CMPNYNAM COMPANY_NAME,

INTERID COMPANY_ID,

LOGINDAT + LOGINTIM LOGIN_DATE_TIME,

SQLSESID SQL_SESSIONID,

P.login_time SQL_LOGINTIME,

P.last_batch SQL_LAST_BATCH,

DATEDIFF(mi, P.last_batch, GETDATE()) TIME_SINCE_LAST_ACTION,

S.session_id SQLSERVER_SESSIONID,

S.sqlsvr_spid SQLSERVER_PROCESSID,

P.spid PROCESSID,

P.status PROCESS_STATUS,

P.net_address NET_ADDRESS,

P.dbid DATABASE_ID,

P.hostname HOSTNAME

FROM DYNAMICS..ACTIVITY A

LEFT JOIN DYNAMICS..SY01400 U ON A.USERID = U.USERID

LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM

LEFT JOIN tempdb..DEX_SESSION S ON A.SQLSESID = S.session_id

LEFT JOIN master..sysprocesses P ON S.sqlsvr_spid = P.spid

AND ecid = 0

LEFT JOIN master..sysdatabases D ON P.dbid = D.dbid

About these ads