Featured Post

Dynamics GP 2019 is now Released

It is now released, Microsoft Dynamics GP 2019 which moves the system to the modern product life cycle   " Beginning with the October...

Friday, September 12, 2014

User Activity Report - Idle and Login Details SQL Script

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:

User Activity Report

 

 

 

 

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

12 comments:

  1. 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.

    Any thoughts on how to get it working correctly with R2???

    Thanks.
    Rick Seavey

    ReplyDelete
    Replies
    1. Hello Rick
      I would need to revise and get back to you.

      Best Regards,
      Mahmoud M. AlSaadi

      Delete
  2. Why don't you rewrite the script to NOT include idle time??
    That should only take 5-10 minutes. :) :)
    Don't procrastinate. :) :)
    Thank you, Tom

    ReplyDelete
    Replies
    1. That's a valid point Tom ;)
      Although, 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,

      Delete
  3. 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.

    ReplyDelete
    Replies
    1. So far, there is no alternative solution. I will update this post once a valid solution is ready

      Best Regards,

      Delete
  4. 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?

    ReplyDelete
  5. Mahmoud - 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. :(

    ReplyDelete
    Replies
    1. Hello Angelo
      What is your Dynamics GP version ?

      Best Regards
      Mahmoud M. AlSaadi

      Delete
    2. Hello Mahmoud - we currently use GP 2015 R2.

      Delete
    3. Hello Angelo
      Unfortunately, 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

      Delete
    4. Hello Mahmoud, maybe you have a script update, I am using GP2016 and 2018

      Delete