Archive for the ‘Audit Trails’ Category

Audit Trails – Thing to Keep in Mind

Hi All

As a follow-up to the article posted by Mohammed Daoud on Audit Trails in Dynamics GP, I just thought of posting this article which gives us an insight into a few things to keep in mind when configuring and using Audit Trails in Dynamics GP.

Requires “sa” Credentials

Audit Trails can be configured ONLY using the “sa” credentials. Even if you have granted all SQL admin rights in SQL Server, to your GP user ID, you cannot use it to install Audit Trails. You need the “sa” user ID.

Auditing Options

We can configure conditional tracking of data from the Auditing Options window. In this window, we can define what are the various fields that will be audited and what are the conditions on those fields when data is audited. The window looks as the one illustrated below.

image image

Depends on whatever audit types we have selected, the application creates the necessary triggers (INSERT, UPDATE, DELETE) into the audited table.

Also depending on the filters that we have specified the triggers have that conditions when the audits need to be captured.  In the example illustrated below the trigger code would look something like

                    FROM    inserted
                    WHERE   ( UPPER([ACTNUMBR_1]) = '000' ) ) 

However, note that we have marked the Alias field not to be audited. This does not have an impact on how the triggers work. The selection of fields is just for the purpose of what are the columns that are monitored during the update process. So in the example illustrated above, even though we have not monitored the Account Alias field in Audits, when you update the Alias of a specific account and save the record in GP, the application still audits the update process (if it matches the filter condition) and in the Smartview it shows the comparison results as shown below, for the update process.


Note that even though an update to the account master has been tracked, the comparison tool indicates nothing was changed, because we had changed the Alias value, which was not one of the audited fields.  This could be misleading at times, especially if we have certain fields which are not tracked.

Audit Trails Security

Note that we need to grant security to the various tables we are auditing so that users can view them on the Smartview. If this is not done correctly, the users will not be able to see the audited tables in Smartview.

This is done by clicking on the Access button on the Audit Trail Maintenance window, as shown below. So for each table we need to assign it to either a specific security task (or) a security role. Once this has been done, any user with the specific security task (or) security role assigned to them will be able to see the audited table on the smartview.

image    image

Audit Trails Smart View

The smartview is similar to the smartlist in GP, but it is still a crude version of the smartlist . It is not as robust and dynamic as the standard smartlist in GP.

For ex. when we try to enter a search condition for a smartview object, and close the smartview window, we need to immediately click on the Refresh button to see the filtered results. Assuming we have to add another condition and we open the Search window to add the new condition, we will note that even the existing search condition is not available in the window. This is unlike the standard smartlist, which holds the search conditions as long as we are in the same smartlist that we added the filter on.

Similarly, we need to click on the Favorites and add them to the favorites immediately after adding the search conditions else we lose the conditions.

Imports and Exports

There are functionality to export and import the audit trail setup. However, this is not as robust as the export and import functionality which is present in Extender (or) Smartlist Builder.

The export functionality only exports the details of the tables that are audited, however, does not export the details of the various fields audited/not audited in the table and also does not export any specific conditions specified in the auditing options window. In the above scenario, I have attached the contents of the export file below.

<?xml version="1.0"?>
 <Row ProductID="0" DB="TWO" SQLName="GL00100" DisplayName="Account Master" TechTableName="GL_Account_MSTR" DBView="1">

Note that the export file is not complete. So when this file is being imported into another environment, we still need to redefine all the auditing options for the table again. This could be a bit cumbersome during the audit process, since the major task of defining the audit options need to be recreated manually.

Hopefully the above tips were helpful to the community…

Until next post…

November 14, 2010 · veeyeskay · One Comment
Tags: , , , ,  · Posted in: Audit Trails, Dynamics, Great Plains Total Views: 2,956