Data Auditing Tool (Audit Trigger Generator)

• AuditToolSample.Zip
• AuditTool.Zip

Introduction

In today’s IT word most of the applications need data auditing, whether it is an ERP system or Work Flow based Application or CRM tool or any database system, auditing has become essential part of that system. Basically Auditing will help on below areas.

1. Application admin can use Audit data for analysis.
2. Forensic analysis on database tampering.
3. Audit data also will help quick data recovery to some extent.

There are multiple approaches in implementing data audit, but having Audit logic in table’s trigger is the most efficient approach and also its very easy for maintenance.

I have created the tool (Stored Procedure) that will generate audit triggers for your tables and load Audit data in a single table for your entire application. You can also customize the Stored Procedure based on your application need.

I have used this tool in couple of my projects and saved around 80% of coding effort. I am sure that you can implement Audit Trial in your entire project in couple of days using this tool.

And also I’ve uploaded sample table and test data that will help you understanding Audit Trial.

Tool Feature

1. This Audit Tool will track entire application audit data in a single table, easy for generating audit reports. Refer below snapshot “Audit Log Table with Sample Data”.
Audit Log Table with Sample Data





2. Audit Tool also will track below information in Audit table, this information will help generating user friendly report.

Table Name
Column Name
RecordIdentifierName(Primary Column name)
RecordIdentifierValue(Primary Column Data)
Actionby_ UserID
ActionDateTime (Time stamp)
Action Type (Insert / Update /Delete)
Audit Description
Old Value (Data will be loaded for Update and Delete action)
Old Value Decode (Data from foreign key reference table)
New Value (Data will be loaded for Insert and Update action)
New Value Decode (Data from foreign key reference table)
Category

3. Any table changes will not impact Application Audit Trial, Audit triggers are capable of reading columns dynamically and load data it in Audit table.
4. For update action you can load only modified data in Audit Table.
5. Will track table’s primary column name and data as record identifier, this can be overridden by configuring specific column as record Identifier in Audit configuration table. For example, “Sales Person Name” can be configured in Audit configuration table as a record identifier, In that case Trigger will use “Sales Person Name” as a record identifier instead of “Sales Person ID”.



6. Trigger can go and pull the data from foreign key reference tables and track it. Let’s take below example, while updating “SalesPerson” table it can go and pull “Territory Name” from “SalesTerritory” table by using “Territory ID” that will give complete information for data auditing.

Audit Tables needed for Data Auditing

Below Audit tables need to be created and configured for Audit implementation. Let’s understand significance of each table.

01. AuditCategory

This table will help categorizing Audit data, for example Audit data can be classified like below.

1. Master Data
2. Sales
3. Purchase

02. AuditSubCategory

Application table name should be configured with category mapping.

Specifying table’s primary key or Specific Column information in RecordIdentifier column will help Audit trigger to load both RecordIdentifier column name and data in audit table. This will help locating the record and match with Actual table data.

The purpose of AuditCategory and AuditSubCategory table is creating audit report with proper grouping.

03. AuditLogDecodeTableMapping

This table is really important one. This table should be loaded with all foreign key references so that Audit trigger can pull necessary information from foreign key table and load it in Audit table (refer feature 6).

04. AuditActionType

This table is to maintain action details (Insert, Update, and Delete.)

05. AuditLogData

AuditLogData table where all audit information will be maintained.

Implementation steps

1. Download the attachment (AuditTool.Zip) and create below mentioned audit tables.
SQL

[Audit].[AuditCategory]
[Audit].[AuditSubCategory]
[Audit].[AuditLogDecodeTableMapping]
[Audit].[AuditActionType]
[Audit].[AuditLogData]

2. Create the function
SQL

Audit.GenerateDynamicQuery

3. Create Audit Stored Procedures for creating Audit Triggers
SQL

Audit.Generate_AuditTrigger_For_Insert
Audit.Generate_AuditTrigger_For_Update
Audit.Generate_AuditTrigger_For_Delete

4. Generate Audit triggers and deploy it.

Implementation training with sample data

1. Download the Sample attachment (AuditToolSample.Zip) and create below sample tables.
SQL

[Sales].[CountryRegion]
[Sales].[SalesTerritory]
[Sales].[SalesPerson]

2. Configure sample table information in below Audit Tables.
SQL

[Audit].[AuditCategory]
[Audit].[AuditSubCategory]
[Audit].[AuditLogDecodeTableMapping]
[Audit].[AuditActionType]

3. Generate Audit Trigger Script and execute it.
SQL

/* Generate the Audit trigger using below SQL script and execute it*/
EXEC [Audit].[Generate_AuditTrigger_For_Insert] 
  'SalesPerson|SalesPersonInsertAudit,SalesTerritory|SalesTerritoryInsertAudit,
   CountryRegion|CountryRegionInsertAudit', 'Jash', 'Sales', 'True'
go
EXEC [Audit].[Generate_AuditTrigger_For_Update] 
  'SalesPerson|SalesPersonUpdateAudit,SalesTerritory|
  SalesTerritoryUpdateAudit,CountryRegion|CountryRegionUpdateAudit', 'Jash', 'Sales', 'True'
go
EXEC [Audit].[Generate_AuditTrigger_For_Delete] 
  'SalesPerson|SalesPersonDeleteAudit,SalesTerritory|
  SalesTerritoryDeleteAudit,CountryRegion|CountryRegionDeleteAudit', 'Jash', 'Sales', 'True'

4. Try with test data.