It’s a common issue to have users logged into Dynamics GP without closing the system for several hours, and sometimes for days. The user activity inquiry doesn’t provide much in-depth details regarding the Idle time and login date and timing. In this post, an SQL script is provided to cover this concern as illustrated below:
Tables Included:
- DYNAMICS..ACTIVITY
- DYNAMICS..SY01400 | USER Master
- DYNAMICS..SY01500 | Company Master
- Tempdb..DEX_SESSION
- master..sysprocesses
- master..sysdatabases
/*---------------------------------------------------------------------
Creation Date: 6th of September, 2014
Created by: Mahmoud M. AlSaadi
The main purpose of the script is to provide a user activity report,
along with the Idle and login details
The script has been tested on a very limited sample data.
Please feel free to contact me for any further enhancement
Revision History:
Revision No. RevisionDate Description
1 06/09/2014 Original Version
2 15/09/2014 Modified Version
SELECT RTRIM(A.USERID) AS UserID ,
RTRIM(B.USERNAME) AS UserName ,
RTRIM(C.INTERID) AS CompanyDatabase ,
RTRIM(C.CMPNYNAM) AS CompanyName ,
LOGINDAT AS LoginDate ,
CONVERT(VARCHAR(1000), DATEPART(HH, LOGINTIM)) + ':'
+ CONVERT(VARCHAR(1000), DATEPART(MI, LOGINTIM)) AS LoginTime ,
ISNULL(CONVERT(VARCHAR(1000),E.last_batch),'') AS SQL_LastBatch ,
CASE WHEN D.session_id IS NULL
THEN 'Corrupted Missing DEX_SESSION'
ELSE CONVERT(VARCHAR(1000),session_id)
END DEX_SESSION ,
CASE WHEN CONVERT(VARCHAR(1000),E.SPID) IS NULL
THEN 'Corrupted SQL_SESSION'
ELSE CONVERT(VARCHAR(1000),SPID)
END SQL_SESSION ,
CASE WHEN DATEDIFF(mi, E.last_batch, GETDATE()) > 1
THEN DATEDIFF(hh, E.last_batch, GETDATE())
ELSE 0
END AS 'IdleTime - InHours' ,
CASE WHEN DATEDIFF(MI, LOGINDAT + LOGINTIM, GETDATE()) > 1
THEN DATEDIFF(HH, LOGINDAT + LOGINTIM, GETDATE())
ELSE 0
END AS 'Logged in for – InHours'
FROM DYNAMICS..ACTIVITY A
LEFT JOIN DYNAMICS..SY01400 B ON A.USERID = B.USERID
LEFT JOIN DYNAMICS..SY01500 C ON A.CMPNYNAM = C.CMPNYNAM
LEFT JOIN tempdb..DEX_SESSION D ON A.SQLSESID = D.session_id
LEFT JOIN master..sysprocesses E ON D.sqlsvr_spid = E.spid
AND ecid = 0
LEFT JOIN master..sysdatabases F ON E.dbid = F.dbid
Best Regards,
Mahmoud M. AlSaadi
Great script that we've used in the past. But we just upgraded to R2 of GP2013 and it no longer calculates idle time correctly. THe last_batch is getting constantly updated on our system when users are actually idle. Perhaps a background process is running that makes it appear that the user is active.
ReplyDeleteAny thoughts on how to get it working correctly with R2???
Thanks.
Rick Seavey
Hello Rick
DeleteI would need to revise and get back to you.
Best Regards,
Mahmoud M. AlSaadi
Why don't you rewrite the script to NOT include idle time??
ReplyDeleteThat should only take 5-10 minutes. :) :)
Don't procrastinate. :) :)
Thank you, Tom
That's a valid point Tom ;)
DeleteAlthough, I am trying to find out another alternative rather than omitting the idle time, it actually represents an essential piece of information for this specific report.
Updates will be provided accordingly.
Thanks a lot Tom,
Hello Mahmood. Were you able to update this to use with GP 2013 R2. I understand that 2015 is out but have not yet upgraded and will not until mid 2016.
ReplyDeleteSo far, there is no alternative solution. I will update this post once a valid solution is ready
DeleteBest Regards,
If a user created a new batch at 10:00am and have started entering transfer entries. He keeps creating multiple transfer entries to different sites in the same batch. Lets say he finish his work in the same batch at 11:30am, he stopped doing transfer entries in the batch, did not posted the batch and have started some Internet surfing. You have run this script at 11:50am, what will this script show about the idle time of this user?
ReplyDeleteMahmoud - when a user in this report has a blank SQL Last Batch and Idle Time, what does that mean? Sorry, our previous guy set us up this SSRS report. But he left. :(
ReplyDeleteHello Angelo
DeleteWhat is your Dynamics GP version ?
Best Regards
Mahmoud M. AlSaadi
Hello Mahmoud - we currently use GP 2015 R2.
DeleteHello Angelo
DeleteUnfortunately, after GP 2013 R2, a new process was added which primarily checks for pending messages on each client, this means, that the idle details for users using the desktop client will always be 0, making this piece of information quite misleading.
Thank to Tim Wappat for providing this insight.
I will make sure to update this post once an alternative solution is found.
Never hesitate to share any further inquiries,
Best Regards,
Mahmoud M. AlSaadi
Hello Mahmoud, maybe you have a script update, I am using GP2016 and 2018
Delete