Asp.Net Core 2 Logging With Serilog And Microsoft SQL Server Sink

In this post, I’m going to describe how we can configure Serilog with Asp.Net Core 2 web and Api projects. I also configure Serilog to work with SQL Server database to store the logging information. Then I’ll write an extension method and use the built-in exception handling middleware to log exceptions in production environments. What I’m going to present here is a very simple approach to logging. It can mainly be used with small projects when things doesn’t get complicated. For more complex scenarios what you’ll need is what is called structured logging, maybe a combination of Serilog, ElasticSearch, Kibana. But for now Let’s see how we can setup logging for a small projects.

Installing and Configuring Serilog

The first step you need to take is to install these three packages:

Serilog.AspNetCore -Version 2.1.0

Serilog.Settings.Configuration -Version 2.5.0

Serilog.Sinks.MSSqlServer -Version 5.1.0

Next we need to add Serilog config to entry point of our project which is Program.cs, here’s what it’ll look like.

You could configure a lot of Serilog features here, but I choose to do it in appsettings.json file. Also there is a section with Serilog.Debugging.SelfLog that I’ve commented out. It is used to give information about Serilog in case it didn’t work as expected and something went wrong and we needed more info.

Next step is to setup how Serilog should communicate with our data store, we configure it using appsettings.json section.

Here I add a section called Serilog with MinimumLevel required to trigger logging set to Error. Other notable settings is table name which here is called Logs and autoCreateSqlTable which when set to true, it’s going to create a table if it doesn’t already exist. You can take a look at Serilog.Settings.Configuration in case you needed more info about controlling other aspect of Serilog configuration. There are other sinks which you can use, for example to store log to file etc. You can find the complete list here. The installation and configuration part is complete, now we have to use the logger. We can use it the normal way or we can do something different based on type of project. I’m going to explain my approach for logging in Api and web projects.

Logging In Asp.Net Core API Projects

One obvious way to log is to inject a logger in our controller and use it.

But another way to make things easier is to create a global exception handler. By doing that if the environment is set to production, the global exception handler capture the exception, log it, and show something generic to the user. On the other hand if it’s development, we as a developer see the error. Here’s what I use for this purpose.

Here I created an extension method to be use in Startup.cs. In UseWebApiExceptionHandler method on line 5, I got an instance of logger factory from DI container and passed it to HandleApiException method. In that method in like 16, I get the current exception object to be used to log info about the exception. On line 20 and 21 I create an instance of logger and log the relevant information. Finally on line 24 and 25 I set the status code to 500 and write a generic message to response body for user. Now we need to use the exception handling method in our startup.cs.

Now whenever we are in production environment and an error happened, the middleware logs the error to database and shows a generic message to user.

Logging In Asp.Net Core Web Projects

In previous section I showed how we can handle exception and logging in APIs, we do something similar for web projects, but with some small changes. The setup we use for Serilog is the same, the difference is how we use the exception handling middleware.

In above code we mostly use the same logic we used for API project, the only difference is instead of writing text to response body, we redirect the user to the appropriate page based on status code. Note that this middleware used only when exception happens, for other scenarios such as 404 NotFound error, we use UseStatusCodePagesWithRedirects as you can see below.

Also you may have both API and web pages in your project, which in this case you can use something like this.

Remember you log the unexpected exceptions automatically, but you still can log them manually, normally in the outer most layers of your application.

Summary

In this post I described how we can configure Serilog for our Asp.Net Core 2 application. We also saw how we can use the built-in mechanisms of Asp.Net Core 2 to make handling exceptions and logging easier. In case my explanations wasn’t enough and you need a sample project, I applied these changes to my sample projects which you can find here and here.

Share...
 

Hamid Mosalla

Hi, I'm Hamid Mosalla, I'm a software developer, indie cinema fan and a classical music aficionado. Here I write about my experiences mostly related to web development and .Net.

 

6 thoughts on “Asp.Net Core 2 Logging With Serilog And Microsoft SQL Server Sink

  1. Hello, Great article really helpful. I set it up in my code exactly the same but I don’t see any change or no errors are being logged. Do I have to do anything else than the code in this article?
    Thank for your time.

  2. Hi,

    Thanks for the article of Asp.Net Core 2 Logging With Serilog And Microsoft SQL Server Sink.

    1) How do we populate the LogEvent column?

    2) Do you know if there is a ‘retentionperiod’ property for the MSSqlServer sink? I know the rollingfile has retainedFileCountLimit.

Leave a Reply

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