Serilog 7 - Custom tables for Error, Audit and User Audit Logging using EF 7

[Bakeries]

I'm trying to configure Serilog to use 3 custom log tables that have being created using DDD in EF7 and MSSql. Reading through the documentation - in Table Definition section- I found out that it is possible to achieve this.
Unfortunately after many days of trying to make it work, I have achieved nothing.

My goal is to use the exiting log tables, that will be created by the migrations, to insert the filtered logs to the tables.
The Serilog configuration I used it the following:

var columnOptions = new ColumnOptions {
    Id = { ColumnName = "Id" , DataType = SqlDbType.BigInt, AllowNull = false }
};

//Removing auto generated columns.
columnOptions.Store.Remove(StandardColumn.Exception);
columnOptions.Store.Remove(StandardColumn.Level);
columnOptions.Store.Remove(StandardColumn.Properties);
columnOptions.Store.Remove(StandardColumn.Message);
columnOptions.Store.Remove(StandardColumn.LogEvent);
columnOptions.Store.Remove(StandardColumn.MessageTemplate);
columnOptions.Store.Remove(StandardColumn.TimeStamp);

#region Audit Log Column Options

var auditColumnOptions = columnOptions;
auditColumnOptions.AdditionalColumns = new Collection<SqlColumn>
{
    new() { ColumnName = "UserId", DataType = SqlDbType.VarChar, DataLength = 50, AllowNull = false },
    new() { ColumnName = "Endpoint", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "Request", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "Response", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "Operation", DataType = SqlDbType.VarChar, DataLength = 50, AllowNull = false },
    new() { ColumnName = "TableName", DataType = SqlDbType.VarChar, DataLength = 150, AllowNull = false },
    new() { ColumnName = "ColumnName", DataType = SqlDbType.VarChar, DataLength = 150, AllowNull = false },
    new() { ColumnName = "OldValue", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "NewValue", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "PrimaryKeyValue", DataType = SqlDbType.VarChar, AllowNull = false },
    new() { ColumnName = "DateChanged", DataType = SqlDbType.DateTime, AllowNull = false }
};

#endregion

#region User Audit Column Options

var userAuditColumnOptions = columnOptions;
userAuditColumnOptions.AdditionalColumns = new Collection<SqlColumn>
{
    new() { ColumnName = "UserId", DataType = SqlDbType.VarChar, DataLength = 50, AllowNull = false },
    new() { ColumnName = "SessionId", DataType = SqlDbType.VarChar, DataLength = 200, AllowNull = false },
    new() { ColumnName = "IpAddress", DataType = SqlDbType.VarChar, DataLength = 150, AllowNull = false },
    new() { ColumnName = "UrlAccessed", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "Data", DataType = SqlDbType.VarChar, DataLength = -1, AllowNull = false },
    new() { ColumnName = "TimeAccessed", DataType = SqlDbType.DateTime, AllowNull = false }
};

#endregion

Log.Logger = new LoggerConfiguration()
    .Enrich.FromLogContext()
    .MinimumLevel.Verbose()
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Level == LogEventLevel.Error)
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.ErrorLogTableName
            },
            appConfiguration: builder.Configuration,
            columnOptions: new ColumnOptions
            {
                AdditionalColumns = new Collection<SqlColumn>
                {
                    new() { ColumnName = "Source", DataType = SqlDbType.VarChar, AllowNull = true },
                    new() { ColumnName = "UserId", DataType = SqlDbType.VarChar, AllowNull = true }
                }
            }))
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Properties.ContainsKey("User Audit:"))
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.UserAuditLogTableName
            },
            appConfiguration: builder.Configuration,
            columnOptions: userAuditColumnOptions))
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Properties.ContainsKey("Audit Logging:"))
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.AuditLogTableName
            },
            appConfiguration: builder.Configuration,
            columnOptions: auditColumnOptions))
    .WriteTo.Console()
    .CreateLogger();

[jonorossi]

You've got 4 sinks, do any of them work? Does the console sink print log events? Do you get any errors? Is the SelfLog writing any errors to your console?

Try simplifying your code to determine where the problem is. For example, comment out your filters, just get one SQL Server sink working first.

[jonorossi]

Have you enabled the Serilog SelfLog? See the Serilog debugging page.

[Bakeries]

I will try it now. This needs to be above, or bellow the init of serilog? or just anywhere in the startup?

[jonorossi]

Best to do before initialising Serilog so you catch all errors.

[Bakeries]

i have tried everything to make it work. nothing helps. i dont even get an error when using selflog. i might have to let serilog create the tables manually and exclude entities from migrations in order to be able to retrieve the logs later.

[Bakeries]

@jonorossi, after many testing, i wasn't able to log data to the tables AuditLog and UserAuditLog, Error log was working without any problems. Bellow you will see a test code implementation that im using in order to find out why previous mentions tables were not registering any data.
Please note that im using:

• .NET 7
• Serilog.AspNetCore 7.0.0
• Serilog.Sinks.MSSqlServer 6.3.0

This is a test code (SelfLog is enabled 2 times, tested 1 before serilog conf, 1 after and last test was with before and after):

• This is how im logging an information for Audit Logging

Log.Information("{@Change}", new AuditLog
{
Endpoint = endpoint.ToString(),
Request = "{test}",
Response = "{test}",
UserId = "test user id",
TableName = "test",
ColumnName = "{entityName}.{prop.Name}",
OldValue = "originalValue",
NewValue = "currentValue",
DateChanged = DateTime.Now,
Operation = OperationTypes.Create.EnumToString(),
PrimaryKeyValue = "test pk"
});

• This is the AuditLog Class:

public class AuditLog
{
    public int Id { get; set; }
    public string UserId { get; set; }
    public string Endpoint { get; set; }
    public string Request { get; set; }
    public string Response { get; set; }
    public string Operation { get; set; }
    public string TableName { get; set; }
    public string ColumnName { get; set; }
    public string OldValue { get; set; }
    public string NewValue { get; set; }
    public string PrimaryKeyValue { get; set; }
    public DateTime DateChanged { get; set; }
}

• And this is the Serilog Configuration region, that is located in Program.cs

# region Serilog Configuration

#region Loggers Column Options

ColumnOptions columnOptions = new ColumnOptions { Id = { ColumnName = "Id", DataType = SqlDbType.BigInt, AllowNull = false } };

#region Error log column options

ColumnOptions errorLogColumnOptions = new ColumnOptions
{
    Store = new Collection<StandardColumn>
    {
        StandardColumn.Id,
        StandardColumn.Message,
        StandardColumn.Exception,
        StandardColumn.Level,
        StandardColumn.LogEvent,
        StandardColumn.TimeStamp
    },
    AdditionalColumns = new Collection<SqlColumn>
    {
        new() { ColumnName = "Source", DataType = SqlDbType.NVarChar, AllowNull = false },
        new() { ColumnName = "UserId", DataType = SqlDbType.NVarChar, AllowNull = false }
    }
};
errorLogColumnOptions.Store.Remove(StandardColumn.MessageTemplate);
errorLogColumnOptions.Store.Remove(StandardColumn.Properties);

#endregion

//Removing auto generated columns.
columnOptions.Store.Remove(StandardColumn.Exception);
columnOptions.Store.Remove(StandardColumn.Level);
columnOptions.Store.Remove(StandardColumn.Properties);
columnOptions.Store.Remove(StandardColumn.Message);
columnOptions.Store.Remove(StandardColumn.LogEvent);
columnOptions.Store.Remove(StandardColumn.MessageTemplate);
columnOptions.Store.Remove(StandardColumn.TimeStamp);

#region Audit Log Column Options

ColumnOptions auditColumnOptions = new ColumnOptions
{
    Store = columnOptions.Store,
    AdditionalColumns = new Collection<SqlColumn>
    {
        new() { ColumnName = "UserId", DataType = SqlDbType.NVarChar, DataLength = 50, AllowNull = false },
        new() { ColumnName = "Endpoint", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "Request", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "Response", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "Operation", DataType = SqlDbType.NVarChar, DataLength = 50, AllowNull = false },
        new() { ColumnName = "TableName", DataType = SqlDbType.NVarChar, DataLength = 150, AllowNull = false },
        new() { ColumnName = "ColumnName", DataType = SqlDbType.NVarChar, DataLength = 150, AllowNull = false },
        new() { ColumnName = "OldValue", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "NewValue", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "PrimaryKeyValue", DataType = SqlDbType.NVarChar, AllowNull = false },
        new() { ColumnName = "DateChanged", DataType = SqlDbType.DateTime, AllowNull = false }
    }
};

#endregion

#region User Audit Column Options

ColumnOptions userAuditColumnOptions = new ColumnOptions
{
    Store = columnOptions.Store,
    AdditionalColumns = new Collection<SqlColumn>
    {
        new() { ColumnName = "UserId", DataType = SqlDbType.NVarChar, DataLength = 50, AllowNull = false },
        new() { ColumnName = "SessionId", DataType = SqlDbType.NVarChar, DataLength = 200, AllowNull = false },
        new() { ColumnName = "IpAddress", DataType = SqlDbType.NVarChar, DataLength = 150, AllowNull = false },
        new() { ColumnName = "UrlAccessed", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "Data", DataType = SqlDbType.NVarChar, DataLength = -1, AllowNull = false },
        new() { ColumnName = "TimeAccessed", DataType = SqlDbType.DateTime, AllowNull = false }
    }
};

#endregion

#endregion
Serilog.Debugging.SelfLog.Enable(msg =>
{
    Debug.Print("SelfLog: " + msg);
    Debugger.Break();
});

Log.Logger = new LoggerConfiguration()
    .Enrich.FromLogContext()
    .MinimumLevel.Verbose()
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Level == LogEventLevel.Error)
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.ErrorLogTableName,
                AutoCreateSqlTable = true
            },
            appConfiguration: builder.Configuration,
            columnOptions: errorLogColumnOptions))
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Properties.ContainsKey("Change"))
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.UserAuditLogTableName,
                AutoCreateSqlTable = true
            },
            appConfiguration: builder.Configuration,
            columnOptions: userAuditColumnOptions))
    .WriteTo.Logger(lc => lc
        .Filter.ByIncludingOnly(evt => evt.Properties.ContainsKey("Change"))
        .WriteTo.MSSqlServer(
            connectionString: Configuration.ConnectionString,
            sinkOptions: new MSSqlServerSinkOptions
            {
                TableName = Configuration.SerilogConfig.AuditLogTableName,
                AutoCreateSqlTable = true
            },
            appConfiguration: builder.Configuration,
            columnOptions: auditColumnOptions))
    .WriteTo.Console()
    // .Filter.ByIncludingOnly(evt => evt.Properties.ContainsKey("Change") || evt.Level == LogEventLevel.Error)
    .CreateLogger().ForContext("Source", "CPInternalWebAPI");

Serilog.Debugging.SelfLog.Enable(msg =>
{
    Debug.Print("SelfLog: " + msg);
    Debugger.Break();
});

builder.Host.UseSerilog();

#endregion