Configure Column Options for Serilog Sinks MsSqlServer in AppSettings.json

Question

I'm trying to determine if it's possible to configure the column options for serilog sink mssqlserver in the appsettings.json file for an ASP.Net Core 2 project.

I create and configure the logger in the Program.cs file.

        public 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()
                //.Enrich.WithProperty("AppName", "One Badass App") // Adds property to XML structure in properties column
                .ReadFrom.Configuration(Configuration)
                .CreateLogger();

                try
                {
                    Log.Information("Starting web host");
                    BuildWebHost(args).Run();
                    return 0;
                }
                catch (Exception ex)
                {
                    Log.Fatal(ex, "Host terminated unexpectedly");
                    return 1;
                }
                finally
                {
                    Log.CloseAndFlush();
                }
        }

I can build the configuration file from the appsettings.json file, which contains a Serilog node with information for which connection string and table to use.

{
  "AppSettings": {
    "Application": {
      "Name": "Payment Processing API",
      "Version":  "1.0"
    }
  },
  "ConnectionStrings": {
    "localPaymentProcessingDb": "Server=(localdb)\\mssqllocaldb;Database=PaymentProcessing;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Serilog": {
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=(localdb)\\mssqllocaldb;Database=PaymentProcessing;Trusted_Connection=True;MultipleActiveResultSets=true",
          "tableName": "Logs"
        }
      }
    ]
 }

There's an open issue on Github for this, but I haven't found any other information about it.

If column options can't be configured in the appsettings.json, where and how should they be configured in an ASP.Net Core 2 project?

Neoplasty

Answer 1

If anyone else stumbles across this same issue the linked GitHub issue in the question now contains the answer:

This is now possible with the latest SQL sink and Serilog.Settings.Configuration packages.

Also, I know this question asks for a .NET Core 2 answer, and it sounds like the linked GitHub page answers this for 2, I am using .NET Core 3.1 and the following worked for me (hopefully it works for 2 as well)

I installed the Serilog.Settings.Configuration Nuget package and used the following Serilog appsettings.json configuration:

"Serilog":{
   "MinimumLevel":"Information",
   "WriteTo":[
      {
         "Name":"MSSqlServer",
         "Args":{
            "connectionString":"DbContext",
            "tableName":"EventLog",
            "autoCreateSqlTable":true,
            "columnOptionsSection":{
               "addStandardColumns":[
                  "LogEvent"
               ],
               "removeStandardColumns":[
                  "MessageTemplate",
                  "Properties"
               ]
            }
         }
      }
   ]
}

Jolynjolynn

Answer 2

For accessing Serilog from appsettings in Code, you could bind Serilog node to Serilog class.
Here are the detail steps.
1. Create Serilog Configuration Class

    public class SerilogConfiguration
{
    public LogEventLevel MinimumLevel { get; set; }
    public List<WriteTo> WriteTo { get; set; }
}
public class WriteTo
{
    public string Name { get; set; }
    public Args Args { get; set; }
}
public class Args
{
    public string ConnectionString { get; set; }
    public string TableName { get; set; }
    public List<StandardColumn> Add { get; set; }
    public List<StandardColumn> Remove { get; set; }
}

2. Configure appsettings.json

Code:

{
    "Serilog": {
    "MinimumLevel": "Error",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "<our connection string>",
          "tableName": "Log",
          "Remove": [ "Properties" ],
          "Add": [ "LogEvent" ]
        }
      }
    ]
  }
}

3. Bind appsetting.json to class

   public void ConfigureServices(IServiceCollection services)
   {           
       services.Configure<SerilogConfiguration>(Configuration.GetSection("Serilog"));            
   }
   

4. Access Configuration

   public class HomeController : Controller    {
   private readonly SerilogConfiguration _configuration;
   public HomeController(IOptions<SerilogConfiguration> configuration)
   {
       _configuration = configuration.Value;
   }       
   
   public IActionResult AppSettings()
   {
       var columnOptions = new ColumnOptions();
       var MSSqlServer = _configuration.WriteTo.Where(wt => wt.Name == "MSSqlServer").FirstOrDefault();
       // Don't include the Properties XML column.
       foreach(var columnRemove in MSSqlServer.Args.Remove)
       {
           columnOptions.Store.Remove(columnRemove);
       }
       // Do include the log event data as JSON.
       foreach (var columnAdd in MSSqlServer.Args.Add)
       {
           columnOptions.Store.Add(columnAdd);
       }
   
       Log.Logger = new LoggerConfiguration()
           .WriteTo.MSSqlServer(MSSqlServer.Args.ConnectionString, MSSqlServer.Args.TableName, columnOptions: columnOptions,
               restrictedToMinimumLevel: _configuration.MinimumLevel)
           .CreateLogger();
       return Ok("OK");
   }    }
   

Marchellemarcher