How to use Serilog to push logs to SQL DB in XP

Question

Using Sitecore 10.2

I have installed Serilog and Serilog.Sinks.MSSqlServer nuget packages. After the Sitecore log file is created, I would like to create logs in the database also.

The debugger is not hitting the SendBuffer method.

Patch.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/" xmlns:security="http://www.sitecore.net/xmlconfig/security/">
  <sitecore role:require="Standalone or ContentDelivery or ContentManagement">
    <log4net>
      <appender name="SerilogAppender" type="Foundation.Logging.Serilog.SerilogAppender, Foundation.Logging" patch:after = "appender[@name='LogFileAppender']">
        <minimumlevel value="DEBUG" />        
        <layout type="log4net.Layout.PatternLayout" />
        <encoding value="utf-8" />
      </appender>
      <root>
        <appender-ref ref="SerilogAppender" patch:after = "*[@ref='LogFileAppender']"/>
      </root>
    </log4net>
  </sitecore>
</configuration>

The reason for using after is not to disturb the default functionality. I want the Serilog DB logging to execute, only after Sitecore default file logging is done.

SerilogAppender.cs

protected override void SendBuffer(LoggingEvent[] events)
{
            var logDB = Sitecore.Configuration.Settings.GetConnectionString("logs");
            var sinkOpts = new MSSqlServerSinkOptions();
            sinkOpts.TableName = "Logs";
            var columnOpts = new ColumnOptions();
            //columnOpts.Store.Remove(StandardColumn.Properties);
            columnOpts.Store.Add(StandardColumn.LogEvent);
            columnOpts.LogEvent.DataLength = 2048;
            columnOpts.PrimaryKey = columnOpts.TimeStamp;
            columnOpts.TimeStamp.NonClusteredIndex = true;

            using (var log = new LoggerConfiguration()
                .WriteTo.MSSqlServer(
                    connectionString: logDB,
                    sinkOptions: sinkOpts,
                    columnOptions: columnOpts
                ).CreateLogger())
            {
                foreach (var thisEvent in events)
                {
                    LogEvent(log, thisEvent);
                }
            }

        }

private void LogEvent(Logger log, LoggingEvent loggingEvent)
            {
                try
                {
                    StringBuilder message = new StringBuilder(string.Empty);
    
                    if (loggingEvent.Level == Level.DEBUG)
                    {
                        log.Debug(loggingEvent.RenderedMessage);
                    }
                    if (loggingEvent.Level == Level.INFO)
                    {
                        log.Information(loggingEvent.RenderedMessage);
                    }
                    if (loggingEvent.Level == Level.WARN)
                    {
                        log.Warning(loggingEvent.RenderedMessage);
                    }
                    if (loggingEvent.Level == Level.ERROR)
                    {
                        log.Error(loggingEvent.RenderedMessage);
                    }
                    if (loggingEvent.Level == Level.FATAL)
                    {
                        log.Fatal(loggingEvent.RenderedMessage);
                    }
                }
                catch (Exception ex)
                {
                    this.ErrorHandler.Error("Error occurred while logging the event.", ex);
                }
            }
            private LogEventLevel GetLogEventLevel()
            {
                var logEventLevel = LogEventLevel.Debug;
                switch (MinimumLevel.ToLower())
                {
                    case "debug":
                        logEventLevel = LogEventLevel.Debug;
                        break;
                    case "info":
                        logEventLevel = LogEventLevel.Information;
                        break;
                    case "warn":
                        logEventLevel = LogEventLevel.Warning;
                        break;
                    case "error":
                        logEventLevel = LogEventLevel.Error;
                        break;
                    case "fatal":
                        logEventLevel = LogEventLevel.Fatal;
                        break;
                }
    
                return logEventLevel;
            }

Controller.cs

public ActionResult RenderHeroBanner()
{
   try
   {
     Convert.ToInt32("asd");
   }
   catch (Exception ex)
   {
      Log.Error("MYAPP", ex, this);
   }
}

I'm expecting the debugger would hit the SendBuffer method after the Log.Error() code in controller, but it isn't happening.

Answer 1

If you see the below config and compare it with yours, you will find a difference i.e. you need to use patch:instead and you are using patch:after for the SerilogAppender. It should looks like below.

<appender-ref ref="SerilogAppender" patch:instead = "*[@ref='LogFileAppender']"/>

Here is the complete config file.

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/" xmlns:role="http://www.sitecore.net/xmlconfig/role/" xmlns:security="http://www.sitecore.net/xmlconfig/security/">
    <sitecore role:require="Standalone or ContentDelivery or ContentManagement">
        <log4net>
            <appender name="SerilogAppender" type="log4net.Appender.SerilogAppender, Foundation.SerilogAppender" patch:after = "appender[@name='LogFileAppender']">
                <minimumlevel value="DEBUG" />
                <apikey value="fz0IdNDO6IfPCY9ct9o5" />
                <seqhost value="http://localhost:5341" />
                <layout type="log4net.Layout.PatternLayout" />
                <encoding value="utf-8" />
            </appender>
            <root>
                <appender-ref ref="SerilogAppender" patch:instead = "*[@ref='LogFileAppender']"/>
            </root>
        </log4net>
  </sitecore>
</configuration>

Try changing it to patch:instead and see if it works for you.

Use the below link for reference.

https://himadritechblog.wordpress.com/2020/11/14/serilog-appender-for-sitecore-logging/