Serilog filter Expression for SQL Server for different tables

Question

Serilog I need to log only Custom Column data into SQL Server with two different tables

Table1 -- > Information

RequestId|RequestName|Description|EventDate|Duration|

Table 2 --- > Debug

RequestId|Request|Response

How to do that config

Answer 1

The way to do this is by using the WriteTo.Conditional method. With this it is possible to easily set conditions for different sinks.

In your case first you would need to create the two tables in the database. Information and Debug.

Then in the Main method, you would configure the serilog. First set the connection string, the table names and all the columns for each table. For example:

        string conn = "myConnectionString";
        var informationSinkOpt = new MSSqlServerSinkOptions
        {
            TableName = "Information",
        };
        var InfoColumnOptionsSection = new ColumnOptions
        {
            AdditionalColumns = new Collection<SqlColumn>
            {
                new SqlColumn { ColumnName = "RequestId", DataType = System.Data.SqlDbType.UniqueIdentifier },
                new SqlColumn { ColumnName = "RequestName", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "Description", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "EventDate", DataType = System.Data.SqlDbType.DateTime2 },
                new SqlColumn { ColumnName = "Duration", DataType = System.Data.SqlDbType.Int }
            }
        };

        var debugSinkOpt = new MSSqlServerSinkOptions
        {
            TableName = "Debug",
        };
        var DebugColumnOptionsSection = new ColumnOptions
        {
            AdditionalColumns = new Collection<SqlColumn>
            {
                new SqlColumn { ColumnName = "RequestId", DataType = System.Data.SqlDbType.UniqueIdentifier },
                new SqlColumn { ColumnName = "Request", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "Response", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 }
            }
        };

You wrote that you need to log only custom column data, so then you need to remove the default columns which serilog adds automatically. You can create a method to do this, for example:

    private static void RemoveDefaultSerilogColumns(ColumnOptions columnOptions)
    {
        columnOptions.Store.Remove(StandardColumn.Id);
        columnOptions.Store.Remove(StandardColumn.LogEvent);
        columnOptions.Store.Remove(StandardColumn.Level);
        columnOptions.Store.Remove(StandardColumn.TimeStamp);
        columnOptions.Store.Remove(StandardColumn.Exception);
        columnOptions.Store.Remove(StandardColumn.Message);
        columnOptions.Store.Remove(StandardColumn.MessageTemplate);
        columnOptions.Store.Remove(StandardColumn.Properties);
    }

Then pass to this method both column options sections to remove the default columns:

        RemoveDefaultSerilogColumns(DebugColumnOptionsSection);
        RemoveDefaultSerilogColumns(InfoColumnOptionsSection);

Then you can configure serilog, with all these settings. You can use the first argument of the Conditional method to set the condition for what should be logged to the sink. For example:

         Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Debug()
            .WriteTo.Conditional(
                ev =>
                {
                    // We want to only log information level logs to this table
                    bool isInformation = ev.Level == LogEventLevel.Information;
                    if (isInformation) { return true; }
                    return false;
                },
                wt => wt.MSSqlServer(
                    connectionString: conn,
                    sinkOptions: informationSinkOpt,
                    columnOptions: InfoColumnOptionsSection)
            )
            .WriteTo.Conditional(
                ev => {
                    // We want to only log debug level logs to this table
                    bool isDebug = ev.Level == LogEventLevel.Debug;
                    if (isDebug) { return true; }
                    return false;
                },
                wt => wt.MSSqlServer(
                    connectionString: conn,
                    sinkOptions: debugSinkOpt,
                    columnOptions: DebugColumnOptionsSection)
            )
            .CreateLogger();

Then when logging, the information logs will go to the first table, and the debug logs to the second table. Here is an example of two log statements:

        Log.Information("{RequestId} {Description} {EventDate} {Duration} {RequestName}", Guid.NewGuid(), "lorem description", DateTime.Now, 100, "lorem Name");
        Log.Debug("{RequestId} {Request} {Response}", Guid.NewGuid(), "lorem Request", "lorem Response");

So putting it all together your Program class would look like this:

using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;
using Serilog;
using Serilog.Events;
using Serilog.Sinks.MSSqlServer;
using System.Collections.ObjectModel;

namespace myNamespace
{
public class Program
{
    public static void Main(string[] args)
    {
        string conn = "myConnectionString";
        var informationSinkOpt = new MSSqlServerSinkOptions
        {
            TableName = "Information",
        };
        var InfoColumnOptionsSection = new ColumnOptions
        {
            AdditionalColumns = new Collection<SqlColumn>
            {
                new SqlColumn { ColumnName = "RequestId", DataType = System.Data.SqlDbType.UniqueIdentifier },
                new SqlColumn { ColumnName = "RequestName", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "Description", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "EventDate", DataType = System.Data.SqlDbType.DateTime2 },
                new SqlColumn { ColumnName = "Duration", DataType = System.Data.SqlDbType.Int }
            }
        };

        var debugSinkOpt = new MSSqlServerSinkOptions
        {
            TableName = "Debug",
        };
        var DebugColumnOptionsSection = new ColumnOptions
        {
            AdditionalColumns = new Collection<SqlColumn>
            {
                new SqlColumn { ColumnName = "RequestId", DataType = System.Data.SqlDbType.UniqueIdentifier },
                new SqlColumn { ColumnName = "Request", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 },
                new SqlColumn { ColumnName = "Response", DataType = System.Data.SqlDbType.VarChar, DataLength = 50 }
            }
        };

        RemoveDefaultSerilogColumns(DebugColumnOptionsSection);
        RemoveDefaultSerilogColumns(InfoColumnOptionsSection);

        Log.Logger = new LoggerConfiguration()
            .MinimumLevel.Debug()
            .WriteTo.Conditional(
                ev =>
                {
                    // We want to only log information level logs to this table
                    bool isInformation = ev.Level == LogEventLevel.Information;
                    if (isInformation) { return true; }
                    return false;
                },
                wt => wt.MSSqlServer(
                    connectionString: conn,
                    sinkOptions: informationSinkOpt,
                    columnOptions: InfoColumnOptionsSection)
            )
            .WriteTo.Conditional(
                ev => {
                    // We want to only log debug level logs to this table
                    bool isDebug = ev.Level == LogEventLevel.Debug;
                    if (isDebug) { return true; }
                    return false;
                },
                wt => wt.MSSqlServer(
                    connectionString: conn,
                    sinkOptions: debugSinkOpt,
                    columnOptions: DebugColumnOptionsSection)
            )
            .CreateLogger();

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

    private static void RemoveDefaultSerilogColumns(ColumnOptions columnOptions)
    {
        columnOptions.Store.Remove(StandardColumn.Id);
        columnOptions.Store.Remove(StandardColumn.LogEvent);
        columnOptions.Store.Remove(StandardColumn.Level);
        columnOptions.Store.Remove(StandardColumn.TimeStamp);
        columnOptions.Store.Remove(StandardColumn.Exception);
        columnOptions.Store.Remove(StandardColumn.Message);
        columnOptions.Store.Remove(StandardColumn.MessageTemplate);
        columnOptions.Store.Remove(StandardColumn.Properties);
    }

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