Serilog 7 - Custom tables for Error, Audit and User Audit Logging using EF 7 · serilog-mssql/serilog-sinks-mssqlserver · Discussion #476

Bakeries
Jul 6, 2023

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();

Replies: 1 comment 6 replies

jonorossi
Jul 6, 2023
Maintainer

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.

6 replies

@jonorossi

jonorossi Jul 6, 2023
Maintainer

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

@Bakeries

Bakeries Jul 7, 2023
Author

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

@jonorossi

jonorossi Jul 7, 2023
Maintainer

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

@Bakeries

Bakeries Jul 9, 2023
Author

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

Bakeries Jul 10, 2023
Author

@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

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

Uh oh!

Uh oh!

Bakeries Jul 6, 2023

Replies: 1 comment · 6 replies

Uh oh!

jonorossi Jul 6, 2023 Maintainer

Uh oh!

jonorossi Jul 6, 2023 Maintainer

Uh oh!

Bakeries Jul 7, 2023 Author

Uh oh!

jonorossi Jul 7, 2023 Maintainer

Uh oh!

Bakeries Jul 9, 2023 Author

Uh oh!

Bakeries Jul 10, 2023 Author

Bakeries
Jul 6, 2023

Replies: 1 comment 6 replies

jonorossi
Jul 6, 2023
Maintainer

jonorossi Jul 6, 2023
Maintainer

Bakeries Jul 7, 2023
Author

jonorossi Jul 7, 2023
Maintainer

Bakeries Jul 9, 2023
Author

Bakeries Jul 10, 2023
Author