I am trying to implement logging using serilog in one of my demo app (using .net 6). I am logging to sql database. I have kept "autoCreateSqlTable" to "true" in appSettings.json but serilog is not creating table in sql.

appSettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "Serilog": {
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Warning",
        "System": "Warning",
        "System.Net.Http.HttpClient": "Warning"
      }
    }
  },
  "Using": [ "Serilog.Enrichers.ClientInfo" ],
  "Enrich": [ "FromLogContext", "WithMachineName", "WithClientIp", "WithClientAgent" ],
  "WriteTo": [
    {
      "Name": "MSSqlServer",
      "Args": {
        "connectionString": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;",
        "sinkOptionsSection": {
          "tableName": "LogsTable",          
          "autoCreateSqlTable": true          
        },
        "restrictedToMinimumLevel": "Information",
        "columnOptionsSection": {
          "primaryKeyColumnName": "Id",
          "addStandardColumns": [ "LogEvent", "SourceContext" ],
          "additionalColumns": [
            {
              "ColumnName": "ClientIP",
              "PropertyName": "ClientIp",
              "DataType": "nvarchar"
            }            
          ]
        }
      }
    }
  ]
}

Program.cs

using Serilog;
using Serilog.Events;

var builder = WebApplication.CreateBuilder(args);


#region Configure serilog

builder.Logging.ClearProviders();
var configuration = new ConfigurationBuilder()
        .AddJsonFile("appsettings.json")
        .Build();

var logger = new LoggerConfiguration()
                .ReadFrom.Configuration(configuration)
                .MinimumLevel.Override("Microsoft", LogEventLevel.Error)
                .MinimumLevel.Override("Microsoft.AspNetCore", LogEventLevel.Error)
                .MinimumLevel.Override("Serilog", LogEventLevel.Error)
                .Enrich.FromLogContext()
                .Enrich.WithClientIp()
                .Enrich.WithClientAgent()
                .CreateLogger();

Log.Logger = logger;
builder.Logging.AddSerilog(logger);
builder.Host.UseSerilog();

// Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg));

#endregion


// Add services to the container.
builder.Services.AddControllersWithViews();

var app = builder.Build();

// Configure the HTTP request pipeline.
app.UseSerilogRequestLogging();
if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
}
app.UseStaticFiles();

app.UseRouting();

app.UseAuthorization();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}");

app.Run();

Below are my nuget packages:

Serilog.AspNetCore - 6.0
Serilog.Settings.Configuration - 3.3.0
Serilog.Sinks.MSSqlServer - 5.7.1
Serilog.Enrichers.ClientInfo - 1.2.0

I tested the connection string and it is working fine but somehow serilog is not creating table. I am not understanding where I am going wrong.

Can anybody help me on this?

CodePudding user response：

the problem is with your appsettings.json.

using,enrich and other sections are out of serilog section:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "Serilog": {
    "Using": [ "Serilog.Enrichers.ClientInfo","Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Warning",
        "Microsoft.Hosting.Lifetime": "Warning",
        "System": "Warning",
        "System.Net.Http.HttpClient": "Warning"
      }
    },
  "Enrich": [ "FromLogContext", "WithMachineName", "WithClientIp", "WithClientAgent" ],
  "WriteTo": [
    {
      "Name": "MSSqlServer",
      "Args": {
        "connectionString": "Server=Test;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true;",
        "sinkOptionsSection": {
          "tableName": "LogsTable",          
          "autoCreateSqlTable": true          
        },
        "restrictedToMinimumLevel": "Information",
        "columnOptionsSection": {
          "primaryKeyColumnName": "Id",
          "addStandardColumns": [ "LogEvent", "SourceContext" ],
          "additionalColumns": [
            {
              "ColumnName": "ClientIP",
              "PropertyName": "ClientIp",
              "DataType": "nvarchar"
            }            
          ]
        }
      }
    }
  ]
 }
}