Serilog.Sinks.MSSqlServer不填充自定义列 [英] Serilog.Sinks.MSSqlServer not filling custom columns
问题描述
我环顾四周，但找不到有效的解决方案，并且由于所有示例看起来都相似，因此我认为这一定是我忽略了明显的内容. 还要注意，这是我使用.NET Core和Serilog进行的第一个实际项目.
I've looked around, but I could not find a working solution, and since all examples look alike I assume it must be me overlooking something obvious. Also note this is my first real project using.NET Core and Serilog.
我试图将一些自定义属性(使用serilog)记录到数据库中自己的列中，如appsettings.json中所定义，并通过中间件添加到管道中.但是，即使在属性XML列中正确填写了相同的项目，这些值仍为NULL.
I am trying to log (using serilog) some custom properties into their own columns in the database as defined in appsettings.json and added via middleware on the pipeline. But the values remain NULL, even though the same items are correctly filled in the properties XML column.
因此，对于身份验证的用户，我具有预期的新属性:
So I have the expected new properties for authenticated users:
<properties>
<property key="UserId">ff88ddb9-6f5a-4ad5-a2a8-1d016ff99398</property>
<property key="UserName">ernst</property>
<properties>
但是我也想将这些值也添加到它们自己的列中，以进行一些查询优化.
But I want to also add these values to their own columns as well for some query optimizations.
代码是.NET Core 2.1+，我已经设置了Serilog，如
The code is .NET Core 2.1+, and I've set up Serilog like shown at https://github.com/serilog/serilog-aspnetcore/blob/dev/samples/SimpleWebSample/Program.cs :
private 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()
.ReadFrom.Configuration(Configuration)
.Enrich.FromLogContext()
.MinimumLevel.Debug()
.CreateLogger();
try
{
Log.Information($"Starting web host in {Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"} environment, version {Util.Version.Long}");
BuildWebHost(args).Run();
return 0;
}
catch (Exception ex)
{
Log.Fatal(ex, "Host terminated unexpectedly");
return 1;
}
finally
{
Log.CloseAndFlush();
}
}
private static IWebHost BuildWebHost(string[] args) =>
WebHost.CreateDefaultBuilder(args)
.UseStartup<Startup>()
.UseConfiguration(Configuration)
.UseSerilog()
.CaptureStartupErrors(true)
.Build();
要添加信息，请在中间件中使用LogContext.PushProperty.
To add the information I use LogContext.PushProperty in my middleware.
配置的相关部分:
"Serilog": {
"Using": [ "Serilog.Sinks.MSSqlServer" ],
"MinimumLevel": "Debug",
"Enrich": [ "FromLogContext", "WithMachineName", "WithThreadId" ],
"WriteTo": [
{
"Name": "MSSqlServer",
"Args": {
"connectionString": "Data Source=localhost;Database=MyDb;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False",
"tableName": "SeriLog_Custom",
"autoCreateSqlTable": true,
"restrictedToMinimumLevel": "Information",
"columnOptionsSection": {
"customColumns": [
{
"ColumnName": "UserId",
"DataType": "nvarchar",
"DataLength": 450,
"AllowNull": true
},
{
"ColumnName": "UserName",
"DataType": "nvarchar",
"DataLength": 256,
"AllowNull": true
}
]
}
}
}
]
}
此外，除了不填充自定义列外，数据库也以标准方式创建，而没有这些列，因此我手动添加了它们.
Furthermore, besides not filling the custom columns, the database is also created in the standard way without these column, so I added them by hand.
这可能指向配置问题?
创建表的SQL:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SeriLog_Custom](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Message] [nvarchar](max) NULL,
[MessageTemplate] [nvarchar](max) NULL,
[Level] [nvarchar](128) NULL,
[TimeStamp] [datetime] NOT NULL,
[Exception] [nvarchar](max) NULL,
[Properties] [xml] NULL,
[UserId] [nvarchar](450) NULL,
[UserName] [nvarchar](256) NULL,
CONSTRAINT [PK_SeriLog_Custom] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
现在，我可以通过以下方式获得所需的信息:
Now I can get the desired info with:
SELECT [Id]
,[Message]
,[MessageTemplate]
,[Level]
,[TimeStamp]
,[Exception]
,[UserId]
,[UserName]
,[Properties].value('(//property[@key="UserName"]/node())[1]', 'nvarchar(max)') as UserName
,[Properties]
FROM [ECSControl].[dbo].[SeriLog_ECMonitor]
where [Properties].value('(//property[@key="UserName"]/node())[1]', 'nvarchar(max)') = 'ernst'
order by id desc;
但是我想通过以下方式访问它:
But I want to access it with:
SELECT [Id]
,[Message]
,[MessageTemplate]
,[Level]
,[TimeStamp]
,[Exception]
,[UserId]
,[UserName]
,[Properties]
FROM [ECSControl].[dbo].[SeriLog_ECMonitor]
where UserName = 'ernst'
order by id desc;
推荐答案
Serilog.Sinks.MSSqlServer (5.1.2)的最新稳定版本不支持Microsoft.Extensions.Configuration.与Serilog.Settings.Configuration (2.6.1)的最新稳定版本相同(参考).
The latest stable version of Serilog.Sinks.MSSqlServer (5.1.2) doesn't have Microsoft.Extensions.Configuration support. The same with the latest stable version of Serilog.Settings.Configuration (2.6.1) (ref).
更新到预发行版本Serilog.Sinks.MSSqlServer 5.1.3-dev-00204和Serilog.Settings.Configuration 3.0.0-dev-00119可解决此问题.
Updating to pre-release versions Serilog.Sinks.MSSqlServer 5.1.3-dev-00204 and Serilog.Settings.Configuration 3.0.0-dev-00119 solves this issue.
这篇关于Serilog.Sinks.MSSqlServer不填充自定义列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
