Implementing the Audit Trail in 3 Tier ASP.NET Application

Introduction

In this article we are going to discuss about implementing the Audit Trail in 3 tier asp.net application. Also this can used for single tier application also.

3-Tier architecture

3-Tier architecture generally contains UI or Presentation Layer, Business Access Layer (BAL) or Business Logic Layer and Data Access Layer (DAL).

Presentation Layer (UI)

Presentation layer contains pages like .aspx or windows form where data is presented to the user or input is taken from the user.

Business Access Layer (BAL) or Business Logic Layer

BAL contains business logic, validations or calculations related with the data, if needed. I will call it Business Access Layer in my demo.

Data Access Layer (DAL)

DAL contains methods that helps business layer to connect the data and perform required action, might be returning data or manipulating data (insert, update, delete etc). For this demo application, I have taken a very simple example. I am assuming that I have to play with record of persons (FirstName, LastName, Age) and I will refer only these data through out this article.

Below are steps and procedure to implement the Audit Trail in our application.

I have seen few Audit Trails examples in web, they explained only about the screen name, Action, IP address and Time Stamp.

Here I have used What data changed, when and changes by Whom with IP address and TimeStamp

Step 1

Create table for Audit Trail (I have mentioned my table below) in SQL database

Table: SampleAuditTrail

• Id

• UserName - Who logged in

• ProcessName - What is the process they did

• Page – Page Name

• Operation - What operation like (Insert, Delete, Update)

• Description

• IP Address

Step 2

Create Interface for AuditTrail (In DAL) and declare below function

Function Insert_AuditTrail(ByVal Data As tblAuditTrail) As IResult(Of String)

Step 3

Create Class for declared Interface(In DAL) and Implement the Declared function in the Class (Code Given Below)

Public Function Insert_AuditTrail(ByVal Data As tblAuditTrail) As IResult(Of String) Implements IDAOAuditTrailValues.Insert_AuditTrail

Dim Db As New //Your DBContext
Dim Res As IResult(Of String) = New QueryResult(Of String)
Try
Db.tblAuditTrails.InsertOnSubmit(Data)
Db.SubmitChanges()
Res.Output = (From var In Db.tblAuditTrails Where var.ProcessTime = Data.ProcessTime And var.UserName = Data.UserName And
var.Operation = Data.Operation).ToList(0).ID
Res.Result = DALResult.Success
Res.Message = Msg.CreateMessage(MessageKeys.MSG_SAVE, New String() {"Audit Trail"})
Catch ex As Exception
Dim dex As New DALException(Msg.CreateMessage(MessageKeys.MSG_SAVE, New String() {"AuditTrail", "Saved"}), ex)
Res.Result = DALResult.Error
Res.Message = dex.Message
Res.Exception = dex
Dim ep As New ExceptionPublisher()
Finally
Db.Dispose()
End Try
Return Res
End Function

Step 4

Create the Separate Function in DAL for getting Audit Trail values (Below are code)

Public Function GetAuditTrailInsertandUpdate(ByVal data As SampleAuditTrail, ByVal Operation As String) As IResult(Of String) Implements IDaoReviewer.GetAuditTrailInsertandUpdate

Dim db As New CodePointDataContext
Dim Res1 As IResult(Of String) = New QueryResult(Of String)
Try

Dim strHostName = System.Net.Dns.GetHostName

Dim ta As New tblAuditTrail
ta.UserName = HttpContext.Current.Session.Item(2).ToString
ta.Operation = "Update"
ta.Page = "Page Name"
ta.IPAddress = HttpContext.Current.Request.ServerVariables("REMOTE_ADDR")
ta.ProcessTime = DateTime.Now
For Each a In GetType(tblname).GetProperties()
If a.Name.ToLower <> "id" Then
If a.PropertyType.IsValueType = True Or a.PropertyType.Equals(GetType(String)) Then
If a.GetValue(Data, Nothing) = Nothing Then
ta.Description += a.Name + ":" + " " & vbCrLf
Else
ta.Description += a.Name + ":" + a.GetValue(data, Nothing).ToString & vbCrLf
End If
End If
End If
Next

Dim res1 = (New daoAuditTrailValues).Insert_AuditTrail(ta)

Catch ex As Exception
End Try
Return Res1
End Function

Step 5

Now we need to call this function in every DAL function which is (Insert, Delete, and Update Methods)

Dim operation = "Insert"

Dim res1 = GetAuditTrailInsertandUpdate(p, operation)

here p is refers which data came from function

Like this we can call for Update and Delete Function

Step 6

Output we will receive like this,

Sno	Username	Time	Form Name	Action	Description	IPAddress
31	John	03/13/2013	User Creation	Insert	Name: Arun UserID: 102 Address:Chennai Gender:Male Remarks: Nil	192.168.0.88

Conclusion

This will be the simplest method to implement with all application. Any Suggestion or feedback is highly welcome.