Retrieving Dynamics GP user idle time

 

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

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.

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/

Posted by Mariano Gomez at 8:00 AM

The Dynamics GP Blogster: Retrieving Dynamics GP user idle time