Summer…I am on a blog posting spree :-)

This is a continuation of the sage about SQL logging for . As I blogged, you can easily have Sitecore log to a SQL database instead of a flat text file.

Now what if we take it one step forward and have Sitecore output more information than we had before, including Sitecore Context User, Sitecore Context Item Id and raw server URL?

Well, after some digging, here is the solution for you.

First of all, we need to add those columns to the actual table that we are going to use for storage.
Here is how your “Log” table could look like:

 [TABLE](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=TABLE&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [ID] [] (1,1) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [] [] [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [Thread] [](255) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [] [](20) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [Logger] [](255) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [Message] [](4000) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
 [] [](2000) [NULL](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
** [SCUser] [****](255) **[**NULL**](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
** [SCItemId] [****](38) ****,**[**NULL**](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
** [RawUrl] [****](255) **[**NULL**](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=NULL&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)
) [[PRIMARY](http://search.microsoft.com/default.asp?so=RECCNT&siteid=us%2Fdev&p=1&nq=NEW&qu=PRIMARY&IntlSearch=&boolean=PHRASE&ig=01&i=09&i=99)

Secondly, we need a custom version of the log4net.Appender.ADONetAppender class where we simply add the values of those 3 into the Properties collection.

I decided to do it only if my Context Site is resolved properly and the logging levels indicate either ERROR or FATAL levels:

using log4net.Appender;
using log4net.spi;  
  
namespace Project.Shell.System  
{  
 public class SitecoreDatabaseLogAppender : ADONetAppender  
 {  
 protected override void Append(LoggingEvent loggingEvent)  
 {  
 if (Sitecore.Context.Site != null &&  
 (loggingEvent.Level  Level.FATAL || loggingEvent.Level  Level.ERROR))  
 {  
 var properties = loggingEvent.Properties;  
  
 if (Sitecore.Context.User != null)  
 {  
 properties["scuser"] = Sitecore.Context.User.Name;  
 }  
  
 if (Sitecore.Context.Item != null)  
 {  
 properties["scitemid"] = Sitecore.Context.Item.ID.ToString();  
 }  
  
 properties["rawurl"] = Sitecore.Web.WebUtil.GetServerUrl() + Sitecore.Web.WebUtil.GetRawUrl();  
 }  
  
 base.Append(loggingEvent);  
 }  
 }  
}

Pretty simple, huh?

Afterwards, we just need to tweak our configuration a bit:

  1. Specify the reference to the new custom “SitecoreDatabaseAppender” class:

<appender name="ADONetAppender_SqlServer" type="Project.Shell.System.SitecoreDatabaseLogAppender, Project.Shell" >

  1. Extend CommandText value to include 3 new parameter we are adding:

<param name="CommandText" value="INSERT INTO Log ([Date],[Thread],[Level],[Logger],[Message],[Exception], [SCUser], [SCItemId], [RawUrl]) VALUES (@logdate, @thread, @loglevel, @logger, @message, @exception, @scuser, @scitemid, @rawurl)" />

  1. Define the way you want log4net parsing engine to process these parameters:

Same story here. Make sure that the value of the _size _parameter matches the database column size.

<param name="Parameter">

               <param name="ParameterName" value="@scuser" />

               <dbType value="String" />

               <size value="255" />

               <*layout type="Project.Shell.System.PropertyLayout,Project.Shell"*>

**                  <param name="PropertyName" value="scuser" />

               </layout>

</param>

<param name="Parameter">

               <param name="ParameterName" value="@scitemid" />

               <dbType value="String" />

               <size value="38" />

               <*layout type="Project.Shell.System.PropertyLayout,Project.Shell"*>

                  <param name="PropertyName" value="scitemid" />

               </layout>

</param>

<param name="Parameter">

               <param name="ParameterName" value="@rawurl" />

               <dbType value="String" />

               <size value="255" />

               <*layout type="Project.Shell.System.PropertyLayout,Project.Shell"*>

**                  <param name="PropertyName" value="rawurl" />

               </layout>

</param>

You may also notice that I am using a custom type for the layout parameter. The reason for that is that I could not find a proper way of extracting the properties appended by SitecoreDatabaseLogAppender with default layouts. So I created a custom one which is able to extract those values with ease.

  1. This means that you should also compile the following class:
using System;
using System.Text;  
using log4net.Layout;  
using log4net.spi;  
  
namespace BayNET.Shell.System  
{  
 public class PropertyLayout : LayoutSkeleton  
 {  
 // Fields  
 protected const int BUF_SIZE = 0x100;  
 private StringBuilder m_sbuf = new StringBuilder(0x100);  
 protected const int MAX_CAPACITY = 0x400;  
 private string m_propertyName;  
  
  
 public string PropertyName  
 {  
 get  
 {  
 return m_propertyName;  
 }  
 set  
 {  
 m_propertyName = value;  
 }  
 }  
  
 public override bool IgnoresException  
 {  
 get  
 {  
 return false;  
 }  
 }  
  
 public override void ActivateOptions()  
 {  
 }  
  
 public override string Format(LoggingEvent loggingEvent)  
 {  
 if (loggingEvent == null)  
 {  
 throw new ArgumentNullException("loggingEvent");  
 }  
 if (m_sbuf.Capacity > 0x400)  
 {  
 m_sbuf = new StringBuilder(0x100);  
 }  
 else  
 {  
 m_sbuf.Length = 0;  
 }  
  
 if (!String.IsNullOrEmpty(PropertyName))  
 {  
 m_sbuf.Append(loggingEvent.Properties[PropertyName]);  
 }  
 return m_sbuf.ToString();  
 }  
  
 }  
}  
  

That should be it. Now you can happily observe all these 3 parameters being written in the log when en error happens. Isn’t that sweet?
image