How To Implement Logging Using Serilog In ASP.NET Core 5.0 Application With Database

Satya Karki

7 Comments

Join the conversation! Your thoughts help the community grow.

Shalin Bhavsar3y ago
UserName and IP is not captured as extra column
Waleed Abukatab5y ago
Hi Satya karki , thanks for article please I have one question , I can use a DBeaver instead of an MSSqlserver
Alireza Shokoienia5y ago
Hi Satya, and thanks for execellent articel. I used it to log my events. Now I have an issue with to much undisred log messages in the table. I set the MinimumLevel as follows: "MinimumLevel": { "Default": "Information", "Override": { "System": "Warning", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Warning", "Microsoft.AspNetCore": "Warning" } . But most of log are of the following types: HTTP "GET" "/_blazor?id=j2RpTXLZXHWaZeQLXY36PA&_=1618382976986" or HTTP "POST" "/_blazor?id=yySHdcBtOsRzy9ksUYD40A", that are undisred. Can you help me to avoid logging of these messages? I mean that do you know which type of event must be added in MinimumLevel to avoid These "Information" messages? I don't know thier event sources.
- Satya Karki5y ago
  Hi Alireza, you can try this way MinimumLevel": { "Default": "Warning", "Override": { "System": "Warning", "Microsoft": "Warning", "Microsoft.Hosting.Lifetime": "Warning", "Microsoft.AspNetCore": "Warning" }
- Satya Karki5y ago
  Information captures several logs you can minimize logs by capturing Error and Warning.
Anjineyulu Maddineni5y ago
Hi Satya Karki overall article is very good I started serilog project in Asp.net core with PostGresSql but i faced some issues like Data not saved in Postgresql please tell me once
- Satya Karki5y ago
  Hi Anjineyulu, Thank you for your comment. I think, there may me issue in connection string or there may be serilog sink package version issue. Please check these two and still there is issue let me know.

Logging is one of the most crucial things in application development. It is fundamental and helps to troubleshoot any application issues. Like other .NET libraries we can implement Serilog in .NET application. Serilog provides diagnostic logging to files, the console, database and elsewhere.

In this article we will learn how to implement Serilog in ASP.NET Core 5.0 Web API Project and save the log in database.

Prerequisities

Visual Studio 2019
MSSQL Server

I have assumed that, you have already created ASP.NET Core 5.0 WebAPI project. Firstly, we need to install following packages to implement Serilog and capture Log in Database. Open Command line and run below command.

Install-Package Serilog.AspNetCore

Install-Package Serilog.Sinks.MSSqlServer

You can install it from Nugget Package Manager also.

To capture Log in database we have to create Table in our database. Create table in your database using following script.

CREATE TABLE [dbo].[Log](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Message] [nvarchar](max) NULL,
[MessageTemplate] [nvarchar](max) NULL,
[Level] [nvarchar](128) NULL,
[TimeStamp] [datetimeoffset](7) NOT NULL,
[Exception] [nvarchar](max) NULL,
[Properties] [xml] NULL,
[LogEvent] [nvarchar](max) NULL,
[UserName] [nvarchar](200) NULL,
[IP] [varchar](200) NULL,
CONSTRAINT [PK_Log] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

We need to add connection string in appsetting.json to save Log in database.

Sample appsetting.json code

{
"ConnectionStrings": {
"DemoSeriLogDB": "Data Source=yourDBServer;Initial Catalog=yourDatabaseName;Persist Security Info=True;User ID=yourDbUserId;Password=yourDBPassword;Pooling=False;MultipleActiveResultSets=False;Connect Timeout=60;TrustServerCertificate=False"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*",
}

Write the below code Program.cs file. This code helps to read Json files and we can get values from Json such as Connection string.

public static IConfiguration Configuration { get; } = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)
.Build();

Write the below code in Program.cs file Main function to get Connection string from Json file.

string connectionString = Configuration.GetConnectionString("DemoSeriLogDB");

Similarly, we need to write the below code in Program.cs file Main function to register or setting of Serilog sink that writes log events to a table in MSSQL Server database.

Additionally, through Column Options we can add custom Log data in the table such as User name, IP etc. In this example, I have added username only but we can add multiple columns also.

var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection<SqlColumn>
{
new SqlColumn("UserName", SqlDbType.VarChar)
}
}; //through this coulmnsOptions we can dynamically add custom columns which we want to add in database
Log.Logger = new LoggerConfiguration()
.Enrich.FromLogContext()
.WriteTo.MSSqlServer(connectionString, sinkOptions: new MSSqlServerSinkOptions { TableName = "Log" }
, null, null, LogEventLevel.Information, null, columnOptions: columnOptions, null, null)
.CreateLogger();

Sample Program.cs file code

public class Program
{
public static IConfiguration Configuration { get; } = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
.AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT") ?? "Production"}.json", optional: true)
.Build();
public static void Main(string[] args)
{
string connectionString = Configuration.GetConnectionString("DemoSeriLogDB");
var columnOptions = new ColumnOptions
{
AdditionalColumns = new Collection<SqlColumn>
{
new SqlColumn("UserName", SqlDbType.NVarChar)
}
};
Log.Logger = new LoggerConfiguration()
.Enrich.FromLogContext()
.WriteTo.MSSqlServer(connectionString, sinkOptions: new MSSqlServerSinkOptions { TableName = "Log" }
, null, null, LogEventLevel.Information, null, columnOptions: columnOptions, null, null)
.CreateLogger();
CreateHostBuilder(args).Build().Run();
}
public static IHostBuilder CreateHostBuilder(string[] args) =>
Host.CreateDefaultBuilder(args)
.ConfigureWebHostDefaults(webBuilder =>
{
webBuilder.UseStartup<Startup>();
}).UseSerilog();//serilog;
}

If we want to capture User name in our Log table then we have to write code to get user name in Configuration function of Startup.cs class below Authorization. So, write below line of code in Configuration function in Startup.cs class.

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
app.UseAuthorization();
// below code is needed to get User name for Log
app.Use(async (httpContext, next) =>
{
var userName = httpContext.User.Identity.IsAuthenticated ? httpContext.User.Identity.Name : "Guest"; //Gets user Name from user Identity
LogContext.PushProperty("Username", userName); //Push user in LogContext;
await next.Invoke();
}
);
}

In your WebAPI Control class, you can now call Serilog and Capture the log. You simply call Serilog in your Method/Function. You can write the below line of code to capture the Log.

Serilog.Log.Information("Started --Log Of Control Name-->Get Method");

To capture Error Log write the below code.

Serilog.Log.Error("Error: Log Of Control Name-->Get Method Error:", ex);

Sample WebAPI Controller code

public IEnumerable<WeatherForecast> Get()
{
Serilog.Log.Information("Started --Log Of Weather Forecast-->Get Method");
try
{
var rng = new Random();
return Enumerable.Range(1, 5).Select(index => new WeatherForecast
{
Date = DateTime.Now.AddDays(index),
TemperatureC = rng.Next(-20, 55),
Summary = Summaries[rng.Next(Summaries.Length)]
})
.ToArray();
}
catch (Exception ex)
{
Serilog.Log.Error("Error: Log Of Weather Forecast-->Get Method Error:", ex);
return null;
}
}

If you run the application your Log will be created. Go to database and you can see Log data are already available in Log table.

It captures all the Logging information. If you want to capture minimal information like, information and error, write the below line of code.

.MinimumLevel.Override("Microsoft", LogEventLevel.Error)//To capture Information and error only

So on we can capture Log in the database.

In this way, this article has described how to implement SeriLog implementation in ASP.NET Core 5.0 application. I hope this helps you to implement it in your project.