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
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 **/
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.