7

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 – Steve Newstead Oct 28 '16 at 14:13
3

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:

  1. Use SQL Server stored procedure
  2. Tune the Log4Net BufferSize setting
  3. 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
8

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 Oct 28 '16 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 Oct 28 '16 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 Oct 28 '16 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 Oct 31 '16 at 14:01
  • Sorry, changed the marked as answer. In my opinion, it's best not to start doing this at all. – ADH Oct 31 '16 at 18:08
4

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:

  1. 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]
  1. 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>
  1. 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>

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

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