September 12, 2011 
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.
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
Fabian,
Take a look at this post by Michael Johnson: http://mbsguru.blogspot.com/2007/12/who-has-that-record-locked.html
-Victoria
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.
Hi Ron,
I personally have not seen this, but it looks like you have started a thread on the same topic on the GP community forum, hopefully you will get some help on there.
-Victoria
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!
David,
Thanks for posting this.
-Victoria
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
Ashon,
If no one is in GP, you can clear those tables by running the following script:
DELETE FROM DYNAMICS..ACTIVITY
DELETE FROM DYNAMICS..SY00800
DELETE FROM DYNAMICS..SY00801
-Victoria
That seems to work very well. Thank you.