A Better Way To Track User Activity In Dynamics GP

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.

ActivityTrackingLocation

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.

ActivityTrackingSetupWin

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.

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.

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.

The results should look something like this:

track_activity_codeResults

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.

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.