Tracking user activity in Dynamics GP is a vital role of any System Administrator. Knowing what your users are doing and what they have done can be the key to solving an array of issues ranging from illegal activity to correcting a simple mistake. Fortunately Dynamics GP offers a tool for allowing you to configure and view user activity on the system. Unfortunately, the tool for displaying the activity can be sluggish and lead to potential performance issues if not run within a very narrow dataset. The application server also suffers as a result of running the activity monitor without narrowing down the parameters and can cause GP to freeze up or, in the worst case, crash altogether.

As a result, I have created a script to handle querying the tables that store user activity within GP.

Before you can begin viewing user activity you must first configure it from within GP. To access the Activity Tracking Setup window click on Dynamics GP — Tools — Setup — System — Activity Tracking as shown below.

You will need access to your GP system password in order to access the Activity Tracking Setup window.

Once the window is displayed it is time to configure the activity that you want to track. There are 5 Activity Types that can be configured (Login/Logout Tracking, Access Tracking, File Tracking, Process Tracking and Posting Tracking). I recommend selecting nearly all the options within each activity type. However, a few notable items can be excluded such as successful logins/logout, successful attempts to open modifier/report writer as well as activity tracking on modules that you do not use within GP. Also, depending on your level of system access you may not want to track everything. If you have access to event logs on the SQL Server you can choose to rely on them as a source of monitoring certain user activity. It’s really your choice.

Once you have configured all the options click OK. You are now capturing user activity within GP. Take note of the date that you initially configured the Activity Tracker as it will be the earliest available date that you can use when querying the data.

Activity in GP is stored in the SY05000 table. However, to get a better picture of who is performing the action and what is occurring we also want to include the SY01400 table as well as a custom table that will need to be created so that we can better organize the data being returned by the script.

The steps outlined below will guide you through the process of creating the necessary table to reference the activity type from the INQYTYPE column in the SY05000 table.

The first step is to create the table. Use the following script to create the track_activity_code table on your DYNAMICS database.

Create track_activity_code Table USE [DYNAMICS] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON GO CREATE TABLE track_activity_code( code INT NULL, description VARCHAR(50) NULL ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 USE [ DYNAMICS ] GO SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON SET ANSI_PADDING ON GO CREATE TABLE track_activity_code ( code INT NULL , description VARCHAR ( 50 ) NULL ) ON [ PRIMARY ] GO SET ANSI_PADDING OFF GO

Now that the table has been created we need to populate it with the appropriate codes and descriptions. In order to fully understand what the individual codes represent I had to do a quick search on the Microsoft Dynamics GP customer source site. It’s a good idea to check the list before publishing as it is possible that new codes could be added in subsequent versions of GP.

The following script can be used to add all the available codes to your new track_activity_code table.

Add Codes to track_activity_code table USE [DYNAMICS] GO INSERT INTO track_activity_code VALUES(1, 'user failed login'); INSERT INTO track_activity_code VALUES(2, 'user logging in'); INSERT INTO track_activity_code VALUES(3, 'user accessed form'); INSERT INTO track_activity_code VALUES(4, 'user denied login'); INSERT INTO track_activity_code VALUES(5, 'user accessed report'); INSERT INTO track_activity_code VALUES(6, 'user denied report'); INSERT INTO track_activity_code VALUES(7, 'user added master record'); INSERT INTO track_activity_code VALUES(8, 'user modified master record'); INSERT INTO track_activity_code VALUES(9, 'user deleted master record'); INSERT INTO track_activity_code VALUES(10, 'user used process server'); INSERT INTO track_activity_code VALUES(11, 'user added utility record'); INSERT INTO track_activity_code VALUES(12, 'user accessed file maintenance'); INSERT INTO track_activity_code VALUES(13, 'user denied file'); INSERT INTO track_activity_code VALUES(14, 'user accessed routines'); INSERT INTO track_activity_code VALUES(15, 'user logged out'); INSERT INTO track_activity_code VALUES(16, 'user went into modifier'); INSERT INTO track_activity_code VALUES(17, 'user went into report writer'); INSERT INTO track_activity_code VALUES(18, 'user added transaction record'); INSERT INTO track_activity_code VALUES(19, 'user deleted transaction record'); INSERT INTO track_activity_code VALUES(20, 'user modified transaction record'); INSERT INTO track_activity_code VALUES(21, 'user added setup record'); INSERT INTO track_activity_code VALUES(22, 'user deleted setup record'); INSERT INTO track_activity_code VALUES(23, 'user modified setup record'); 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 USE [ DYNAMICS ] GO INSERT INTO track_activity_code VALUES ( 1 , 'user failed login' ) ; INSERT INTO track_activity_code VALUES ( 2 , 'user logging in' ) ; INSERT INTO track_activity_code VALUES ( 3 , 'user accessed form' ) ; INSERT INTO track_activity_code VALUES ( 4 , 'user denied login' ) ; INSERT INTO track_activity_code VALUES ( 5 , 'user accessed report' ) ; INSERT INTO track_activity_code VALUES ( 6 , 'user denied report' ) ; INSERT INTO track_activity_code VALUES ( 7 , 'user added master record' ) ; INSERT INTO track_activity_code VALUES ( 8 , 'user modified master record' ) ; INSERT INTO track_activity_code VALUES ( 9 , 'user deleted master record' ) ; INSERT INTO track_activity_code VALUES ( 10 , 'user used process server' ) ; INSERT INTO track_activity_code VALUES ( 11 , 'user added utility record' ) ; INSERT INTO track_activity_code VALUES ( 12 , 'user accessed file maintenance' ) ; INSERT INTO track_activity_code VALUES ( 13 , 'user denied file' ) ; INSERT INTO track_activity_code VALUES ( 14 , 'user accessed routines' ) ; INSERT INTO track_activity_code VALUES ( 15 , 'user logged out' ) ; INSERT INTO track_activity_code VALUES ( 16 , 'user went into modifier' ) ; INSERT INTO track_activity_code VALUES ( 17 , 'user went into report writer' ) ; INSERT INTO track_activity_code VALUES ( 18 , 'user added transaction record' ) ; INSERT INTO track_activity_code VALUES ( 19 , 'user deleted transaction record' ) ; INSERT INTO track_activity_code VALUES ( 20 , 'user modified transaction record' ) ; INSERT INTO track_activity_code VALUES ( 21 , 'user added setup record' ) ; INSERT INTO track_activity_code VALUES ( 22 , 'user deleted setup record' ) ; INSERT INTO track_activity_code VALUES ( 23 , 'user modified setup record' ) ;

Once the above script has been executed run the following SELECT to view the data in the table and verify that all records were created.

Query track_activity_code SELECT * FROM dbo.track_activity_code ORDER BY code 1 2 SELECT * FROM dbo . track_activity_code ORDER BY code

The results should look something like this:

Now that our custom table has been created and populated with the necessary data it is time to begin tracking activity within Dynamics GP. The script below will accomplish this task by pulling activity records from the SY05000 table and joining them against the SY01400 and track_activity_code tables to retrieve the users’s name as well as a summary description of the activity which can be used to better narrow down your results.

Query Activity Tracking In Dynamics GP USE [DYNAMICS] DECLARE @userid VARCHAR(20), @startdate DATETIME, @enddate DATETIME, @action VARCHAR(100) SET @userid = NULL SET @startdate = '1/1/2016' SET @enddate = GETDATE() SET @action = NULL IF @UserID IS NULL AND @Action IS NULL BEGIN SELECT s.USERID AS 'User ID', m.USERNAME AS 'User Name', m.USRCLASS AS 'User Class', s.DATE1 AS 'Date', s.TIME1 AS 'Time', c.description AS 'Simple Description', s.SECDESC AS 'Detailed Description', s.CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s.inqytype = c.code INNER JOIN SY01400 m ON m.userid = s.userid WHERE s.date1 BETWEEN @startdate AND @enddate ORDER BY s.DATE1 END ELSE IF @UserID IS NOT NULL AND @Action IS NULL BEGIN SELECT s.USERID AS 'User ID', m.USERNAME AS 'User Name', m.USRCLASS AS 'User Class', s.DATE1 AS 'Date', s.TIME1 AS 'Time', c.description AS 'Simple Description', s.SECDESC AS 'Detailed Description', s.CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s.inqytype = c.code INNER JOIN SY01400 m ON m.userid = s.userid WHERE s.date1 BETWEEN @startdate AND @enddate AND s.userid = @userid ORDER BY s.DATE1 END ELSE IF @UserID IS NOT NULL and @Action IS NOT NULL BEGIN SELECT s.USERID AS 'User ID', m.USERNAME AS 'User Name', m.USRCLASS AS 'User Class', s.DATE1 AS 'Date', s.TIME1 AS 'Time', c.description AS 'Simple Description', s.SECDESC AS 'Detailed Description', s.CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s.inqytype = c.code INNER JOIN SY01400 m ON m.userid = s.userid WHERE s.date1 BETWEEN @startdate AND @enddate AND s.userid = @userid AND c.description = @action ORDER BY s.DATE1 END ELSE IF @UserID IS NULL and @Action IS NOT NULL BEGIN SELECT s.USERID AS 'User ID', m.USERNAME AS 'User Name', m.USRCLASS AS 'User Class', s.DATE1 AS 'Date', s.TIME1 AS 'Time', c.description AS 'Simple Description', s.SECDESC AS 'Detailed Description', s.CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s.inqytype = c.code INNER JOIN SY01400 m ON m.userid = s.userid WHERE s.date1 BETWEEN @startdate AND @enddate AND c.description = @action ORDER BY s.DATE1 END 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 USE [ DYNAMICS ] DECLARE @ userid VARCHAR ( 20 ) , @ startdate DATETIME , @ enddate DATETIME , @ action VARCHAR ( 100 ) SET @ userid = NULL SET @ startdate = '1/1/2016' SET @ enddate = GETDATE ( ) SET @ action = NULL IF @ UserID IS NULL AND @ Action IS NULL BEGIN SELECT s . USERID AS 'User ID' , m . USERNAME AS 'User Name' , m . USRCLASS AS 'User Class' , s . DATE1 AS 'Date' , s . TIME1 AS 'Time' , c . description AS 'Simple Description' , s . SECDESC AS 'Detailed Description' , s . CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s . inqytype = c . code INNER JOIN SY01400 m ON m . userid = s . userid WHERE s . date1 BETWEEN @ startdate AND @ enddate ORDER BY s . DATE1 END ELSE IF @ UserID IS NOT NULL AND @ Action IS NULL BEGIN SELECT s . USERID AS 'User ID' , m . USERNAME AS 'User Name' , m . USRCLASS AS 'User Class' , s . DATE1 AS 'Date' , s . TIME1 AS 'Time' , c . description AS 'Simple Description' , s . SECDESC AS 'Detailed Description' , s . CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s . inqytype = c . code INNER JOIN SY01400 m ON m . userid = s . userid WHERE s . date1 BETWEEN @ startdate AND @ enddate AND s . userid = @ userid ORDER BY s . DATE1 END ELSE IF @ UserID IS NOT NULL and @ Action IS NOT NULL BEGIN SELECT s . USERID AS 'User ID' , m . USERNAME AS 'User Name' , m . USRCLASS AS 'User Class' , s . DATE1 AS 'Date' , s . TIME1 AS 'Time' , c . description AS 'Simple Description' , s . SECDESC AS 'Detailed Description' , s . CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s . inqytype = c . code INNER JOIN SY01400 m ON m . userid = s . userid WHERE s . date1 BETWEEN @ startdate AND @ enddate AND s . userid = @ userid AND c . description = @ action ORDER BY s . DATE1 END ELSE IF @ UserID IS NULL and @ Action IS NOT NULL BEGIN SELECT s . USERID AS 'User ID' , m . USERNAME AS 'User Name' , m . USRCLASS AS 'User Class' , s . DATE1 AS 'Date' , s . TIME1 AS 'Time' , c . description AS 'Simple Description' , s . SECDESC AS 'Detailed Description' , s . CMPNYNAM AS 'Company Name' FROM sy05000 s INNER JOIN track_activity_code c on s . inqytype = c . code INNER JOIN SY01400 m ON m . userid = s . userid WHERE s . date1 BETWEEN @ startdate AND @ enddate AND c . description = @ action ORDER BY s . DATE1 END

The script is designed in such a way that the @startdate and @enddate parameters are always required. Optionally, you can specify a value for @userid and/or @action or leave them as NULL . This allows you to hone in on a specific activity depending on how much you know when starting your investigation.

By default the @enddate is set to the current date. Remember, the @startdate can only go back as far as the point at which you began tracking activity within Dynamics GP. You can hard code this value if you prefer but it would probably be a better practice to simply note it as a comment within the script.

The possibilities for further configuring this are endless. You could put it into a stored procedure to allow you to call it more quickly or publish it through SQL Server Reporting Services. One technique that I have also used is to create an additional log table and then set up triggers on the SY05000 table to populate the log with key activity, specifically modifications to master files. It’s up to you how you want to track activity within your GP environment but hopefully this will give you some valuable insight as to how activity tracking works.