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 https://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.
Victoria Yudin 
Is it possible to get information from GP 2010 (SQL 2008 R2) that how many transactions a specific user has been made on a specific date, including posting, unposted and backdate entries. SQL Query / View or anything which can get me this required information. E.g. How many transactions ABC user has been made in today’s date. (must contain all unposted transactions, any backdate transactions, all posted transactions). Please guide victoria.
LikeLike
ADi,
Unfortunately, this is not possible without putting in place some additional custom code ahead of time. This would need to be done separately and specifically for each type of transaction you need to track.
-Victoria
LikeLike
Victoria,
We just experienced a power outage and a payment was incompletely posted against the invoice. The payment does not show but the amount remaining is now zero. Unable to void, message says you must unapply this document. The batch recovery finds nothing to fix. Do you have a handy sql statement that will help me identify where the tables are out of balance, or a method for forcing completion of the transaction?
LikeLike
Hi Steven,
While I have fixed similar issues many times, they are often quite different in what’s needed for a fix, so I don’t have any generic scripts that can help with this site unseen. And I would not feel comfortable simply giving out SQL code to ‘fix’ things without actually seeing the data. I would recommend that you work with your GP partner or Microsoft support, so that they can look at your data and help you come up with the appropriate fix for this after looking at your data. Hope that makes sense.
-Victoria
LikeLike
Thank you Victoria,
I didn’t think there woul be a fix script for such a situation, just a direction to look, as these systems are used in so many different models depending on mutlitple facors.. I will take a look at the RM tables for previous transactions to identify the hung payment and pass this along to our partner. The payment did set the invoice document amount remaining to zero? I would have believed that transactions were more atomic and would roll back or forward when the system became responsive again.
Steven
LikeLike
Hi Steven,
Gotcha. GP does usually roll this back if the transaction did not complete, but that depends on how far along it got. One thing you can try before going any further is the Reconcile utility:
– GP | Tools | Utilities | Sales | Reconcile
– Select Outstanding Document Amounts under Reconcile
– Select the Customer ID for the From and To under Range
– Click Process
Depending on how far along the posting process got, this may fix the invoice by returning the current amount back to what it was before the payment was entered.
-Victoria
LikeLike
Very cool!
LikeLike
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
LikeLike
Fabian,
Take a look at this post by Michael Johnson: http://mbsguru.blogspot.com/2007/12/who-has-that-record-locked.html
-Victoria
LikeLike
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.
LikeLike
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
LikeLike
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!
LikeLike
David,
Thanks for posting this.
-Victoria
LikeLike
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
LikeLike
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
LikeLike
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
LikeLike
That seems to work very well. Thank you.
LikeLike