search

serilog-mssql / serilog-sinks-mssqlserver

A Serilog sink that writes events to Microsoft SQL Server and Azure SQL
Apache License 2.0
274 stars 147 forks source link

Serilog not filling custom column, and no error in selflog. #534

Open LouisMach opened 2 months ago

LouisMach commented 2 months ago

Bug Report / Support Request Template

Please clearly describe what the SQL Sink is doing incorrectly:

I have set up database logging which works, but my custom column is always null even though the property exists and I have added a mapping for the column. SelfLog to console and debug is enabled, but no error shows up. I can trigger a visible selflog by setting an invalid connection string, so I think selflogging is working correctly.

Please clearly describe the expected behavior:

I expect Serilog to fill the column with the value of the property when the property matches the mapping. I also expect Serilog to emit an error on selflog when an error prevents writing to the column.

List the names and versions of all Serilog packages used in the project:

Target framework and operating system:

.NET 7.0 OS: Windows 10

Provide a simple reproduction of your Serilog configuration code:

https://gist.github.com/LouisMach/a6c41f017dcb2fcf3d7e2ea79d880c82#file-program-cs

Provide a simple reproduction of your Serilog configuration file, if any:

None, set in code.

Provide a simple reproduction of your application code:

Code: https://gist.github.com/LouisMach/a6c41f017dcb2fcf3d7e2ea79d880c82#file-service-cs SQL script: https://gist.github.com/LouisMach/a6c41f017dcb2fcf3d7e2ea79d880c82#file-tbllog-sql

The property is correctly added to the properties and scope part of the log entry, this is the content of the property column:

<properties>
    <property key='SourceContext'>MyProject.Services.x.Service</property>
    <property key='AssetNumber'>123</property>
    <property key='ActionId'>643b5dd4-1147-4c4b-aacf-e3f6766cbeac</property>
    <property key='ActionName'>MyProject.Controllers.x.Controller.Sync (projname)</property>
    <property key='RequestId'>redacted</property>
    <property key='RequestPath'>/api/v1/project/sync</property>
    <property key='ConnectionId'>redacted</property>
    <property key='Scope'>
        <sequence>
            <item>Asset Number: 123</item>
        </sequence>
    </property>
</properties>

If I set the wrong connection string, I get the following error in Console and Debug Output: 2024-04-30T08:11:32.8056928Z Unable to write batch of 1 log events to the database due to following error: Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) So it seems that errors from Serilog are being written, so I would expect error output when Serilog is unable to write to the column.

LouisMach commented 2 months ago

I also opened this stackoverflow thread for it: https://stackoverflow.com/questions/78408990/serilog-sql-server-sink-not-filling-custom-column-and-no-error-in-selflog

ckadluba commented 2 months ago

Hi @LouisMach!

Thank you for getting in touch. From a first look at your sample program, I could not see anything that looks obviously wrong. So I will try and debug it once I have some time for it.

I compared your sample to our samples/WorkerServiceDemo where we also use a custom column (AdditionalColumn1) and everything related to the custom column looks similar except that our sample has different names for the column and the property while yours does not. But that is probably not the cause.

Investigation continues.

LouisMach commented 2 months ago

Hi @ckadluba, thank you very much for your answer. I'm glad to hear you are investigating, I'd love to get this to work. I tried to include all relevant code in the samples but I could not share the whole project since it's proprietary. If you have any issues with reproducing this I will try to create a full sample app to reproduce the problem.

ckadluba commented 2 months ago

I have tried to get your code to compile but it seems to be incomplete (e.g. builder is used but not defined in Program.cs). So, can you please put together a simple but full sample project which I can use? That would be great.

P.S.: I modified our WorkerServiceDemo to test and rule out if the problem is caused by setting the column and property names differently, like in your sample. And as I assumed, this did not cause any trouble.