Problem switching configuration from code to appsettings.json

[OculiViridi]

In my project I'm trying to switch from the Fluent configuration in code to an appsettings.json one.
Here there is my C# Fluent configuration

ColumnOptions columnOptions = new ColumnOptions();
columnOptions.Store.Remove(StandardColumn.Properties);
columnOptions.Store.Add(StandardColumn.LogEvent);
columnOptions.Level.StoreAsEnum = true;

LoggingLevelSwitch levelSwitch = new LoggingLevelSwitch();
	levelSwitch.MinimumLevel = LogEventLevel.Debug;
				
Log.Logger = new LoggerConfiguration()
	.MinimumLevel.ControlledBy(levelSwitch)
	.MinimumLevel.Override("Microsoft", LogEventLevel.Warning)
	.Enrich.WithMachineName()
	.Enrich.WithProperty("Application", "My App")
	.Enrich.WithProperty("Environment", Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT"))
	.Enrich.WithThreadId()
	.Enrich.With<EventTypeEnricher>()
	.Enrich.FromLogContext()
	.WriteTo.Console(outputTemplate: "[{Timestamp:HH:mm:ss.fff} {ThreadId} {EventType:x8} {Level:u3}] {Message:lj}{NewLine}{Exception}")
	.WriteTo.MSSqlServer(configuration.GetConnectionString("LogConnectionString"), "Logs", columnOptions: columnOptions, autoCreateSqlTable: true)
	.CreateLogger();

And this is my actual appsettings.json

{
  "ConnectionStrings": {
    "DbContext": "Server=..."
  },
  "Serilog": {
    "Using": [ "Serilog.Sinks.Console", "Serilog.Sinks.MSSqlServer", "MyProject.AssemblyName" ],
    "MinimumLevel": {
      "Default": "Debug",
      "Override": {
        "Microsoft": "Warning",
        "System": "Warning"
      }
    },
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId", "EventTypeEnricher" ],
    "Properties": {
      "Application": "My App",
      "Environment": "Development"
    },
    "LevelSwitches": { "$levelSwitch": "Debug" },
    "WriteTo": [
      {
        "Name": "Console",
        "Args": {
          "outputTemplate": "[{Timestamp:HH:mm:ss.fff} {ThreadId} {EventType:x8} {Level:u3}] {Message:lj}{NewLine}{Exception}"
        }
      },
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=...",
          "tableName": "Logs",
          "columnOptionsSection": {
            "addStandardColumns": [ "LogEvent" ],
            "removeStandardColumns": [ "Properties" ],
            "level": {
              "columnName": "Level",
              "storeAsEnum": true
            }
          },
          "autoCreateSqlTable": true
        }
      }
    ]
  }
}

Now I'm currently dealing with some problems:

1. My custom Enricher EventTypeEnricher, defined inside the MyProject.AssemblyName that I added to Using, is not loaded. I can see because of the Console output template shows a blank space instead of the numeric values.

2. Adding the MSSqlServer block leads to the following Exception:

   Exception has been thrown by the target of an invocation.
   {System.Data.DuplicateNameException: A column named 'Level' already belongs to this DataTable.}

3. I'm not sure about configuration of LevelSwitches...

4. Is it possible to configure the Properties.Environment value dynamically, like it is in my code?

The in code configuration is fully functional.
My actual versions of Serilog packages are:

• Serilog.AspNetCore v2.1.1
• Serilog.Sinks.Console v3.1.1
• Serilog.Sinks.MSSqlServer v5.1.3-dev-00202
• Serilog.Settings.Configuration v3.0.0-dev-00119
• Serilog.Enrichers.Environment v2.1.2

Thanks!

UPDATE

About problem with MSSqlServer configuration, if I remove the

"level": {
  "columnName": "Level",
  "storeAsEnum": true
}

block, I've no more Exceptions, but that leads (obviously) to a runtime Exception:

Unable to write 2 log events to the database due to following error: The given value of type String from the data source cannot be converted to type tinyint of the specified target column.

[MV10]

I'll set up some tests to see what might be happening with the Enricher and SQL level option. (I've used a custom enricher with this config package before, so I know it should work.)

I've not used LevelSwitches but I know there are a lot of threads about it and the XUnit tests pass... :)

Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") is executable code so you can't put it into the JSON, but the Microsoft configuration builder packages are additive -- you should be able to pull that configuration item in using Microsoft.Extensions.Configuration.EnvironmentVariables. (I haven't done this myself but it should work...)

[OculiViridi]

With the configuration file and loading code below, I'm getting this error:

{System.Data.DuplicateNameException: A column named 'Level' already belongs to this DataTable.
at System.Data.DataColumnCollection.RegisterColumnName(String name, DataColumn column)
at System.Data.DataColumnCollection.BaseAdd(DataColumn column)
at System.Data.DataColumnCollection.AddAt(Int32 index, DataColumn column)
at Serilog.Sinks.MSSqlServer.MSSqlServerSinkTraits.CreateDataTable()
at Serilog.Sinks.MSSqlServer.MSSqlServerSinkTraits..ctor(String connectionString, String tableName, String schemaName, ColumnOptions columnOptions, IFormatProvider formatProvider, Boolean autoCreateSqlTable)
at Serilog.LoggerConfigurationMSSqlServerExtensions.MSSqlServer(LoggerSinkConfiguration loggerConfiguration, String connectionString, String tableName, IConfiguration appConfiguration, LogEventLevel restrictedToMinimumLevel, Int32 batchPostingLimit, Nullable`1 period, IFormatProvider formatProvider, Boolean autoCreateSqlTable, ColumnOptions columnOptions, IConfigurationSection columnOptionsSection, String schemaName)}

"Serilog": {
  "MinimumLevel": {
    "Default": "Debug",
    "Override": {
  	"Microsoft": "Warning",
  	"System": "Warning"
    }
  },
  "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ],
  "Properties": {
    "Application": "My App",
    "Environment": "Development"
  },
  "WriteTo": [
    {
  	"Name": "Console",
  	"Args": {
  	  "outputTemplate": "[{Timestamp:HH:mm:ss.fff} {ThreadId} {EventType:x8} {Level:u3}] {Message:lj}{NewLine}{Exception}"
  	}
    },
    {
  	"Name": "MSSqlServer",
  	"Args": {
  	  "connectionString": "Server=...",
  	  "tableName": "Logs",
  	  "columnOptionsSection": {
  		"addStandardColumns": [ "LogEvent" ],
  		"removeStandardColumns": [ "Properties" ],
  		"level": {
  		  "columnName": "Level",
  		  "storeAsEnum": true
  		}
  	  },
  	  "autoCreateSqlTable": true
  	}
    }
  ]
}

Log.Logger = new LoggerConfiguration()
	.ReadFrom.Configuration(configuration)
	.CreateLogger();

The EventTypeEnricher and LevelSwitch options were removed. The SQL Server Logs table is already in place, I'm getting the error only when using JSON configuration file instead of the following Fluent configuration.

Log.Logger = new LoggerConfiguration()
	MinimumLevel.Override("Microsoft", LogEventLevel.Warning)
	Enrich.WithMachineName()
	Enrich.WithProperty("Application", "DCS Engine")
	Enrich.WithProperty("Environment", Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT"))
	Enrich.WithThreadId()
	Enrich.FromLogContext()
	WriteTo.Console(outputTemplate: "[{Timestamp:HH:mm:ss.fff} {ThreadId} {EventType:x8} {Level:u3}] {Message:lj}{NewLine}{Exception}")
	WriteTo.MSSqlServer(configuration.GetConnectionString("LogConnectionString"), "Logs", columnOptions: columnOptions, autoCreateSqlTable: true)
	.CreateLogger();

[MV10]

I found the bug with the level column settings. I'll set up a PR to get this one fixed immediately.

[MV10]

I merged the PR so when the next dev build is ready for the SQL sink, you should be all set.

[OculiViridi]

@MV10 And so, I'm set now! 👍
My JSON configuration is actually working.

About the EventTypeEnricher, I found that I was missing the appropriate extension method:

public static LoggerConfiguration WithEventType(this LoggerEnrichmentConfiguration enrichmentConfiguration)
{
	if (enrichmentConfiguration == null) throw new ArgumentNullException(nameof(enrichmentConfiguration));
	return enrichmentConfiguration.With<EventTypeEnricher>();
}

and the correct configuration on JSON file:

"Serilog": {
    "Using": [ "MyApp.Assembly.Name" ],
    "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId", **"WithEventType"** ],
}

Thank you very much!

[MV10]

Glad to help.