Unable to log to separate tables within one database

[eSamchuk]

My goal is to split logs into different tables depending on log entry level, so that Information and Warning records are separated from Error and Fatal. There are some guides on this topic, I used several of them, but without any success.

Serilog packages I am using:

• Serilog.AspNetCore 4.1.0
• Serilog.Sinks.MSSqlServer 5.6.1
• Serilog.Expressions 3.2.1
• Serilog.Formatting.Compact 1.1.0
• Serilog.Settings.Configuration 3.3.0
• Serilog.Sinks.Debug 2.0.0
• Serilog.Sinks.File 5.0.0

Target framework: .NET Core 3.1 Project Type: ASP.NET Core Web API Operating system: Windows 10,

What I know and what I've tried so far:

• Logging into single table works fine
• At the beginning I have used sa login to connect to my logging database, so it is not about permissions, but with no success.
• After that I used SQL script, provided in documentation to create role and user with permissions to CREATE TABLE, SELECT and INSERT
• Initially I used autoCreateSqlTable=true and required tables were created, but no records were written to them.
• Then I have abandoned using autoCreateSqlTable=true and used recommended script to create required tables ahead of time.
• batchPostingLimit has been set to 1 to enforce immediate log writing (Not sure that this could help at all, but gave it a try)
• SelfLog directed to the Debug window don't indicate any problems during application startup, which means that configuration (provided below) is correct.
• Any errors intentionally put into connection strings are ignored and not causing any activity in SelfLog, so it seems that logger is don't even trying to connect.

Serilog configuration in Program.cs:

public static IHostBuilder CreateHostBuilder(string[] args)
{
	return Host.CreateDefaultBuilder(args)
		.ConfigureWebHostDefaults(webBuilder => { webBuilder.UseStartup<Startup>().UseIISIntegration(); })
		.UseSerilog(
			(context, configuration) =>
			{
				configuration.ReadFrom.Configuration(context.Configuration);
			}
			);
}

Logging itself is done inside a middleware like this:

this._logger.Information("{5} request was sent to {0}, time was: {1}, remote IP address: {4}  user: {2}  request content is: {3}", request.Path, DateTime.Now, user, content, httpContext.Connection.RemoteIpAddress, httpContext.Request.Method);

Serilog configuration in appSettings.json:

  "Serilog": {
    "MinimumLevel": {
      "Default": "Verbose",
      "Override": {
        "Microsoft": "Warning"
      },
      "Using": [
        "Serilog.Sinks.Debug",
        "Serilog.Sinks.MSSqlServer",
        "Serilog.Sinks.File",
        "Serilog.Expressions",
        "Serilog.Settings.Configuration"
      ]
    },
    "WriteTo": [
      {
        "Name": "File",
        "Args": {
          "Path": "D:/file.txt",
          "rollingInterval": "Day",
          "outputTemplate": "[{Timestamp:HH:mm:ss}] [{Level:u3}] {Message:l} {NewLine}"
        }
      },
      {
        "Name": "Logger",
        "Args": {
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "(@Level = 'Error' or @Level = 'Fatal')"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "MSSqlServer",
                "Args": {
                  "connectionString": "Data Source=.\\SQLEXPRESS;User Id=Serilog;Password=password;Initial Catalog=LogsDb",
                  "tableName": "NmsRecipesLogs_Errors",
                  "batchPostingLimit": 1,
                  "columnOptionsSection": {
                    "removeStandardColumns": [ "MessageTemplate", "Properties" ]
                  }
                }
              }
            ]
          }
        }
      },
      {
        "Name": "Logger",
        "Args": {
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "(@Level = 'Information' or @Level = 'Warning')"
                }
              }
            ],
            "WriteTo": [
              {
                "Name": "MSSqlServer",
                "Args": {
                  "connectionString": "Data Source=.\\SQLEXPRESS;User Id=Serilog;Password=password;Initial Catalog=LogsDb",
                  "tableName": "NmsRecipesLogs_Info",
                  "autoCreateSqlTable": false,
                  "batchPostingLimit": 1,
                  "columnOptionsSection": {
                    "removeStandardColumns": [ "MessageTemplate", "Properties" ]
                  }
                }
              }
            ]
          }
        }
      }
    ]
  }

What am I missing?

[ckadluba]

Hi @eSamchuk!

Sorry for the delay. Can you provide us a full sample program to reproduce your issue?

Thanks, Christian

[eSamchuk]

Hi @ckadluba!

Full solution is here. It's a bit of mish-mash of everything that could be in WebAPI project, so don't freak out. Main project is this.

[ckadluba]

Sorry for the late response.

Have you tried to create and register two different loggers (logging pipelines) in the ASP.NET Core dependency container and inject them as separate ILogger instances as needed.

Possibly you could also have only one logging pipeline but use sub-loggers as shown here: https://github.com/serilog/serilog/wiki/Configuration-Basics#sub-loggers