baby and horse

SQL view for user activity in Dynamics GP


There are many different pieces of code that I have seen for this over the years, but more often than not what our users ask me for is a way to quickly see who is logged into GP, what company, and when did they log in. While this can easily be seen on the User Activity window (GP | Tools | Utilities | System | User Activity), many users do not have access to this window and sometimes this need arises when someone is either not able to get into GP or does not want to take the time to do so. 

Below is a view to show all the users logged in and a count of the batches, resources and tables each user has locked or open. Please note that this code will not show details of what the users have locked or open, just the counts. The idea behind this is that if a user has something locked, you may not want to simply delete them out of the ACTIVITY table, because those resources, tables or batches will still be locked by the user. This code can also be helpful to monitor users who are not logging out of the system at night or have multiple companies open.

~~~~~

CREATE VIEW view_User_Activity
AS

/*
view_User_Activity
created Sep 12, 2011 by Victoria Yudin
for updates please see http://victoriayudin.com/gp-reports/

*/

SELECT
a.USERID GP_User_ID,
um.USERNAME [User_Name],
a.CMPNYNAM Company_Name,
a.LOGINDAT+a.LOGINTIM Login_Date_and_Time,
coalesce(b.batch_count,0) Batch_Activity_Records,
coalesce(r.resource_count,0) Resource_Activity_Records,
coalesce(t.table_locks,0) Table_Lock_Records

FROM DYNAMICS..ACTIVITY a

LEFT OUTER JOIN
(SELECT USERID, count(*) batch_count
 FROM DYNAMICS..SY00800
 GROUP BY USERID) b -- batch activity
ON a.USERID = b.USERID

LEFT OUTER JOIN
(SELECT USERID, count(*) resource_count
 FROM DYNAMICS..SY00801
 GROUP BY USERID) r -- resource activity
ON a.USERID = r.USERID

LEFT OUTER JOIN
(SELECT Session_ID, COUNT(*) table_locks
 FROM tempdb..DEX_LOCK
 GROUP BY Session_ID) t -- table locks
ON a.SQLSESID = t.Session_ID

LEFT OUTER JOIN
DYNAMICS..SY01400 um -- user master
ON a.USERID = um.USERID


/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/

GO
GRANT SELECT ON view_User_Activity TO DYNGRP

~~~~~

Disclaimer: I tested this on limited data, if you find an issue or have a suggestion for improvement, please let me know and I will post the update here for everyone.

12 Responses to “SQL view for user activity in Dynamics GP”

  1. Hi Victoria,

    Thanks so much for to do public this site and help to advanced users of Dynamics GP as I. My question:

    How to know what user have an specific order or document opened?. for example where another user open any window with the invoice No 001, in the window “Sales transaction entry” but an financial manager needs to post the same invoice contained in a batch named “invoices-xxx”. in that scenario we have 10 users that process invoices. He needs to call to user that have opened the document.

    Thanks for your helpful tips

    Like

  2. Has any one seen Microsoft Dynamics GP 10 freeze when switching between companies?
    We have all of our users approximately 50 at anyone time using four Citrix App Servers (as our Dynamics GP clients / workstations). very randomly (at times) a user goes to switch companies (GP Desktop using the taskbar and clicking the company to launch the Company Switch Form). And GP freezes (it appears). Just a empty Dynamics GP Window frame. No error messages and timing out.

    What happens is the user closes the Windows and the Citrix Session ends. They wait a minute or two and back they are in GP and might never experience this again. We might get five or ten of these a month. Not sure if this is happening at the GP API level, SQL or Citrix or some combination.

    Please if you have heard of or have ideas about this your sharing might be helpful.

    Like

  3. I made a slight improvement on this, using a function I cribbed from here

    http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-convert-a-datediff-to-days-hours-and-minutes.html

    I then changed your view, like this…
    CREATE VIEW view_User_Activity
    AS
    SELECT
    a.USERID GP_User_ID,
    um.USERNAME [User_Name],
    a.CMPNYNAM Company_Name,
    a.LOGINDAT+a.LOGINTIM Login_Date_and_Time,
    dbo.datediffToWords(a.LOGINDAT+a.LOGINTIM, GETDATE ( )) Duration,
    coalesce(b.batch_count,0) Batch_Activity_Records,
    coalesce(r.resource_count,0) Resource_Activity_Records,
    coalesce(t.table_locks,0) Table_Lock_Records

    FROM DYNAMICS..ACTIVITY a

    LEFT OUTER JOIN
    (SELECT USERID, count(*) batch_count
    FROM DYNAMICS..SY00800
    GROUP BY USERID) b — batch activity
    ON a.USERID = b.USERID

    LEFT OUTER JOIN
    (SELECT USERID, count(*) resource_count
    FROM DYNAMICS..SY00801
    GROUP BY USERID) r — resource activity
    ON a.USERID = r.USERID

    LEFT OUTER JOIN
    (SELECT Session_ID, COUNT(*) table_locks
    FROM tempdb..DEX_LOCK
    GROUP BY Session_ID) t — table locks
    ON a.SQLSESID = t.Session_ID

    LEFT OUTER JOIN
    DYNAMICS..SY01400 um — user master
    ON a.USERID = um.USERID

    Voila, duration in days, hours, and minutes!

    Like

    • David,

      Thanks for posting this.

      -Victoria

      Like

    • How does one clear out these locks. As i am attempting to upgrade GP 9.0 to service pack 4. However once it goes into the utilities aspect of the upgrae. it complains about other users currently using microsoft dynamics gp.. im not from a strong sql background.. whats the comand to clear out all people currently locked in on the below tables:
      DYNAMICS..ACTIVITY
      DYNAMICS..SY00800
      DYNAMICS..SY00801

      Like

    • And one more refinement: add a line to the main query to display the type of user: Full or Limited


      a.CMPNYNAM Company_Name,
      CASE um.UserType
      WHEN 1 THEN ‘Full’
      ELSE ‘Limited’
      END AS UserType,
      a.LOGINDAT+a.LOGINTIM Login_Date_and_Time,

      Regards,

      Steve Erbach
      Appleton, WI

      Like

  4. That seems to work very well. Thank you.

    Like

Trackbacks/Pingbacks

  1. SQL view for user activity in Dynamics GP | Victoria Yudin - DynamicAccounting.net - GP Technical Blogs - Microsoft Dynamics Community - September 14, 2011

    [...] Comments 0 Victoria brings us a new SQL view for user activity in Dynamics GP. [...]

    Like

  2. SQL view for user activity in Dynamics GP | Interesting Findings & Knowledge Sharing - September 12, 2011

    [...] Reading: SQL view for user activity in Dynamics GP VN:F [1.9.10_1130]Please wait while your rating is cast…Rating: 0.0/10 (0 votes cast)VN:F [...]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,492 other followers

%d bloggers like this: