.NET中Serilog配置Sink级过滤器实现分表日志写入失败求助

问题背景 #

在.NET项目中使用Serilog，希望将日志写入SQL Server的不同表，要求完全通过appsettings.json配置实现每个Sink的专属过滤器，而非代码方式。当前配置中全局过滤器可生效，但针对SQL Server Sink的专属过滤器未起作用，所有日志都写入Log_ApiRequests表，不符合过滤条件的日志也会写入。

原配置片段：

"Serilog": {
  "Using": [
    "Serilog.Sinks.Console",
    "Serilog.Sinks.File",
    "Serilog.Sinks.MSSqlServer",
    "Serilog.Expressions"
  ],
  "MinimumLevel": {
    "Default": "Information"
  },
  //"Filter": [
  //  {
  //    "Name": "ByIncludingOnly",
  //    "Args": {
  //      "expression": "SourceContext like '%ApiRequest%'"
  //    }
  //  }
  //],
  "WriteTo": [
    {
      "Name": "Console",
      "Args": {
        "restrictedToMinimumLevel": "Information",
        "theme": "Serilog.Sinks.SystemConsole.Themes.AnsiConsoleTheme::Code, Serilog.Sinks.Console",
        "outputTemplate": "[{Timestamp:u} {Level:u3}] {Message:lj} <{SourceContext}> <{LogTypeName}>{NewLine}{Exception}"
      }
    },
    {
      "Name": "File",
      "Args": {
        "restrictedToMinimumLevel": "Debug",
        "path": "Logs/log.txt",
        "outputTemplate": "[{Timestamp:u} {Level:u3}] {Message:lj} <{SourceContext}> <{LogTypeName}>{NewLine}{Exception}",
        "rollOnFileSizeLimit": true,
        "fileSizeLimitBytes": 4194304,
        "retainedFileCountLimit": 500,
        "rollingInterval": "Day"
      }
    },
    {
      "Name": "MSSqlServer",
      "Args": {
        "connectionString": "DocumentsDatabaseConnectionString",
        "sinkOptionsSection": {
          "tableName": "Log_ApiRequests",
          "autoCreateSqlTable": true,
          "configureLogger": {
            "Filter": [
              {
                "Name": "ByIncludingOnly",
                "Args": {
                  "expression": "SourceContext like '%ApiRequest%'"
                }
              }
            ]
          }
        },
        "restrictedToMinimumLevel": "Information"
      }
    },
    {
      "Name": "MSSqlServer",
      "Args": {
        "connectionString": "DocumentsDatabaseConnectionString",
        "sinkOptionsSection": {
          "tableName": "Log_ApiResponses",
          "autoCreateSqlTable": true
        },
        "columnOptionsSection": {
          "additionalColumns": [
            {
              "ColumnName": "EventId",
              "DataType": "uniqueidentifier",
              "AllowNull": false
            },
            {
              "ColumnName": "HTTPMethod",
              "DataType": "nvarchar",
              "DataLength": 50
            },
            {
              "ColumnName": "Endpoint",
              "DataType": "nvarchar",
              "DataLength": 256
            },
            {
              "ColumnName": "Body",
              "DataType": "nvarchar",
              "DataLength": 4000
            },
            {
              "ColumnName": "StatusCode",
              "DataType": "int"
            },
            {
              "ColumnName": "RequestGuid",
              "DataType": "uniqueidentifier",
              "AllowNull": true
            },
            {
              "ColumnName": "ResponseTime",
              "DataType": "int",
              "AllowNull": true
            }
          ]
        },
        "restrictedToMinimumLevel": "Information"
      }
    }
  ],
  "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ]
}

配置错误分析 #

你在MSSqlServer Sink的sinkOptionsSection下添加configureLogger配置是错误的，Serilog的SQL Server Sink并不支持在sinkOptionsSection内部配置过滤器。这种写法完全无效，导致过滤规则根本没有被应用到目标Sink上。

正确配置方案 #

要为单个Sink配置专属过滤器，需要使用Serilog的Filter扩展将目标Sink包裹起来，通过顶层的WriteTo项定义过滤逻辑，再将Sink嵌套在过滤规则内部。以下是修正后的配置：

"Serilog": {
  "Using": [
    "Serilog.Sinks.Console",
    "Serilog.Sinks.File",
    "Serilog.Sinks.MSSqlServer",
    "Serilog.Expressions"
  ],
  "MinimumLevel": {
    "Default": "Information"
  },
  "WriteTo": [
    // Console和File配置保持不变
    {
      "Name": "Console",
      "Args": {
        "restrictedToMinimumLevel": "Information",
        "theme": "Serilog.Sinks.SystemConsole.Themes.AnsiConsoleTheme::Code, Serilog.Sinks.Console",
        "outputTemplate": "[{Timestamp:u} {Level:u3}] {Message:lj} <{SourceContext}> <{LogTypeName}>{NewLine}{Exception}"
      }
    },
    {
      "Name": "File",
      "Args": {
        "restrictedToMinimumLevel": "Debug",
        "path": "Logs/log.txt",
        "outputTemplate": "[{Timestamp:u} {Level:u3}] {Message:lj} <{SourceContext}> <{LogTypeName}>{NewLine}{Exception}",
        "rollOnFileSizeLimit": true,
        "fileSizeLimitBytes": 4194304,
        "retainedFileCountLimit": 500,
        "rollingInterval": "Day"
      }
    },
    // 为Log_ApiRequests表配置带过滤器的Sink
    {
      "Name": "Filter",
      "Args": {
        "expression": "SourceContext like '%ApiRequest%'",
        "writeTo": [
          {
            "Name": "MSSqlServer",
            "Args": {
              "connectionString": "DocumentsDatabaseConnectionString",
              "sinkOptionsSection": {
                "tableName": "Log_ApiRequests",
                "autoCreateSqlTable": true
              },
              "restrictedToMinimumLevel": "Information"
            }
          }
        ]
      }
    },
    // Log_ApiResponses表的Sink（可按需添加自身过滤规则）
    {
      "Name": "MSSqlServer",
      "Args": {
        "connectionString": "DocumentsDatabaseConnectionString",
        "sinkOptionsSection": {
          "tableName": "Log_ApiResponses",
          "autoCreateSqlTable": true
        },
        "columnOptionsSection": {
          "additionalColumns": [
            {
              "ColumnName": "EventId",
              "DataType": "uniqueidentifier",
              "AllowNull": false
            },
            {
              "ColumnName": "HTTPMethod",
              "DataType": "nvarchar",
              "DataLength": 50
            },
            {
              "ColumnName": "Endpoint",
              "DataType": "nvarchar",
              "DataLength": 256
            },
            {
              "ColumnName": "Body",
              "DataType": "nvarchar",
              "DataLength": 4000
            },
            {
              "ColumnName": "StatusCode",
              "DataType": "int"
            },
            {
              "ColumnName": "RequestGuid",
              "DataType": "uniqueidentifier",
              "AllowNull": true
            },
            {
              "ColumnName": "ResponseTime",
              "DataType": "int",
              "AllowNull": true
            }
          ]
        },
        "restrictedToMinimumLevel": "Information"
      }
    }
  ],
  "Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ]
}

关键注意事项 #

• 必须确保Serilog.Expressions包已安装且包含在Using列表中，过滤表达式依赖该包才能生效。
• 每个需要独立过滤的Sink都要单独用Filter项包裹，这样不同Sink可以遵循各自的过滤规则写入对应表。
• 过滤表达式支持Serilog表达式语法，可根据需求调整规则（比如按LogTypeName、日志级别等过滤）。

内容的提问来源于stack exchange，提问作者The Expert