Sitecore Logging (Log4Net) Log to Database as Well as Text Files

Question

Sitecore 7.5

The text file logging is working fine. But, I would like the logging to go to a SQL Server database so that I can search the data with T-SQL.

How would I do that?

I imagine it would be a change to the web.config file inside the <log4net> block. And, also an additional connection string.

<log4net>
<!-- LOGGING SETTINGS
     The file element defines the location of the log files. This location must
     be the same as the setting in LogFolder. The file element is a relative or
     absolute path that always uses slashes (/) as separators. A valid file
     element for a relative path would be:

       <file value="/data/logs/log.{date}.{processid}.txt"/>

     A valid element for an absolute path would be:

       <file value="C:/inetpub/wwwroot/data/logs/log.{date}.{processid}.txt"/>

     The macros supported are:

       {date}: Replaced with the current date (in the format yyyyMMdd)
       {time}: Replaced with the current time (in the format HHmmss)
       {processid}: Replaced with the current Windows process id

     For further information refer to the Log4Net documentation.
-->
<appender name="LogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<appender name="WebDAVLogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/WebDAV.log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<appender name="SearchLogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/Search.log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<appender name="CrawlingLogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/Crawling.log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<appender name="PublishingLogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/Publishing.log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<appender name="NHibernateLogFileAppender" type="log4net.Appender.SitecoreLogFileAppender, Sitecore.Logging">
  <file value="$(dataFolder)/logs/NHibernate.log.{date}.txt" />
  <appendToFile value="true" />
  <layout type="log4net.Layout.PatternLayout">
    <conversionPattern value="%4t %d{ABSOLUTE} %-5p %m%n" />
  </layout>
  <encoding value="utf-8" />
</appender>
<root>
  <priority value="INFO" />
  <appender-ref ref="LogFileAppender" />
</root>
<logger name="Sitecore.Diagnostics.WebDAV" additivity="false">
  <level value="INFO" />
  <appender-ref ref="WebDAVLogFileAppender" />
</logger>
<logger name="Sitecore.Diagnostics.Search" additivity="false">
  <level value="INFO" />
  <appender-ref ref="SearchLogFileAppender" />
</logger>
<logger name="Sitecore.Diagnostics.Crawling" additivity="false">
  <level value="INFO" />
  <appender-ref ref="CrawlingLogFileAppender" />
</logger>
<logger name="Sitecore.Diagnostics.Publishing" additivity="false">
  <level value="INFO" />
  <appender-ref ref="PublishingLogFileAppender" />
</logger>
<logger name="NHibernate" additivity="false">
  <level value="WARN" />
  <appender-ref ref="NHibernateLogFileAppender" />
</logger>
<logger name="NHibernate.SQL" additivity="false">
  <level value="WARN" />
  <appender-ref ref="NHibernateLogFileAppender" />
</logger>
</log4net>

You could also give some consideration to taking this further and look into elastic.co/products/logstash then you can throw all logs into one searchable place; think IIS logs, event logs, Sitecore logs in one glorious searchable place — Steven Newstead, Commented Oct 28, 2016 at 14:13

G Killian · Accepted Answer · 2016-10-28 14:54:45Z

I talk about this in a series of posts (https://grantkillian.wordpress.com/2014/12/26/sitecore-logging-to-the-database-part-1/, https://grantkillian.wordpress.com/2014/12/26/sitecore-logging-to-the-database-part-2-earning-the-good-developer-badge/, and https://grantkillian.wordpress.com/2014/12/26/sitecore-logging-to-the-database-part-3-the-performance-picture/)

The other answers here are all good, but for any large Sitecore implementation I would encourage you to look at any performance implications and the asynchronous logging solution. If you don't want to dig into the async stuff, and last I looked there was some custom development needed to complete that properly for Sitecore, I'd recommend:

Use SQL Server stored procedure
Tune the Log4Net BufferSize setting
Consider data retention as log records could accumulate quickly: do you archive or remove the old data every month? Be sure to have proper SQL Server maintenance plans etc (https://ola.hallengren.com/) to keep the system optimal

Community · Accepted Answer · 2020-06-17 09:47:05Z

Yes. You need a new <appender>. First you need to set up a table to receive the logging information:

CREATE TABLE [dbo].[Log] (
    [Id] [int] IDENTITY (1, 1) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar] (255) NOT NULL,
    [Level] [varchar] (50) NOT NULL,
    [Logger] [varchar] (255) NOT NULL,
    [Message] [varchar] (4000) NOT NULL,
    [Exception] [varchar] (2000) NULL
)

Then set up the <appender>.

<appender name="AdoNetAppender" type="log4net.Appender.AdoNetAppender">
    <bufferSize value="100" />
    <connectionType value="System.Data.SqlClient.SqlConnection, System.Data, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <connectionString value="data source=[database server];initial catalog=[database name];integrated security=false;persist security info=True;User ID=[user];Password=[password]" />
    <commandText value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception]) VALUES (@log_date, @thread, @log_level, @logger, @message, @exception)" />
    <parameter>
        <parameterName value="@log_date" />
        <dbType value="DateTime" />
        <layout type="log4net.Layout.RawTimeStampLayout" />
    </parameter>
    <parameter>
        <parameterName value="@thread" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%thread" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@log_level" />
        <dbType value="String" />
        <size value="50" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%level" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@logger" />
        <dbType value="String" />
        <size value="255" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%logger" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@message" />
        <dbType value="String" />
        <size value="4000" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%message" />
        </layout>
    </parameter>
    <parameter>
        <parameterName value="@exception" />
        <dbType value="String" />
        <size value="2000" />
        <layout type="log4net.Layout.ExceptionLayout" />
    </parameter>
</appender>

All of this sourced from the original documentation for log4net.

Additional information

Any idea how to figure out what version of log4net is being used? — ADH, Commented Oct 28, 2016 at 20:33
That's actually a very good question. Normally I'd say "look at the version on log4net.dll" - but I can see that Sitecore has done some reimplementation and use Sitecore.Logging.dll. I'll try and find an answer for you. — Mark Cassidy, Commented Oct 28, 2016 at 20:36
Message: Could not load file or assembly 'log4net, Version=1.2.10.0, Culture=neutral, PublicKeyToken=1b44e1d426115821' or one of its dependencies. The system cannot find the file specified. — ADH, Commented Oct 28, 2016 at 21:15
I am going to mark this as the answer. But, I am having a lot of trouble getting this working. I will post more questions when I can. — ADH, Commented Oct 31, 2016 at 14:01
Sorry, changed the marked as answer. In my opinion, it's best not to start doing this at all. — ADH, Commented Oct 31, 2016 at 18:08

Ahmed Okour · Accepted Answer · 2016-10-28 14:24:12Z

You can start by creating a table for logging, Preferably in your own separate Database, Then you can update your Log4Net configurations to use the SQL appender, Alex Shyba article shows how you can do that, Basically you will need the following:

Create Your own Table for logging inside your own Database, Let say SitecoreSystem

CREATE TABLE [dbo].[Log] ( 
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NOT NULL ,  
[Thread] [varchar] (255) NOT NULL ,  
[Level] [varchar] (20) NOT NULL ,  
[Logger] [varchar] (255) NOT NULL ,  
[Message] [varchar] (4000) NOT NULL  
) ON [PRIMARY]

Create your SQL Appender in web.config (or in your Log4Net config):

<appender name="SQL_Appender" type="log4net.Appender.ADONetAppender, Sitecore.Logging">
   <param name="ConnectionType" value="System.Data.SqlClient.SqlConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
   <param name="ConnectionString" value="user id=***;password=***;Data Source=****;Database=SitecoreSystem" />
   <param name="CommandText" value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message]) VALUES (@log_date, @thread, @log_level, @logger, @message)" />
   <param name="Parameter">
      <param name="ParameterName" value="@log_date" />
      <param name="DbType" value="DateTime" />
      <param name="Layout" type="log4net.Layout.PatternLayout">
         <param name="ConversionPattern" value="%d{yyyy'-'MM'-'dd HH':'mm':'ss'.'fff}" />
      </param>
   </param>
   <param name="Parameter">
      <param name="ParameterName" value="@thread" />
      <param name="DbType" value="String" />
      <param name="Size" value="255" />
      <param name="Layout" type="log4net.Layout.PatternLayout">
         <param name="ConversionPattern" value="%t" />
      </param>
   </param>
   <param name="Parameter">
      <param name="ParameterName" value="@log_level" />
      <param name="DbType" value="String" />
      <param name="Size" value="50" />
      <param name="Layout" type="log4net.Layout.PatternLayout">
         <param name="ConversionPattern" value="%p" />
      </param>
   </param>
   <param name="Parameter">
      <param name="ParameterName" value="@logger" />
      <param name="DbType" value="String" />
      <param name="Size" value="255" />
      <param name="Layout" type="log4net.Layout.PatternLayout">
         <param name="ConversionPattern" value="%c" />
      </param>
   </param>
   <param name="Parameter">
      <param name="ParameterName" value="@message" />
      <param name="DbType" value="String" />
      <param name="Size" value="4000" />
      <param name="Layout" type="log4net.Layout.PatternLayout">
         <param name="ConversionPattern" value="%m" />
      </param>
   </param>
</appender>

In your Log4Net root section, switch the default appender to the new one:

<root>
  <priority value="INFO"/>
  <appender-ref ref="SQL_Appender" />  
  <!--<appender-ref ref="LogFileAppender"/>-->  
</root>

Stack Exchange Network

Sitecore Logging (Log4Net) Log to Database as Well as Text Files

3 Answers 3

Additional information

Your Answer

Not the answer you're looking for? Browse other questions tagged
sql-database
logging
log4net
or ask your own question.

Linked

Hot Network Questions

Sitecore Logging (Log4Net) Log to Database as Well as Text Files

3 Answers 3

Additional information

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged sql-databaselogginglog4net or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
sql-database
logging
log4net
or ask your own question.