Event types to Serilog events in MSSQL

Question

I am new to Serilog. I was just recently tasked with logging Serilog to our MSSQL database. I have been able to do that; however, I am having problems trying to assign event types to different Serilog events. It works when I try to save the event type information to a text file but not when writing to the database. I have read many different articles on this but I still must missing something. Any help would be greatly appreciated. I am using .NET Core 3.1. Here is what I have.

Here are the Nuget packages I have installed that relate to Serilog.

• Serilog.AspNetCore (v 3.2.0)
• Serilog.Enrichers.Environment (v 2.1.3)
• Serilog.Enrichers.Process (v 2.0.1)
• Serilog.Enrichers.Thread (v 3.1.0)
• Serilog.Settings.Configuration (v 3.1.0)
• Serilog.Sinks.MSSqlServer (v 5.5.1)
• Serilog.Sinks.RollingFile (v 3.3.0)
• MurmurHash-net-core (v 1.0.0)

Database table

USE [MyDatabase]
GO

/****** Object:  Table [dbo].[Logs]    Script Date: 8/17/2020 6:10:12 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Logs](
    [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,
 CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information"
    }
  },
  "ConnectionStrings": {
    "DataMart": "Data Source=.\\MSSQLSERVER2K16,53307;Initial Catalog=MyDatabase;Integrated Security=SSPI;MultipleActiveResultSets=True"
  },
  "Serilog": {
    "Using": [ "Serilog.Sinks.MSSqlServer" ],
    "MinimumLevel": {
      "Default": "Information",
      "Override": {
        "Microsoft": "Information",
        "System": "Information"
      }
    },
    "WriteTo": [
      {
        "Name": "RollingFile",
        "Args": {
          "pathFormat": "C:\\Temp\\Application-API-log-{Date}.txt",
          "outputTemplate": "{Timestamp:yyyy-MM-dd HH:mm:ss.fff zzz} [{EventType:x8} {Level:u3}] {Message:lj}{NewLine}{Exception}"
        }
      },
      {
        "Name": "MSSqlServer",
        "Args": {
          "connectionString": "Data Source=.\\MSSQLSERVER2k16,53307;Initial Catalog=MyDatabase;Integrated Security=SSPI;MultipleActiveResultSets=True",
          "schemaName": "dbo",
          "tableName": "Logs",
          "autoCreateSqlTable": false,
          "restrictedToMinimumLevel": "Information"
        }
      }
    ],
    "Properties": {
      "Application": "Application Api"
    }
  },
  "AllowedHosts": "*"
}

EventTypeEnricher.cs

class EventTypeEnricher : ILogEventEnricher
{
    public void Enrich(LogEvent logEvent, ILogEventPropertyFactory propertyFactory)
    {
        var murmur = MurmurHash.Create32();
        var bytes = Encoding.UTF8.GetBytes(logEvent.MessageTemplate.Text);
        var hash = murmur.ComputeHash(bytes);
        var numericHash = BitConverter.ToUInt32(hash, 0);
        var eventType = propertyFactory.CreateProperty("EventType", numericHash);
        logEvent.AddPropertyIfAbsent(eventType);
    }
}

Program.cs

public static 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)
    {
        Log.Logger = new LoggerConfiguration()
            .ReadFrom.Configuration(Configuration)
            .Enrich.With<EventTypeEnricher>()
            .Enrich.WithThreadId()
            .Enrich.WithProcessId()
            .Enrich.WithMachineName()
            .Enrich.WithEnvironmentUserName()
            .CreateLogger();

        Serilog.Debugging.SelfLog.Enable(msg =>
        {
            Debug.Print(msg);
            Debugger.Break();
        });

        try
        {
            Log.Information("Application version {Version} starting up", typeof(Program).Assembly.GetName().Version);
            BuildWebHost(args).Run();
        }
        catch (Exception ex)
        {
            Log.Fatal(ex, "Host terminated unexpectedly");
        }
        finally
        {
            Log.CloseAndFlush();
        }
    }

    public static IWebHost BuildWebHost(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
            .UseStartup<Startup>()
            .UseConfiguration(Configuration)
            .UseSerilog()
            .Build();
}

Answer 1

I think you're confusing the LogEvent, Properties and EventType columns. Before I begin, it's important to know that LogEvent is a confusing name, because that is also the name of the class used to write logs in serilog. Essentially when you write a log, a LogEvent object is created that contains all of the data (manually passed items like the message template and prop values, automatic info like the timestamp, and any additional information attached through enrichers) of the log. I'll use italics to denote the class.

• LogEvent column:
  • Stores log event property values as JSON. Typically you will use either this column or the XML-based Properties column, but not both.
  • The 'Properties' column is enabled by default, although you can easily switch it out with 'LogEvent' by editing the Standard Columns.
• EventType column/enricher:
  • This enricher simply hashes the message template to create an unique identifier you can use to look up logs of the same message template. The enricher only attaches the property to each LogEvent, it doesn't add that as a column to the table.

In order to hook up this new property you need to add the column to the sink's additional column options, via either:

• appsettings.json config
• fluent-interface builder (shown below).

var columnOpts = new ColumnOptions();
columnOpts.Store.Remove(StandardColumn.Properties ); // removes the xml serialization of log event properties
columnOpts.Store.Add(StandardColumn.LogEvent ); // add the json serialization of log event props
columnOpts.AdditionalColumns = new List<SqlColumn>() // tell the sink to create a column for the EventType
{
    new SqlColumn { DataType = SqlDbType.NVarChar, ColumnName = "EventType", AllowNull = true }
};

return new LoggerConfiguration()
    .MinimumLevel.Information()
    .Enrich.With<EventTypeEnricher>()
    .WriteTo.MSSqlServer(connectionString: connString, sinkOptions: sinkOpts, columnOptions: columnOpts, restrictedToMinimumLevel: LogEventLevel.Information )
    .CreateLogger();

The reason that you saw the EventType in the Properties column is because this column captures all data sent to the log event.

TL;DR The LogEvent/Properties columns store all data of the log event as serialized json/xml strings. If you want to see the EventType as its own column, you need to tell the sink to create this column.

Answer 2

There are a number of steps you can take in order to troubleshoot why your SQL Server sink is not working. The common ones are described in this answer below:

Serilog MSSQL Sink doesn't write logs to database

By default, any log event properties you include in your log statements will be saved to the XML Properties column or the JSON LogEvent column. But they can also be stored in their own individual columns via the AdditionalColumns collection

Custom Property Columns