Add audit table to .net core 2 app

2018-01-31
 |  No Comments

I recently added an audit log to a .NET Core 2 app, this is how I did it. There’s lots of info on audit logs on the internet, this one used some ideas of Gordon Beeming from this post.

All I want to capture in the audit is –

  • who did it,
  • what time,
  • the Entity Name and Id,
  • the EntityState (Modified/Added/Deleted),
  • and a json string of the saved entity.

AuditLog Table

The script below creates the audit table in an existing database.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
 
IF EXISTS (SELECT * FROM SYS.tables WHERE object_id = OBJECT_ID('dbo.AuditLog') )
DROP TABLE dbo.AuditLog
GO

CREATE TABLE dbo.AuditLog (
	Id bigint identity NOT NULL,
	[EntityName] varchar(200) NOT NULL,
	[EntityId] int NOT NULL,
	[Login] varchar(200) NOT NULL,
	[Time] datetime NOT NULL,
	[ModType] varchar(15) NOT NULL,
	RowAfter varchar(max),
	CONSTRAINT [PK_AuditLog] PRIMARY KEY CLUSTERED 
	(Id)
)

GO

CREATE NONCLUSTERED INDEX [IX1_AuditLog] ON [dbo].[AuditLog]
(
	[EntityName] ASC,
	[EntityId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

CREATE NONCLUSTERED INDEX [IX2_AuditLog] ON [dbo].[AuditLog]
(
	[EntityName] ASC,
	[Time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

CREATE NONCLUSTERED INDEX [IX3_AuditLog] ON [dbo].[AuditLog]
(
	[Time] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO

Lines 19 and onwards create some basic indexes which should keep this table responding as snappy as possible.  These indexes will help when :

  • Listing all changes for a specific row or entity.
  • Listing recent changes for a table.
  • Listing all recent changes.

AuditLog Model

Add the following class to your models. (Or generate it using Scaffold-DbContext)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
 
public class AuditLog
{
	public long Id { get; set; }
	[Required]
	[StringLength(200)]
	public string EntityName { get; set; }
	public int EntityId { get; set; }
	[Required]
	[StringLength(200)]
	public string Login { get; set; }
	[Column(TypeName = "datetime")]
	public DateTime Time { get; set; }
	[Required]
	[StringLength(15)]
	public string ModType { get; set; }
	public string RowAfter { get; set; }
}

DbContext Changes

I created this in a partial class just so that when regenerating the DbContext file using Scaffold-DbContext it didn’t blow away this custom code. In a new file called MyDatabaseContext_partial.cs (replace with your context name) put the following code.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
 
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;

namespace MyNamespace.Models
{
    public partial class MyDatabaseContext
    {
        private readonly UserResolverService _userResolverService;

        public MyDatabaseContext(DbContextOptions<MyDatabaseContext> options, UserResolverService userResolverService) : base(options)
        {
            _userResolverService = userResolverService;
        }

        public override int SaveChanges()
        {
            return SaveChangesAsyncAudit(true, CancellationToken.None).Result;
        }

        public override int SaveChanges(bool acceptAllChangesOnSuccess)
        {
            return SaveChangesAsyncAudit(acceptAllChangesOnSuccess, CancellationToken.None).Result;
        }

        public override Task<int> SaveChangesAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            return SaveChangesAsyncAudit(true, cancellationToken);
        }

        public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
        {
            return SaveChangesAsyncAudit(acceptAllChangesOnSuccess, cancellationToken);
        }

        private async Task<int> SaveChangesAsyncAudit(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
        {
            //NOTE: ref https://beeming.net/data/2017/1/easily-adding-auditing-to-a-entity-framework-code-first-project

            if (!acceptAllChangesOnSuccess) //Ignore audit logging
                return await base.SaveChangesAsync(false, cancellationToken).ConfigureAwait(false);

            //Get the changes
            var entries = this.ChangeTracker.Entries().Where(e => e.State == EntityState.Modified || e.State == EntityState.Added || e.State == EntityState.Deleted).ToList();
            var audits = new List<EntityEntryHolder>();
            //store them in holding area until after save
            foreach (var item in entries)
            {
                audits.Add(new EntityEntryHolder()
                {
                    ModType = item.State,
                    EntityEntry = item
                });
            }

            //Save changes
            int result = await base.SaveChangesAsync(true, cancellationToken).ConfigureAwait(false);

            int id;
            string user = _userResolverService.GetUser();
            foreach (var item in audits)
            {
                id = GetIdentity(item.EntityEntry);
                var mType = item.ModType.ToString();
                var tName = item.EntityEntry.Entity.GetType().Name;
                var r = JsonConvert.SerializeObject(item.EntityEntry.Entity, new JsonSerializerSettings { Formatting = Formatting.None, ReferenceLoopHandling = ReferenceLoopHandling.Ignore });
                this.AuditLog.Add(new AuditLog
                {
                    Login = user,
                    ModType = mType,
                    EntityName = tName,
                    EntityId = id,
                    Time = DateTime.Now,
                    RowAfter = r
                });
            }

            int logResult = await base.SaveChangesAsync(true, cancellationToken).ConfigureAwait(false);
            
            return result;
        }

        private int GetIdentity(EntityEntry entry)
        {
            int id = 0;

            Type t = entry.Entity.GetType();

            var propInfo = t.GetProperties().FirstOrDefault(o => o.CustomAttributes.FirstOrDefault(oo => oo.AttributeType == typeof(System.ComponentModel.DataAnnotations.KeyAttribute)) != null);

            if (propInfo == null) //Fall back to Id Name
                propInfo = t.GetProperty("Id");

            if (propInfo != null)
                id = (int)propInfo.GetValue(entry.Entity);

            return id;
        }
    }

    internal class EntityEntryHolder
    {
        public EntityState ModType { get; set; }
        public Microsoft.EntityFrameworkCore.ChangeTracking.EntityEntry EntityEntry { get; set; }
    }
}

Replace with your namespace and DbContext name as appropriate.

UserService

In order to inject the web user into the DbContext create another file called UserService.cs with the following code.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
 
using Microsoft.AspNetCore.Http;

namespace MyNamespace
{
    public class UserResolverService: IUserResolverService
    {
        private readonly IHttpContextAccessor _httpContextAccessor;

        public UserResolverService(IHttpContextAccessor httpContextAccessor)
        {
            _httpContextAccessor = httpContextAccessor;
        }

        public string GetUser()
        {
            string user = _httpContextAccessor.HttpContext.User?.Identity?.Name;


            return user ?? "system";
        }
    }

    interface IUserResolverService
    {
        string GetUser();
    }
}

It’s a bit of a shame that using this method means that the DbContext now contains state, which means when registering it in Startup you will not be able to take advantage of Database Connection Pooling.  Perhaps creating new save methods and pass the current web user in from the Controllers is the way to get around that, if you want to take advantage of the faster connection pooling method.

Startup.cs

In the ConfigureServices method of Startup, register the DbContext.

//register db Context
services.AddDbContext<Models.MyDatabaseContext>(options => options.UseSqlServer(Configuration.GetConnectionString("MyConnString")));

Next Steps

Create a read only web api controller and use the data in your views. Or, create specific methods to get the last logs for certain tables. Like this example which gets the last 10 changes for Tag and Alarm and returns a view model.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
 
/// <summary>
/// Get the recent changes for Tag and Alarm entities.
/// </summary>
/// <returns>The last 10 audit logs for Tags and Alarms</returns>
[HttpGet("GetRecentChanges")]
public IActionResult GetRecentChanges()
{
	var retList3 = _context.AuditLog
		.Where(l => l.EntityName == "Tag" || l.EntityName == "Alarm")
		.Select(l => new
		{
			Id = l.Id,
			EntityName = l.EntityName,
			EntityId = l.EntityId,
			ModType = l.ModType,
			Login = l.Login,
			Time = l.Time,
			EntityTitle = l.EntityName == "Tag" ? Newtonsoft.Json.Linq.JObject.Parse(l.RowAfter).Value<string>("TagName") : Newtonsoft.Json.Linq.JObject.Parse(l.RowAfter).Value<string>("TagAlarmField")
		})
		.GroupBy(l1 => new { EntityName = l1.EntityName, EntityId = l1.EntityId })
		.Select(g => new Models.RecentUpdatesViewModel
		{
			EntityName = g.OrderByDescending(g1 => g1.Id).First().EntityName,
			EntityId = g.OrderByDescending(g1 => g1.Id).First().EntityId,
			EntityTitle = g.OrderByDescending(g1 => g1.Id).First().EntityTitle,
			Login = g.OrderByDescending(g1 => g1.Id).First().Login,
			ModType = g.OrderByDescending(g1 => g1.Id).First().ModType,
			Time = g.OrderByDescending(g1 => g1.Id).First().Time
		})
		.OrderByDescending(l2 => l2.Time)
		.Take(10)
		.ToList();

	return Json(retList3);
}
Tags: ,