AspNet Core Serilog SQL custom column not working

Question

I've setup SeriLog in my ASP.NET Core MVC Web App with dotnet 3.1, and added a couple of extra columns. Trying to populate UserName automatically does not work. All else works fine. I'm using the Microsoft.Extensions.Logging ILogger to inject my logger in the controllers. Can't this property be automatically set using the LogContext, or do I have to explicitly call the object for each log entry like so, ("Info logged by {UserName}", User.Identity.Name).

Here's my Program.cs

public static void Main(string[] args)
    {
        // Boilerplate code: https://github.com/serilog/serilog-aspnetcore/blob/dev/samples/Sample/Program.cs
        Log.Logger = new LoggerConfiguration()
            .WriteTo.Console()
            .CreateBootstrapLogger();

        Log.Information("Starting up!");

        try
        {
            CreateHostBuilder(args).Build().Run();
            Log.Information("Stopped cleanly");
        }
        catch (System.Exception ex)
        {
            Log.Fatal(ex, "An unhandled exception occured during bootstrapping");
            throw;
        }
        finally
        {
            Log.CloseAndFlush();
        }
    }

    public static IHostBuilder CreateHostBuilder(string[] args)
    {
        return Host.CreateDefaultBuilder(args)
                    .UseSerilog((context, services, configuration) => configuration
                        .ReadFrom.Configuration(context.Configuration)
                        .ReadFrom.Services(services)
                        .Enrich.FromLogContext()
                        .WriteTo.Console()
                        .WriteTo.MSSqlServer(
                            context.Configuration.GetConnectionString("FlightDelayDatabase"), 
                            sinkOptions: new MSSqlServerSinkOptions { TableName = "Log" }, null, null, 
                            LogEventLevel.Information, null, columnOptions: new ColumnOptions
                            {
                                AdditionalColumns = new Collection<SqlColumn>
                                {
                                   new SqlColumn("UserName", SqlDbType.NVarChar)
                                }
                            }, null, null))
                    .ConfigureWebHostDefaults(webBuilder => { webBuilder.UseStartup<Startup>(); });
    }

In my StartUp.cs I've this between app.UseAuthorization() and app.UseEndpoints()

app.Use(async (context, next) =>
        {
            var userName = context.User.Identity.IsAuthenticated ? context.User.Identity.Name : "Guest"; //Gets user Name from user Identity  
            LogContext.PushProperty("Username", userName); //Push user in LogContext;  
            await next(); 
        });

using Serilog.AspNetCore 4.1.0 and Serilog.Sinks.MSSqlServer 5.6.1.

Here's the schema

CREATE TABLE [dbo].[Log](  
[Id] [int] IDENTITY(1,1) NOT NULL,  
[Message] [nvarchar](max) NULL,  
[MessageTemplate] [nvarchar](max) NULL,  
[Level] [nvarchar](128) NULL,  
[TimeStamp] [datetimeoffset](7) NOT NULL,  
[Exception] [nvarchar](max) NULL,  
[Properties] [xml] NULL,  
[LogEvent] [nvarchar](max) NULL,  
[UserName] [nvarchar](200) NULL,  
[IP] [varchar](200) NULL,  

...

And from the database here's a log entry from the properties xml column.

Answer 1

You can use UseSerilogRequestLogging like below to add custom properties like Username and IPAddress. You should add it after UseAuthentication to get logged in Username.

app.UseAuthentication();

app.UseSerilogRequestLogging(options =>
{
    // Attach additional properties to the request completion event
    options.EnrichDiagnosticContext = (diagnosticContext, httpContext) =>
    {
        diagnosticContext.Set("UserName", httpContext.User.Identity.Name);
        diagnosticContext.Set("IP", httpContext.Connection.RemoteIpAddress.ToString());
    };
});

app.UseAuthorization();

Also Add Enrich.FormLogContext() to LoggerConfiguration

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Override("Microsoft", LogEventLevel.Information)
            .Enrich.FromLogContext() // <-- Add Enrich.FormLogContent()
            .WriteTo.Console()
            .CreateLogger();

You can find more details here https://github.com/serilog/serilog-aspnetcore