C#•2y ago

❔ Why Serilog doesn't loggin into a MSSQL Database

Hi dear friends, I'm working on a solution that contains 5 projects, ( Core, IdentityCore, EFCore, CQRSCore and WEB), the WEB project is an ASP.NET Core 7 API project. Recently I tried to integrate the Serilog logger in the project. I created the following entity that represents a Log :

public class Log
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string Level { get; set; }
    public string Message { get; set; }
    public string Exception { get; set; }
    public string Properties { get; set; }
    public string? UserId { get; set; }

    public AppUser User { get; set; }
}

public class Log
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string Level { get; set; }
    public string Message { get; set; }
    public string Exception { get; set; }
    public string Properties { get; set; }
    public string? UserId { get; set; }

    public AppUser User { get; set; }
}

AppUser is a class that Inherits from IdentityUser. in the AppDbContext I add the Log as a DbSet<Log> Logs. And I configured in the Serilog in the Program.cs of the web project like this :

Serilog.Log.Logger = new LoggerConfiguration()
        .MinimumLevel.Override("Microsoft.AspNetCore", Serilog.Events.LogEventLevel.Warning)
        .Enrich.FromLogContext()
        .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs"
        }).CreateLogger();

Serilog.Log.Logger = new LoggerConfiguration()
        .MinimumLevel.Override("Microsoft.AspNetCore", Serilog.Events.LogEventLevel.Warning)
        .Enrich.FromLogContext()
        .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs"
        }).CreateLogger();

And to log from Controllers I use like the following example :

    [Authorize]
    [HttpGet]
    public async Task<ActionResult<IEnumerable<BranchGetModel>>> GetBranches()
    {
        var result = await _mediator.Send(new GetBranchesQuery());
        var mappedResult = _mapper.Map<IEnumerable<BranchGetModel>>(result);

        Log.ForContext("UserId", User.Identity.Name).Information($"User has requested all branches");

        return Ok(mappedResult);
    }

    [Authorize]
    [HttpGet]
    public async Task<ActionResult<IEnumerable<BranchGetModel>>> GetBranches()
    {
        var result = await _mediator.Send(new GetBranchesQuery());
        var mappedResult = _mapper.Map<IEnumerable<BranchGetModel>>(result);

        Log.ForContext("UserId", User.Identity.Name).Information($"User has requested all branches");

        return Ok(mappedResult);
    }

With all these and no logs stored in the DB, please from your experience how do I can solve this issue ?

36 Replies

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

no everything goes fine but no logs stored in the Logs table

Tvde1•2y ago

are the logs also emitted to a file/console? Maybe serilog logs why it cannot write to the DB

M B V R KOP•2y ago

From this expression:

  .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs"
        }).CreateLogger();

  .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs"
        }).CreateLogger();

I think I forced it to log in a database

Tvde1•2y ago

you can add additional sinks right? e.g. the console

M B V R KOP•2y ago

yeah But my goal is to log in a database

Tvde1•2y ago

yes but maybe the logs in the console will tell you why it's not able to write to the database and then you can remove that afterwards

M B V R KOP•2y ago

hmmm, I was thinking about this idea I will try it now and see @Tvde1 it loggs fine in the Console

M B V R KOP•2y ago

M B V R KOP•2y ago

IDK why it not loggs in Database

Tvde1•2y ago

hmm and it doesn't say in the console that db logging doesn't work?

M B V R KOP•2y ago

no Do you have any idea ?

Tvde1•2y ago

are there any interesting options in MSSqlServerSinkOptions? maybe on the DB you can see a log of connection attempts should serilog create the table in the DB or is it already created? Does it match the columns that serilog expects?

M B V R KOP•2y ago

I used SQL Server Profiler but I see only the used query to get Branches but no query related to `Logs'

M B V R KOP•2y ago

M B V R KOP•2y ago

Yeah there is an option I used the following configs but still nothing

        Serilog.Log.Logger = new LoggerConfiguration()
        .MinimumLevel.Override("Microsoft.AspNetCore", Serilog.Events.LogEventLevel.Warning)
        .MinimumLevel.Override("Microsoft.EntityFrameworkCore", Serilog.Events.LogEventLevel.Error)
        .MinimumLevel.Override("Microsoft.Hosting.Lifetime", Serilog.Events.LogEventLevel.Error)
        .Enrich.FromLogContext()
        .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs",
            AutoCreateSqlTable = true,//<=======================
            SchemaName = "dbo" //<===============
        })
        .WriteTo.Console()
        .WriteTo.Debug()
        .CreateLogger();

        Serilog.Log.Logger = new LoggerConfiguration()
        .MinimumLevel.Override("Microsoft.AspNetCore", Serilog.Events.LogEventLevel.Warning)
        .MinimumLevel.Override("Microsoft.EntityFrameworkCore", Serilog.Events.LogEventLevel.Error)
        .MinimumLevel.Override("Microsoft.Hosting.Lifetime", Serilog.Events.LogEventLevel.Error)
        .Enrich.FromLogContext()
        .WriteTo.MSSqlServer(configuration.GetConnectionString("DefaultConnection"), sinkOptions: new MSSqlServerSinkOptions()
        {
            TableName = "Logs",
            AutoCreateSqlTable = true,//<=======================
            SchemaName = "dbo" //<===============
        })
        .WriteTo.Console()
        .WriteTo.Debug()
        .CreateLogger();

Tvde1•2y ago

and the DefaultConnection is a valid connection string that can create tables?

M B V R KOP•2y ago

Yeah, it is the same Connection String I used with EF Core

Tvde1•2y ago

and by the time this code runs, the configuration already contains appsettings.json and user secrets etc you can verify this by Debug.WriteLine the configuration.GetConnectionString("DefaultConnection") at that point it has happened to me before where I used configuration before the configuration was actually filled with appsettings and user secrets

M B V R KOP•2y ago

The same with me I already worked before in a project with this method and it works but I used a different technique of configs

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

I actually not want to real log, I want to use Serilog for a simple Audit

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

I never had any experience with ElasticSearch/Kibana is they simple to implement and understood ?

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

so ElasticSearch/Kibana used to store Loggs ?

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

hmmm interesting, is it simple to implement it?

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

Yeah, but I want to practice ElasticSearch/Kibana as you said, I want to take this new experience too

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

I mean by simple is it simple to setup and register thier services (ElasticSearch/Kibana) and hsing them too, or it needs some architectural considerations or something like taht ?

Unknown User•2y ago

Message Not Public

M B V R KOP•2y ago

hmmm, looks interesting, I will take this experience after finishing the current issue with Serilog

Unknown User•2y ago

Message Not Public

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

Gaming

Programming

❔ Why Serilog doesn't loggin into a MSSQL Database