Configure Column Options for Serilog Sinks MsSqlSe

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?

Answer 1

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");
   }    }