ASP.NET Core is a popular framework for building web applications, and it includes built-in support for logging. However, the built-in logging providers may not be sufficient for all use cases. One popular alternative is Serilog, which provides additional features such as logging to a variety of destinations, including SQL Server.

In the previous article, we had an Introduction to Serilog in ASP.NET Core.

In this tutorial, we will learn how to set up Serilog in an ASP.NET Core application and configure it to write logs to a SQL Server database.

Setting up the Project

First, create a new ASP.NET Core Web Application using the template of your choice. Once the project is created, install the following NuGet packages:

Serilog
Serilog.AspNetCore
Serilog.Sinks.MSSqlServer

You can install these packages by running the following commands in the Package Manager Console:

Install-Package Serilog
Install-Package Serilog.AspNetCore
Install-Package Serilog.Sinks.MSSqlServer

Configuring Serilog

Next, we will configure Serilog to write logs to a SQL Server database. To do this, we will need to create a connection string that points to our SQL Server database. You can create a connection string by following these steps:

Open SQL Server Management Studio
Connect to your SQL Server instance
Right-click on the "Databases" node and select "New Database"
Give your new database a name, such as "LoggingDb"

Once you have created your database, you will need to create a table to store the logs. You can do this by running the following SQL script:

CREATE TABLE Logs (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Timestamp DATETIME NOT NULL,
    Level VARCHAR(50) NOT NULL,
    Message VARCHAR(MAX) NOT NULL,
    Exception VARCHAR(MAX) NULL
);

Now that the database and table are set up, you can create a connection string in the format of

"Data Source=<your server name>;Initial Catalog=<your database name>;Integrated Security=True;"

Once you have the connection string, you can configure Serilog to write logs to your SQL Server database by adding the following code to the Configure method in the Startup class:

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
    {
        // ...

        var connectionString = "<your connection string>";
        Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs")
            .CreateLogger();

        // ...
    }

This code configures Serilog to write logs with a minimum level of Information to a table named "Logs" in the SQL Server database specified by the connection string.

Writing Logs

Now that Serilog is configured to write logs to SQL Server, you can use the ILogger interface to write logs in your application. You can inject the ILogger interface into your controllers or services using dependency injection, and then use the various logging methods, such as LogInformation, LogError, etc., to write logs.

For example, in a controller you can use the ILogger interface like this:

public class HomeController : Controller
    {
        private readonly ILogger<HomeController> _logger;

        public HomeController(ILogger<HomeController> logger)
        {
            _logger = logger;
        }

        public IActionResult Index()
        {
            _logger.LogInformation("Home page accessed.");
            //...
        }
    }

You can also include additional information in the log by using structured logging. Here is an example of how to log an exception with additional details:

    try
    {
        // code that may throw an exception
    }
    catch (Exception ex)
    {
        _logger.LogError(ex, "An error occurred while processing a request.");
    }

You can also use the ForContext method to add additional properties to the log.

_logger.ForContext("RequestId", Request.HttpContext.TraceIdentifier)
        .LogError("An error occurred while processing a request.");

The log will contain the additional properties in the log message.

Automatically Create Logs Table without SQL

Serilog provides a feature called AutoCreateSqlTable which automatically creates the logging table in the SQL Server database if it doesn't already exist. This can be useful if you don't want to manually create the table or if you are deploying the application to multiple environments and don't want to manually create the table in each one.

To enable this feature, you can pass an additional parameter to the WriteTo.MSSqlServer method, like this:

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs", autoCreateSqlTable: true)
            .CreateLogger();

When this feature is enabled, Serilog will check if the table exists in the database when the application starts, and if it doesn't, it will create the table using the following script:

CREATE TABLE Logs (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    [Timestamp] DATETIME NOT NULL,
    [Level] NVARCHAR(128) NOT NULL,
    [Message] NVARCHAR(MAX) NOT NULL,
    [Properties] NVARCHAR(MAX) NULL,
    [Exception] NVARCHAR(MAX) NULL
);

It's worth noting that you need to make sure that the SQL Server user that your application is using has the appropriate permissions to create tables in the specified database.

Also, it's a good practice to make sure that your table structure match the data that you will log, as the script above will log all the fields that Serilog uses by default, but you can customize it to fit your needs.

Add Custom Columns to Logs Table

You can add custom columns to the logging table and log to them using the ForContext method to add additional properties to the log event.

First, you need to add the custom columns to the SQL table, you can do this by altering the table and adding the new columns. for example, let's say you want to add a UserId column:

ALTER TABLE Logs ADD UserId INT;

Then, in your code, you can use the ForContext method to add the UserId property to the log event.

_logger.ForContext("UserId", userId)
        .LogError("An error occurred while processing a request.");

Make sure that you are passing the correct value to the ForContext method, in this case userId should be the value of the current user or the user that generates the log event.

When Serilog writes the log event to the SQL Server table, it will include the UserId property in the Properties column as an XML string.

You can also use the Destructure.ToMaximumStringLength method to customize the maximum length of the string that will be stored in the Properties column, this is useful if you want to truncate long strings to save space in the database.

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs", autoCreateSqlTable: true, columnOptions: columnOptions => columnOptions.Destructure.ToMaximumStringLength(4000))
            .CreateLogger();

This will truncate any strings that exceed 4000 characters in length when they are stored in the Properties column.

Add Custom Columns to the Logs Table without SQL

To add custom columns to the Logs table without using SQL, you can use the ColumnOptions feature of the WriteTo.MSSqlServer method as described previously.

Here is an example of how to add multiple custom columns to the Logs table:

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs",
                autoCreateSqlTable: true,
                columnOptions: new ColumnOptions()
                {
                    AdditionalColumns = new Collection<SqlColumn>()
                    {
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserId", AllowNull = true },
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserName", AllowNull = true },
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserAgent", AllowNull = true }
                    }
                }
            )
            .CreateLogger();

This will add three new columns to the Logs table named UserId,UserName,UserAgent with data type NVARCHAR and allow null to the logs table.

Logging Data into Custom Column

Once you have added custom columns to the Logs table, you can use the ForContext method to add properties to the log message and store them in the custom columns.

Here is an example of how to log the current user's ID and name into UserId and UserName columns respectively :

_logger.ForContext("UserId", User.Identity.Name)
            .ForContext("UserName", "mbark")
            .LogInformation("Home page accessed.");

You can also use the PushProperty method to add properties to the log message and store them in the custom columns.

_logger.PushProperty("UserId", User.Identity.Name);
_logger.PushProperty("UserName", "mbark");
_logger.LogInformation("Home page accessed.");

It's worth noting that the property names in the ForContext and PushProperty methods should match the column names in the Logs table, otherwise the properties won't be stored in the correct columns.

You can also use the Enrich.FromLogContext method to automatically include the properties that you have pushed to the log message, this is useful if you want to add properties to the log message but you don't have them available when you are creating the log message.

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs",
                autoCreateSqlTable: true,
                columnOptions: new ColumnOptions()
                {
                    AdditionalColumns = new Collection<SqlColumn>()
                    {
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserId", AllowNull = true },
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserName", AllowNull = true },
                        new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "UserAgent", AllowNull = true }
                    }
                }
            )
            .Enrich.FromLogContext() // <==============
            .CreateLogger();

This will include all the properties that you have pushed to the log message and store them in the corresponding columns, you don't need to use ForContext or PushProperty methods in the log message, you can use them whenever you want and Enrich.FromLogContext will include them in the log message.

Filter Logs

The Filter method in Serilog allows you to filter logs based on specific conditions. You can use the Filter method to include or exclude certain log events based on certain conditions. This can be useful when you only want to log certain types of events or when you want to exclude certain types of events from the log.

The ByIncludingOnly method is used to include only events that meet a certain condition. For example, you can use the ByIncludingOnly method to only log events that have a specific property, like this:

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs", autoCreateSqlTable: true)
            .Filter.ByIncludingOnly(e => e.Properties.ContainsKey("UserId"))
            .CreateLogger();

This will only log events that have a "UserId" property, which can be useful when you want to log only specific events, such as logins or specific user actions.

You can also use the ByExcluding method to exclude certain log events based on a certain condition, like this:

Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Information()
            .WriteTo.MSSqlServer(connectionString, "Logs", autoCreateSqlTable: true)
            .Filter.ByExcluding(e => e.Properties.ContainsKey("Debug"))
            .CreateLogger();

This will exclude all the events that have a "Debug" property from the log. This can be useful when you want to exclude debug messages from the log.

Logging with Serilog and SQL Server

Table of contents