Custom Db logger library using Serilog (ASP.NET Core 2.2)

Hello, long time ago since my last post, but I’m back and wanting to share more frecuent posts and be more active.
In this post I want to show you how we could create a custom logger that logs into the database. In this case we are going to use serilog (right now 2.8.0 (here you’ll see source code of different approaches) and we are going to create custom properties to add more detail.

Why Serilog?

It allows us to do structured logging (the way serilog organizes the data makes it easier to query over the information), we could log information and properties (more info here).

The Approach

This approach uses configuration from a configuration file and we are going to implement some stuff from code as well (configuration+code).
In case you want to check in depth this is a good introduction to understand how serilog uses entries from a configuration file.

Hands on

Let’s create a solution with 2 projects (web application project that will use a library). In the start page let’s create a blank solution

I’m going to call it aspnetCorelogging, we are going to create a web application to use our library

Add new project

I select ASP.NET Core Web Application

I’m gonna call it Abc.Web

Let’s select api to use a controller that the template creates for us

Let’s create a library to implement our custom logger.Add a new project then select Class library (.NET Core)

I’m going to call this library CLogger

Add 2 items, an Interface called Ilogger and a class Logger, we would have something like

The most basic way to log using serilog or any log is to send information classified by a particular log level (debug, information, warning, error, etc) more info.
So we are going to create a generic way to log information, in LogInformation method we are going to pass the log level, exception (optional) and data to log (properties).

void LogInformation(LogLevels eventLevel, string information, Exception ex = null, params object[] values);

We need a structure for log levels, add a new item a “code file” called LogLevels

The web application is going to use our custom library, the library is going to use serilog, let’s add the packages. In the package manager console (Tools menu, nuget package manager), select as default project Clogger

Install all these packages

1. Install-Package Serilog -Version 2.8.0
2. Install-Package Microsoft.Extensions.Configuration.Json
3. Install-Package Microsoft.Extensions.Configuration.EnvironmentVariables
4. Install-Package Serilog.Sinks.MSSqlServer

The first one is needed to use serilog, 2 and 3 to support configuration settings from file and code. The forth one is needed to connect to database (sql server, but you can use any other database engine or any other origin/target provider, more info).

Logger

Let’s implement Logger
First we are going to create a Configuration property that’s going to give us information using configuration file (reference required Microsoft.Extensions.Configuration; )

Let’s implement LogInformation

So we are going to get information from a configuration file let’s create appsettings.json json file

{
  "ConnectionStrings": {
    "LoggingDatabase": "SERVER=.\\SQLEXPRESS;Database=Logging;Trusted_Connection=True;ConnectRetryCount=0"
  },
  "CustomLoggingLevel": "Debug",
  "LoggingTable": "Logs"
}

Connection String is going to have the database connection where we are going to log
CustomLoggingLevel is the initial level where the library is going to log, lower level than that, is going to be ignored.
LoggingTable is the name of the table which stores the log data

Create a new database

Run this script to create the log table (based on documentation and this one)

  CREATE TABLE [Logs] (
   [Id] int IDENTITY(1,1) NOT NULL,
   [Source] nvarchar(500) NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetime NOT NULL,
   [Exception] nvarchar(max) NULL,
    -- store properties data as JSON (nvarchar) or XML
   --[Properties] nvarchar(max) NULL
   [Properties] xml NULL
   CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id] ASC)
);

With an additional column Source to show how you can add more detail to the logs.

Let’s go back to Logger class to explain what we are doing when logging information

From lines 20 to 22 we get information from our configuration file appsettings.json (this one be created in the application that uses our library)
In line 26 we get add additional columns to use the object when we instantiate LoggerConfiguration
LoggerConfiguration (lines 38-56, check this and this).
We are doing this.

Going back to line 26 in GetColumnsOptions method we are going to add an additional column called Source

I recommend to enable log over serilog object that we are going to create, it’s going to log information in the console when debugging (on Output window you will see the information.

Web App

Let’s add reference the library in the web solution. Right click on Dependencies then add reference.

Click on Projects select Clogger

Let’s edit appSettings.json in Abc.Web project. Add these config entries

In Startup.cs we need register the dependency

When calling api/values, we are logging some information (the last in parameter you’ll see that contains the properties you want log besides the information you are logging).

Serilog logs the properties that you added in a particular way (you should put your properties between brackets), let’s run the app and see how it logs in database

There’s a column Properties that store all the properties we wanted to include when logging, in this case we let the LoggerConfiguration to store properties as a XML string (we can query using XQuery), but it also supports JSON (we can query using JSON path) you could check more info here.

So as you can see Serilog offers a lot of features and different ways to log information into a target (cloud, txt, database, console, etc). I hope this post will be a starting point for you to create your own logger.

You can download the source from the repository.

Bonus:

Don’t forget before starting to code, play a heavy list of songs. I found this really cool soul rock song by accident, so add it to your list.
The Altons - Gotcha