ASP.NET Core Logging with Serilog and SQL Server

Serilog is a great 3rd party library for structured logging in our ASP.NET core apps. Structured logging is key to producing easily readable and filterable logs.

Using SQL Server as a log destination, allows us to leverage the power of SQL querying for log filtering. It can be a good choice if our app is already using SQL Server.

So, how do we implement a Serilog SQL log in ASP.NET Core 2.0 …

First we need to bring in the following nuget packages:

Next up, we need to change how logging is setup in Program.cs. The 3 lines at the start of Main() tells the program to use Serilog as the logger and to read the configuration from appsettings.json.

Serilog can then be configured to use SQL Server as a destination in appSettings.json, along with the minimum logging level. We need to specify the connection string for the logging database and the table name to log the data into.

We can get Serilog to create our Log table automatically but let’s do this outselves so that we have control of our schema. For example, we want the Properties column to be based on the xml data type so that we can query it (serilog creates this as nvarchar).

Below is TSQL script to create the Log table:

We can then write to the log in our code, logging structured objects using {@object} in the message template. Below is a web API action method for getting and returning a record (a contact in this case). We log when the contact is fetched from the cache / database and also when the contact is set in the cache.

Now that Serilog and SQL Server are setup, wired together and we have some logging code, we should get our logs output to our SQL Server table.

Get contact log

Note that if we have problems getting the log to write to SQL Server, we can use Serilog.Debugging.SelfLog.Enable() in Program.Main() to surface errors from Serilog.

The Properties column contains useful additional information, including what we specified in our structured log after the contact is added to the cache is:
Get contact log properties

We can extract specific data in the Properties column using XQuery syntax. We can even filter on values in the Properties column.

For example, if we wanted to find when a particular contact was added to the cache, we could use something like:

XQuery

Pretty useful!

Recommended reading for building great ASP.NET Web APIs:

Share this:Share on RedditTweet about this on TwitterShare on LinkedInShare on Google+

Leave a Reply

Your email address will not be published. Required fields are marked *