Skip to content

Tips and Tricks #3 – Dynamics GP User Idle Time

March 12, 2010

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

From → Dynamics GP

3 Comments
  1. wow really helpfull
    thanks a lot for this script

  2. Ron permalink

    Thanks for the feedback. I do want to give credit where credit is due once more however. Sivakumar Venkataraman is the one responsible for the script.

Trackbacks & Pingbacks

  1. New Category – SQL Alerts « Real Life Dynamics User

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 27 other followers

%d bloggers like this: