Saturday, February 20, 2010

Script to Get Users Logged in to GP with no activities!?

Great script to get idle users that have GP open with no activities worth testing!!

Thanks to Frank for this script who in return did thank Ron Wilson and Sivakumar Venkataraman for this great tip!

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

Regards,
--
Mohammad R. Daoud - CTO
MVP, MCP, MCT, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85 
mohdaoud@gmail.com
mohdaoud.blogspot.com

3 comments:

seo optimization said...

God! that was mind blowing for me. i never thought that plenty of people are into computer and technology ang programs and scriipts.

Karpagaoorthy said...

hi mohammad,
The SQL_Last_Batch value(in master..sysprocesses table) is automatically update after 75 seconds without any activities from GP.So,how can i track the idle users from GP. Please give some advise..

Anonymous said...

Dear Mohamed ,
AlSalam alykom

Thanks for it , but I have question
I understood that it gives the idle time from the last batch posted? I mean if the user is making any other action like inquiry , report or cards will he considered active or idle ?

Related Posts:

Related Posts with Thumbnails