Tuesday, April 14, 2009

Retrieving Dynamics GP user idle time

One of the questions I regularly get from DBAs and Dynamics GP systems administrators alike is, "how do I know how long a GP user has been idle in the system without the use of third party products?". It is sufficient to say that they are good third party products out there that rely on Dexterity scripting to provide this information and perform all sort of actions that the system administrator may want to do, take for example Rockton Software's Omni Tools with it's inactivity timeout feature.

SQL Server offers a great way of tracking idle times from a process perspective. This is very cool, because most of the time we want to know that there is absolutely nothing running on the user's session before we decide to take some action -- most of the time remotely.

The following script displays the time (in seconds) the last batch was executed for a particular Dynamics GP user. It looks at the DEX_SESSION table and cross-reference it with the SPIDs retrieved via the undocumented SQL Server system stored procedure sp_who2.

UserActivity.sql

-- Create by Mariano Gomez, MVP
-- This script conferes no rights and has no warranties express or implied

USE MASTER
GO

IF OBJECT_ID('tempdb.dbo.#GP_UserActivity') IS NOT NULL
DROP TABLE #GP_UserActivity
GO

CREATE TABLE #GP_UserActivity
( SPID int
, [Status] varchar( 50)
, [Login] varchar( 80)
, HostName varchar( 80)
, BlkBy varchar( 10)
, dbanme varchar( 80)
, Command varchar( 500)
, CpuTime int
, DiskIO int
, LastBatch varchar( 22)
, ProgramName varchar( 200)
, SPID2 int
, RequestID int
)

INSERT #GP_UserActivity EXEC sp_who2

SELECT
SPID
, [Login]
, datediff(ss, cast(substring(LastBatch, 1, 5) + '/' + cast( datepart( year, getdate()) as char( 4)) +
' ' + substring( lastbatch, 7, 20) as datetime), getdate() ) 'seconds'
FROM #GP_UserActivity INNER JOIN tempdb.dbo.DEX_SESSION on spid = sqlsvr_spid



The script can be automated to track idleness information periodically and obtain detailed reports of system usage among GP users -- a sort of process activity. I use it a lot to plan server expansions activities and monitoring for my clients in conjunction with other important metrics. I hope you find this script useful in your daily administrative activities.

[Edit] One thing I truly like about blogging is that there is no short of solutions to a problem. My friend Robert Cavill, Systems Analyst in Western Australia offers his own version of determining Dynamics GP users' activity (or lack thereof). Robert adds "I have to point out to you that sometimes I have noticed that on my system the DEX_SESSION table infrequently will not have a row present in it for an active user". His comment is well on as the above script will exclude such users. Why and how a logged on user does not register a session in the DEX_SESSION table is something that's up for research, but the bottom line is it happens, especially on Citrix and Terminal Server environments. Take a look at Robert's elegant script:


-- Created by Robert Cavill
-- This script conferes no rights and has no warranties express or implied

SELECT
CASE
WHEN S.session_id is null
THEN 'Missing DEX_SESSION'
WHEN A.USERID <> P.loginame or P.loginame is null
THEN 'Phantom'
ELSE ''
END MismatchOnUserID,

CASE
WHEN datediff ( mi, P.last_batch, getdate() ) > 90
THEN 'Idle ' + str ( datediff ( mi, P.last_batch, getdate() ) )
ELSE ''
END AS Working
, A.USERID
, A.CMPNYNAM
, INTERID
, LOGINDAT + LOGINTIM LoginDatestamp
, SQLSESID
, P.login_time
, P.last_batch
, datediff ( mi, P.last_batch, getdate() ) SinceLastAction
, S.session_id
, S.sqlsvr_spid
, P.spid
, P.status
, P.net_address
, P.dbid
, P.hostname
, P.loginame
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

If you have a script that you use for this purpose or a variation, please don't hesitate to send it in or attach as a comment. I will be more than glad to publish your solution.

Related Articles

sp_who - Transact-SQL Server Reference. Click here

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Article updates:
04/15/2009 - Added script by Robert Cavill and commentary to his solution.

5 comments:

Anonymous said...

Do you think it is wise to end all sessions that do not have a SPID in the sysproccesses table?

Do you know what kills the SPID? GP or SQLServer?

Anonymous said...

I am killing all GP sessions that do not have a record in the sysproccess table. Do you thing that is a good idea?

When a session goes idle does GP shut down the SPID or does GP shut down the SPID?

Mohammed Fayyazuddin said...

Thanks for this incredible script.... After reviewing it i just added one feature more i.e excluding those users who have open some transactions screen and working on it and due to some work left from there desk.

SELECT
A.USERID,
u.USERNAME ,
A.CMPNYNAM COMPANY_NAME,
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 S.session_id IS NULL THEN 'Missing DEX_SESSION' ELSE '' END MISSING_SESSION,
CASE WHEN DATEDIFF(mi, P.last_batch, GETDATE()) > 1 THEN DATEDIFF(mi, P.last_batch, GETDATE()) ELSE 0 END AS IDLE_TIME,
DATEDIFF(mi, P.last_batch, GETDATE()) IDLE_TIME_In_MINUTES,
INTERID COMPANY_ID,
LOGINDAT + LOGINTIM LOGIN_DATE_TIME,
SQLSESID SQL_SESSIONID,
P.login_time SQL_LOGINTIME,
P.last_batch SQL_LAST_BATCH,
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
where a.USERID not in (select distinct USERID from dynamics..sy00800 where CMPNYNAM=c.CMPNYNAM)


Anonymous said...

This only applies if the user created a batch but not with the user who happens to open any window and half created the document/entries. The old scripts allows us to see all the users who are in fact idle and notify them.

Tom said...

How would it be possible to have SQL Server email a list of the users and their time on the server?? Such a report would be very useful. I'm not a SQL expert or programmer...

Thank you, Tom