sql server - Serilog.Sinks.MSSqlServer not filling custom columns

Question

I've looked around, but I could not find a working solution, and since all examples look alike I assume it must be me overlooking something obvious. Also note this is my first real project using.NET Core and Serilog.

The Problem

I am trying to log (using serilog) some custom properties into their own columns in the database as defined in appsettings.json and added via middleware on the pipeline. But the values remain NULL, even though the same items are correctly filled in the properties XML column.

So I have the expected new properties for authenticated users:

<properties>
    <property key="UserId">ff88ddb9-6f5a-4ad5-a2a8-1d016ff99398</property>
    <property key="UserName">ernst</property>
<properties>

But I want to also add these values to their own columns as well for some query optimizations.

The code

The code is .NET Core 2.1+, and I've set up Serilog like shown at https://github.com/serilog/serilog-aspnetcore/blob/dev/samples/SimpleWebSample/Program.cs :

private static IConfiguration Configuration { get; } = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)
            .AddEnvironmentVariables()
            .Build();

public static int Main(string[] args)
{
  Log.Logger = new LoggerConfiguration()
      .ReadFrom.Configuration(Configuration)
      .Enrich.FromLogContext()
      .MinimumLevel.Debug()
      .CreateLogger();
  try 
  {
    Log.Information($"Starting web host in {Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"} environment, version {Util.Version.Long}");
    BuildWebHost(args).Run();
    return 0;
  }
  catch (Exception ex)
  {
    Log.Fatal(ex, "Host terminated unexpectedly");
    return 1;
  }
  finally
  {
    Log.CloseAndFlush();
  }
}

 private static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
                .UseStartup<Startup>()
                .UseConfiguration(Configuration)
                .UseSerilog()
                .CaptureStartupErrors(true)
                .Build();

To add the information I use LogContext.PushProperty in my middleware.

The relevant pieces of the configuration:

"Serilog": {
    "Using": [ "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": "Debug",
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ],
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Data Source=localhost;Database=MyDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False",
          "tableName": "SeriLog_Custom",
          "autoCreateSqlTable": true,
          "restrictedToMinimumLevel": "Information",
          "columnOptionsSection": {
            "customColumns": [
              {
                "ColumnName": "UserId",
                "DataType": "nvarchar",
                "DataLength": 450,
                "AllowNull": true
              },
              {
                "ColumnName": "UserName",
                "DataType": "nvarchar",
                "DataLength": 256,
                "AllowNull": true

              }
            ]
          }
        }
      }
    ]
  }

Furthermore, besides not filling the custom columns, the database is also created in the standard way without these column, so I added them by hand.

This could point to a configuration issue?

The SQL to create the table:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[SeriLog_Custom](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Message] [nvarchar](max) NULL,
    [MessageTemplate] [nvarchar](max) NULL,
    [Level] [nvarchar](128) NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Exception] [nvarchar](max) NULL,
    [Properties] [xml] NULL,
    [UserId] [nvarchar](450) NULL,
    [UserName] [nvarchar](256) NULL,
 CONSTRAINT [PK_SeriLog_Custom] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Now I can get the desired info with:

SELECT [Id]
      ,[Message]
      ,[MessageTemplate]
      ,[Level]
      ,[TimeStamp]
      ,[Exception]
      ,[UserId]
      ,[UserName]
      ,[Properties].value('(//property[@key="UserName"]/node())[1]', 'nvarchar(max)') as UserName
      ,[Properties]
  FROM [ECSControl].[dbo].[SeriLog_ECMonitor]
  where [Properties].value('(//property[@key="UserName"]/node())[1]', 'nvarchar(max)') = 'ernst'
  order by id desc;

But I want to access it with:

SELECT [Id]
      ,[Message]
      ,[MessageTemplate]
      ,[Level]
      ,[TimeStamp]
      ,[Exception]
      ,[UserId]
      ,[UserName]
      ,[Properties]
  FROM [ECSControl].[dbo].[SeriLog_ECMonitor]
  where  UserName = 'ernst'
  order by id desc;

Answer 1

The latest stable version of Serilog.Sinks.MSSqlServer (5.1.2) doesn't have Microsoft.Extensions.Configuration support. The same with the latest stable version of Serilog.Settings.Configuration (2.6.1) (ref).

Updating to pre-release versions Serilog.Sinks.MSSqlServer 5.1.3-dev-00204 and Serilog.Settings.Configuration 3.0.0-dev-00119 solves this issue.