Is there any way to implement Audit Trails for user actions in .Net MVC

Question

I'm new to .Net MVC. I've designed a web application using Ado.Net CRUD operation without entity framework. I've searched on the internet on how to implement audit trails on .Net MVC application, but I'm getting results on the implementation of Audit trails on EF application. Can someone provide any resource reference or example? Thank you guys in advance!

Answer 1

If you are using MS SQL you can look at Change Data Capture or you can track changes using triggers.

This will have to be done on SQL server level though.

Track Data Changes (SQL Server)

I would recommend Change Data Capture. You can access the changes made to tables and also include historic data changes

SQL Server 2019 (15.x) provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database. Change data capture and change tracking can be enabled on the same database; no special considerations are required. For the editions of SQL Server that support change data capture and change tracking, see Features Supported by the Editions of SQL Server 2016. Change tracking is supported by SQL Database. Change data capture is only supported in SQL Server and Azure SQL Managed Instance.

Change Data Capture Change data capture provides historical change information for a user table by capturing both the fact that DML changes were made and the actual data that was changed. Changes are captured by using an asynchronous process that reads the transaction log and has a low impact on the system.

or

Have a look at using triggers.

Understanding Change Tracking in SQL Server using Triggers

As you might be aware, a trigger in a database is a simple program that is executed when an event occurs. We are going to leverage this concept and execute a trigger whenever a record in a table is either inserted, updated, or deleted. If you have a very big database, you can control which columns or tables to install the triggers on. This gives greater control over the other inbuilt techniques for implementing change tracking in SQL Server.

Answer 2

I need to capture the user visited page and action performed on that page

You can implement an HttpModule which logs all the visited pages within your .NET MVC application.

With an HttpModule you have access to all the Request/Response properties, including Headers, Cookies, FormData parameters.

namespace MyApplication
{
    public class DatabaseAuditHttpModule : IHttpModule
    {
        private class Properties
        {
            public string Url { get; set; }
            public string HttpMethod { get; set; }
            public int StatusCode { get; set; }
            
            // add other important HTTP properties
        }
        
        public void Init(HttpApplication context)
        {
            context.BeginRequest += BeginRequest;
            context.EndRequest += EndRequest;
        }

        private void BeginRequest(object sender, EventArgs e)
        {
            HttpContext ctx = HttpContext.Current;
            var request = ctx.Request;

            var requestHeaders = request.Unvalidated.Headers;
            var requestFormData = request.Unvalidated.Form;

            var properties = new Properties
            {
                Url = request.Url.ToString(),
                HttpMethod = request.HttpMethod
            };

            ctx.Items["X-Properties"] = properties;
        }


        private void EndRequest(object sender, EventArgs e)
        {
            HttpContext ctx = HttpContext.Current;

            // get the properties for the current HTTP request
            Properties properties = (Properties)HttpContext.Current.Items["X-Properties"];

            properties.StatusCode = ctx.Response.StatusCode;
            
            // TODO:
            // log these values in the database
        }

        public void Dispose()
        {

        }
    }
}

Registering the HttpModule in Global.asax:

namespace MyApp.Mvc
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            
        }
 
        // register DatabaseAuditHttpModule
        public static DatabaseAuditHttpModule AuditHttpModule = new DatabaseAuditHttpModule();
 
        public override void Init()
        {
            base.Init();
 
            AuditHttpModule.Init(this);
        }
    }
}

Logging stored procedures execution example:

public class DatabaseService
{
    public static async Task ExecuteStoredProcedureAsync(string connectionString, string storedProcedureName, ILogger logger)
    {
        logger.LogTrace($"'{storedProcedureName}' Stored Procedure executing");
        long totalDuration = 0;

        Stopwatch sw = new Stopwatch();
        sw.Start();

        using(var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            
            sw.Stop();
            totalDuration += sw.ElapsedMilliseconds;

            logger.LogTrace($"connection.Open() Duration:{sw.ElapsedMilliseconds}");

            sw.Restart();
            
            using (var command = SqlCommand(storedProcedureName, connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                using (var reader = await command.ExecuteReaderAsync())
                {
                    sw.Stop();
                    totalDuration += sw.ElapsedMilliseconds;

                    logger.LogTrace($"'{storedProcedureName}' Stored Procedure ExecuteReader Duration:{sw.ElapsedMilliseconds}");

                    sw.Restart();

                    do
                    {
                        while (await reader.ReadAsync())
                        {
                            // read the data
                        }

                    } while (await reader.NextResultAsync());
                }

                sw.Stop();
                totalDuration += sw.ElapsedMilliseconds;

                logger.LogTrace($"'{storedProcedureName}' Stored Procedure executed. Duration:{totalDuration}");

                return result;
            }
        }
    }
}