Serilog is not creating log SQL table when I run the application in .NET 5

user_12375751 0
Viewed 3020

I have the appsettings.json setup to create the log table automatically with "autoCreateSqlTable": true

However, I checked SQL but the table is nowhere to be found. I can't seem to find what is causing it to not create and log the information.

Program.cs :

public class Program
{
    public static void Main(string[] args)
    {
        IConfigurationRoot configuration = new ConfigurationBuilder()
            .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
            .Build();

        Log.Logger = new LoggerConfiguration()
            .ReadFrom.Configuration(configuration)
            .CreateLogger();

        CreateHostBuilder(args).Build().Run();
    }

    public static IHostBuilder CreateHostBuilder(string[] args) =>
        Host.CreateDefaultBuilder(args)
            .ConfigureWebHostDefaults(webBuilder =>
            {
                webBuilder.UseStartup<Startup>().UseSerilog();
            });
}

appsettings.json:

"Serilog": {
    "MinimumLevel": "Information",
    "WriteTo": [
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "****",
          "tableName": "Log",
          "autoCreateSqlTable": true
        }
      }
    ]
  },
serilog asp.net-core c#
4 Answers

My Project is based on .Net5.0 Core MVC and project has below nuget packages installed.

    <PackageReference Include="Serilog" Version="2.10.0" />
    <PackageReference Include="Serilog.AspNetCore" Version="4.1.0" />
    <PackageReference Include="Serilog.Sinks.MSSqlServer" Version="5.6.0" />

Below config and program.cs file works for me.

Make sure your Serilog in appsettings.json has this line "Using":["Serilog.Sinks.MSSqlServer"] as shown below:

"Serilog": {
    "Using": [ "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": "Information",
    "Override": {
      "Microsoft.EntityFrameworkCore.Database.Command": "Error",
      "Microsoft": "Error",
      "Microsoft.AspNetCore.Mvc": "Warnning"
    },
    "WriteTo": [         
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Server=dbserverhost.domain.com;Database=DBName; Trusted_Connection=True; MultipleActiveResultSets=true",
          "schemaName": "dbo",
          "tableName": "AppLogs",
          "autoCreateSqlTable": true
          //,
          //"columnOptionsSection": {
          //  "removeStandardColumns": [ "Properties" ]
          //  //,
          //  //"customColumns": [
          //  //  {
          //  //    "ColumnName": "EventType",
          //  //    "DataType": "int",
          //  //    "AllowNull": false
          //  //  },
          //  //  {
          //  //    "ColumnName": "Release",
          //  //    "DataType": "varchar",
          //  //    "DataLength": 32
          //  //  }
          //  //]
          //}
        }
        ,
        "restrictedToMinimumLevel":  "Warning"
      }
    ]
  }

My Program.cs file

using Serilog;
public class Program
    {
        public static void Main(string[] args)
        {


            var config = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json", optional: false, reloadOnChange: true)
                .Build();


            Log.Logger = new LoggerConfiguration()
                .ReadFrom.Configuration(config)
                //.WriteTo.File("Logs/log-.txt", rollingInterval: RollingInterval.Day)
                .CreateLogger();
            
            Log.Information("Hello {Name} from thread {ThreadId}", Environment.GetEnvironmentVariable("USERNAME"), Thread.CurrentThread.ManagedThreadId);

            

            var host = CreateHostBuilder(args).Build();

            using (var scope = host.Services.CreateScope())
            {
                var services = scope.ServiceProvider;
                var loggerFactory = services.GetRequiredService<ILoggerFactory>();
            }

            host.Run();

            // CreateHostBuilder(args).Build().Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .UseSerilog()
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
    }

Serilog Default Create Table, For more details click here

CREATE TABLE [Logs] (

   [Id] int IDENTITY(1,1) NOT NULL,
   [Message] nvarchar(max) NULL,
   [MessageTemplate] nvarchar(max) NULL,
   [Level] nvarchar(128) NULL,
   [TimeStamp] datetime NOT NULL,
   [Exception] nvarchar(max) NULL,
   [Properties] nvarchar(max) NULL

   CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED ([Id] ASC) 
);
user_54419720

Creating tables in SQL Server requires certain permissions that your user might not have and the sink might be unable to create the table due to permission error.

There are also other reasons that might be preventing the sink from even connecting to the database, such as a typo on the connection string, or incorrect user/password, etc.

Serilog sinks for logging are safe by default, and don't throw exceptions, so if you want to troubleshoot what's happening, you should look at Serilog's SelfLog

e.g.

Serilog.Debugging.SelfLog.Enable(msg => Console.WriteLine(msg));

This will print any errors from sinks in the Console (change to something else if you don't have a console app)

user_2116720

Please verify that you have database and schema created. In my case, when using Serilog.sinks.postgresql, database and schema would not be created automatically.

user_101516250

use script in https://gist.github.com/mivano/10429656 or set AutoCreateSqlTable=True

.WriteTo.MSSqlServer(
            connectionString: connectionString,
            sinkOptions: new Serilog.Sinks.MSSqlServer.MSSqlServerSinkOptions() { 
                TableName = "Logs", 
                SchemaName="dbo", 
                AutoCreateSqlTable=true 
            }
)
user_70257290
Related