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.