Audit Requirements for Dynamics GP

The need to keep a log for every single modification on the system usually never comes without considerable cost either from a technical or business perspective. Although, the need for such requirement remains crucial. In this post, I am shedding a light specifically on the need to keep track of every single modification of a record in a simple and yet cost effective methodology.

Technical Perspective

There are several methods to accomplish such requirement; very great and well know third party products can just deliver the need, techniques such as triggers, timestamp columns, join queries … etc could be a time-consuming which sometimes result in an undesirable performance. Meanwhile, there is a great feature out there in SQL management which can give you all these requirements with a low-cost, it is called the “Change Data Capture - CDC”

SQL Server | Change Data Capture – CDC

Change data capture provides information about the DML (Data Manipulation Language) changes on a table or database, it is very useful to know what are the inserted or deleted records, what are the values of the updated records (before and after the update).

The important of CDC will be explained through a simple example of updating a vendor card, which got few fields updated from a specific value to another value. CDC will provide detailed information of the data before and after update as shown below

Configuring Change Data Catalog for Dynamics GP

Once configured, CDC builds new system tables, stored procedures, SQL jobs and functions. Initially, you can run the scripts below to check whether CDC is enabled either on the database level or table level.

To check whether the CDC is enabled on the database level

-- The script below checks whether CDC is enabled for each database
USE master
SELECT  is_cdc_enabled ,
database_id,
name,
state_desc,
create_date,
user_access_desc,
is_read_only,
snapshot_isolation_state_desc,
recovery_model_desc
FROM    sys.databases     

To check whether the CDC is enabled on the table level

-- The script below checks whether CDC is enabled for each table
USE TWO
SELECT object_id,type_desc,name,is_tracked_by_cdc
FROM sys.tables

 

 

 

 

In the screen shot above, it is obvious that CDC is enabled on the TWO db, and specifically on the IV00101 table which is the Item Master. In order to enable CDC on the DB and Table, run the following scripts:

To enable the CDC on the database level:

-- The script below enables CDC on the database level
USE TWO
EXEC sys.sp_cdc_enable_db

To enable the CDC on the table level:

-- The script below enables CDC on the table level

USE TWO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'IV00101',
@role_name     = NULL

Once CDC is enabled, the following objects are created under TWO db > Tables > System Tables

Important Tables:

cdc.change_tables | Storing the tables being tracked
cdc.captured_columns | Storing the column being tracked per table

 

Real Case Scenario

After enabling the CDC on the TWO db, and the item master table specifically IV00101, I am going to apply the following scenario in order to check the result:

1. Adding a new item card
2. Changing few fields of the item such as; item class, UOM schedule, item description …etc
3. Delete the item

Here is the tracking log as retrieved from [cdc].[dbo_IV00101_CT]  on the following path  (TWO db > Tables > System Tables), the table shows the different changes applied on the item card since it was inserted, updated then deleted.

 

Here is the script which is used to retrieve tracking information above, it should be different according to the table for which you enable CDC.

SELECT CASE __$OPERATION
         WHEN 1 THEN 'Deleted'
         WHEN 2 THEN 'Inserted'
         WHEN 3 THEN 'Before being Updated'
         WHEN 4 THEN 'After beign Updated'
         ELSE ''
       END AS Record_Status,
       *
       FROM [cdc].[dbo_IV00101_CT]

 

In brief, change data catalog feature is a simple and cost-effective method that can be applied to track highly sensitive data such as setup in general. In proceeding posts, I will include a real case example of tracking opening and closing fiscal periods on the financial module and provide alert accordingly.

Best Regards,
Mahmoud M. AlSaadi