File Logging And MS SQL Logging Using Serilog With ASP.NET Core 2.0

In this article, you will learn File Logging and MS SQL Logging using Serilog with ASP.NET Core 2.0.

Logging is a very critical and essential part of any software. It helps us in the investigation of the essence of problems. ASP.NET Core has built-in support for logging API's, which is able to work with various logging providers. Using these built-in providers, we can send application logs to one or more destinations and also, we can plug in third-party logging frameworks such as Serilog, Nlog, etc.
Serilog is a good logging framework and it is built with the structured log data in mind. It is a kind of serializer. Serilog determines the right representation when the properties are specified in log events. To use Serilog logging feature with ASP.NET core 2.0, we need to add the dependency of "Serilog.AspNetCore". Serilog provides a variety of sinks such as a file, MSSQL, Log4net, PostgreSQL, etc.
In this article, I will explain about Serilog File sink and MS SQL sink with core 2.0. After adding a respective package of Serilog, we need to add UseSerilog() to the web host builder in BuildWebHost().
  1. public static IWebHost BuildWebHost(string[] args) =>  
  2.     WebHost.CreateDefaultBuilder(args)  
  3.         .UseStartup<Startup>()  
  4.         .UseSerilog()  
  5.         .Build();  
Using Serilog File Sink
To write log data into the file, we need to use "Serilog.Sinks.File" dependency. It writes Serilog event to one or more file based on configurations. We can add these sinks by using either NuGet package manager or .NET CLI

Using Package Manager,
  1. PM> Install-Package Serilog.Sinks.File  
Using .NET CLI,
  1. > dotnet add package Serilog.Sinks.File  
To configure the sink in c# code, we need to call WriteTo.File method during logger configuration. The logger configuration needs to write in a program.Main
  1. Log.Logger = new LoggerConfiguration()  
  2.                 .MinimumLevel.Information()  
  3.                 .MinimumLevel.Override("SerilogDemo", LogEventLevel.Information)  
  4.                 .WriteTo.File("Logs/Example.txt")  
  5.                 .CreateLogger();   
By default, log file size lime is 1GB but it can be increased or removed using the fileSizeLimitBytes parameter of WriteTo.File method.
  1. .WriteTo.File("Logs/Example.txt", fileSizeLimitBytes: null)  
We can also create log file by year, month, day, hour, or minute by specify rollingInterval parameter to WriteTo.File method. In the following example, I have defined rollingInterval to RollingInterval.Day so it will create a new log file every day.
  1. .WriteTo.File("Logs/Example.txt", rollingInterval: RollingInterval.Day)  
Serilog retained the most recent 31 files by default for some reason. We can change or remove this limit using retainedFileCountLimit parameter. In the following example, I have assigned this parameter to null, so it removes the limitation of 31 log files.
  1. .WriteTo.File("Logs/Example.txt", rollingInterval: RollingInterval.Day, retainedFileCountLimit: null)  
Controlling Text file formatting

The File sink creates events in specific fix format by default. Following is the fixed format.

The file format uses outputTemplate parameter to WriteTo.File method. Following is the default format of output template.
  1. .WriteTo.File("Logs/Example.txt",  
  2.     outputTemplate: "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{Level:u3}] {Message:lj}{NewLine}{Exception}")  

I have made small changes in timestamp formatting to "dd-MMM-yyyy HH:mm: ss.fff zzz", it will show date time format accordingly in the log file
Shared log files

Only one process may be written to the log file at a time by default. Serilog sinks enable us to multi-process shared log files by setting shared parameter of WriteTo.File to true.
  1. .WriteTo.File("Logs/Example.txt", shared: true)  
We can also do the same configuration using JSON configuration. To use JSON configuration, we need to add/install package "Serilog.Settings.Configuration".
Instead of configuring the logger in code, we can read from JSON configuration file by calling ReadFrom.Configuration() method.
In our application, we can do configuration in appsettings.json file. Here, we need to specify the file sink assembly and required path format under the "Serilog" node.
The parameters which can be set through the Serilog-WriteTo-File keys are the method parameters accepted by the WriteTo.File() configuration method. In the following example, I have created appSetting.json file that contains Serilog file sink configuration. In file configuration, the text file is created under logs folder with customized output template.
  1. {  
  2.   "Serilog": {  
  3.     "Using": [ "Serilog.Sinks.File" ],  
  4.     "MinimumLevel""Information",  
  5.     "WriteTo": [  
  6.       {  
  7.         "Name""File",  
  8.         "Args": {  
  9.           "path""Logs\\Example.txt"// log file path  
  10.           "rollingInterval""Day"// Rolling Interval  
  11.           "outputTemplate""{Timestamp:dd-MMM-yyyy HH:mm:ss.fff zzz} [{Level:u3}] {Message:lj}{NewLine}{Exception}"  
  12.         }  
  13.       }  
  14.     ]  
  15.   }  
  16. }  
Before creating logger, we need to add this configuration file to ConfigurationBuilder object and The ConfigurationBuilder object is need to pass to ReadFrom.Configuration method of LoggerConfiguration.
  1. var configuration = new ConfigurationBuilder()  
  2. .AddJsonFile("appsettings.json")  
  3. .Build();  
  5. Log.Logger = new LoggerConfiguration()  
  6.     .ReadFrom.Configuration(configuration)  
  7.     .CreateLogger();  
Using Serilog MSSQL Sink
Serilog provides sink that writes events to the MS SQL Server. Currently, NoSQL is very famous due to providing more flexibility to store the different kinds of properties but sometimes it is easier to use existing MS SQL Server to log the events. The sink "Serilog.Sinks.MSSQLServer" will write the events data to SQL table. This sink will work with .NET framework 4.5 and .NET Standard 2.0. We can add these sinks by using either NuGet package manager or .NET CLI using Package Manager.
  1. PM> Install-Package Serilog.Sinks.MSSqlServer  
Using .NET CLI:
  1. > dotnet add package Serilog.Sinks.MSSqlServer  
To configure this sink in C# code, we need to call WriteTo.MSSQLServer method during logger configuration. The logger configuration needs to be written in a program.Main method. There are two minimum configuration requirements:
  1. var connectionString = @"Data Source=(local); Initial Catalog=Test;User ID=sa;Password=Passwd@12;";  
  2. var tableName = "Logs";  
  4. var columnOption = new ColumnOptions();  
  5. columnOption.Store.Remove(StandardColumn.MessageTemplate);  
  7. Log.Logger = new LoggerConfiguration()  
  8.                 .MinimumLevel.Information()  
  9.                 .MinimumLevel.Override("SerilogDemo", LogEventLevel.Information)  
  10.                 .WriteTo.MSSqlServer(connectionString, tableName, columnOptions: columnOption)  
  11.                 .CreateLogger();  
Following is table definition:
  1. CREATE TABLE [dbo].[Logs](  
  2.     [Id] [int] IDENTITY(1,1) NOT NULL,  
  3.     [Message] [nvarchar](maxNULL,  
  4.     [MessageTemplate] [nvarchar](maxNULL,  
  5.     [Level] [nvarchar](128) NULL,  
  6.     [TimeStamp] [datetimeoffset](7) NOT NULL,  
  7.     [Exception] [nvarchar](maxNULL,  
  8.     [Properties] [xml] NULL,  
  9.     [LogEvent] [nvarchar](maxNULL,  
  11. (  
  12.     [Id] ASC  
Following are the columns that can be used by this sink. We can modify the column name of database table using columnOptions.
  • StandardColumn.Message
  • StandardColumn.MessageTemplate
  • StandardColumn.Level
  • StandardColumn.TimeStamp
  • StandardColumn.Exception
  • StandardColumn.Properties
By default all the columns are inserted when this sink performs log operation. We can change this list by adding or removing the columns using columnOptions.
  1. //Remove the column  
  2. columnOptions.Store.Remove(StandardColumn.Properties);  
  4. //Add the column  
  5. columnOptions.Store.Add(StandardColumn.Exception);  
We can also add our own log event properties in logs table.They can be added by using AdditionalDataColumns property of columnOption. For example, I have added "OtherData" property to the logs table and also added to AdditionalDataColumns property.
By using following SQL query, we can add a column to logs table.
  1. Alter table logs add OtherData Varchar(50);  
By using the following code, we can added column to AdditionalDataColumns property.
  1. columnOption.AdditionalDataColumns = new Collection<DataColumn>  
  2.             {  
  3.                 new DataColumn {DataType = typeof (string), ColumnName = "OtherData"},  
  4.             };  
  7. Log.Logger = new LoggerConfiguration()  
  8.                 .MinimumLevel.Information()  
  9.                 .MinimumLevel.Override("SerilogDemo", LogEventLevel.Information)  
  10.                 .WriteTo.MSSqlServer(connectionString, tableName,  
  11.                         columnOptions: columnOption  
  13.                         )  
  14.                 .CreateLogger();  

The log event property OtherData will be added to the corresponding column upon logging. The property name must match a column name in logs table.
The value of an additional property can be set by using ForContext property of ILogger class. Following the example, I have to assign OtherData property to "Test Data".
  1. using Microsoft.AspNetCore.Mvc;  
  2. using Serilog;  
  4. namespace SerilogDemo.Controllers  
  5. {  
  6.     public class HomeController : Controller  
  7.     {  
  8.         public IActionResult Index()  
  9.         {  
  10.             Log.Logger.ForContext("OtherData""Test Data").Information("Index method called!!!");  
  11.             return View();  
  12.         }  
  13.     }  
  14. }  
We can do the same configuration using JSON file. In the following example, I have passed connection string and table name to appsetting.json file under "Serilog" node.
  1. {  
  2.   "Serilog": {  
  3.     "Using": [ "Serilog.Sinks.MSSqlServer" ],  
  4.     "MinimumLevel""Information",  
  5.     "WriteTo": [  
  6.       {  
  7.         "Name""MSSqlServer",  
  8.         "Args": {  
  9.           "connectionString""Data Source=(local); Initial Catalog=Test;User ID=sa;Password=Passwd@12;"// connection String  
  10.           "tableName""logs" // table name  
  11.         }  
  12.       }  
  13.     ]  
  14.   }  
  15. }  
Using LoggerConfiguration.ReadFrom.Configuration method, we can read our configuration file and our logger is working according to our configuration.
  1. var configuration = new ConfigurationBuilder()  
  2. .AddJsonFile("appsettings.json")  
  3. .Build();  
  5. Log.Logger = new LoggerConfiguration()  
  6.     .ReadFrom.Configuration(configuration)  
  7.     .CreateLogger();  
Serilog is a logging framework that has many built-in sinks available for structured log data. In this article, I have explained about Serilog File sink and MS SQL sink with ASP.NET Core 2.0.

You can view or download the source code from the following GitHub link: Serilog file Sink And Serilog MS SQL sink.