Log4net, Elmah: HowTo Select surrounding trace rows

HowTo Select surrounding trace rows from an Elmah table?

-- Print log rows surrounding @searchfor
DECLARE @searchfor VARCHAR(20) = '%My Message%'
DECLARE @noofrowstofetch INT = 15
/*
-- print latest 3 rows with wanted @searchfor
SELECT TOP 3 TimeUtc, [Sequence] FROM [dbo].[ELMAH_Error]
WHERE [Message] like @searchfor
order by [Sequence] desc
*/

-- find latest row with wanted @searchfor
DECLARE @sequenceno INT;
SELECT TOP 1 @sequenceno =[Sequence] FROM [dbo].[ELMAH_Error]
WHERE [Message] like @searchfor
ORDER BY [Sequence] DESC


-- print latest msg with wanted @searchfor
SELECT [Message] FROM [dbo].[ELMAH_Error]
WHERE [Sequence] = @sequenceno

-- Print log rows surrounding @searchfor 
SELECT [Sequence] 
      ,[TimeUtc]
      ,[Type]
      ,[Application]
      ,[Host]
      ,[Message]
	  FROM [dbo].[ELMAH_Error]
WHERE (@sequenceno-@noofrowstofetch-1) < [Sequence] and [Sequence] < (@sequenceno+@noofrowstofetch+1)
ORDER BY [Sequence] DESC

Write trace info to the log

using log4net;

namespace MyNamespace{
    public class MyClass{
        private static readonly ILog log = LogManager.GetLogger
            (MethodBase.GetCurrentMethod().DeclaringType);

        public MyMethod()        {
            log.Info("My Message");
        }
    }
}

Configuration

In our setup we are using Log4net with EPiServer. It is configured in \EPiServerLog.config

<?xml version="1.0" encoding="utf-8"?>
<log4net>
    <appender name="errorFileLogAppender" type="log4net.Appender.RollingFileAppender" >
        <!-- Consider moving the log files to a location outside the web application -->
        <file value="..\logs\App_Data\EPiServerErrors.log" />
        <encoding value="utf-8" />
        <staticLogFileName value="true"/>
        <datePattern value=".yyyyMMdd.'log'" />
        <rollingStyle value="Date" />
        <threshold value="Info" />
        <lockingModel type="log4net.Appender.FileAppender+MinimalLock" />
        <appendToFile value="true" />
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="%date [%thread] %level %logger: %message%n" />
        </layout>
    </appender>
    <appender name="outputDebugStringAppender" type="log4net.Appender.OutputDebugStringAppender" >
        <layout type="log4net.Layout.PatternLayout">
            <conversionPattern value="[%thread] %level %logger: %message%n" />
        </layout>
    </appender>
    <appender name="elmahAppender" type="IF.MyPages.Web.Business.ElmahAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [thread] %level %logger - %message%newline" />
      </layout>
    </appender>

    <!-- Reduce amount of debug logging -->
    <logger name="EPiServer.Core.OptimisticCache" additivity="false">
        <level value="Error" />
    </logger>
    <logger name="EPiServer.Core.ContentProvider" additivity="false">
        <level value="Error" />
    </logger>
    <logger name="EPiServer.Data.Dynamic.Providers.DbDataStoreProvider" additivity="false">
        <level value="Error" />
    </logger>
    <logger name="EPiServer.Data.Providers.SqlDatabaseHandler" additivity="false">
        <level value="Error" />
    </logger>
    <logger name="EPiServer.Data.Providers.ConnectionContext" additivity="false">
        <level value="Error" />
    </logger>
    <logger name="EPiServer.Events">
      <level value="Error" />
    </logger>
  <logger name="EPiServer.Events.Remote.RemoteEventsManager">
    <level value="Error" />
  </logger>
    <root>
        <!--Be careful, setting this value to All, Debug or Info will affect performance.-->
        <level value="Error" />

        <!--Enabled file logging-->
        <appender-ref ref="errorFileLogAppender" />
      
        <appender-ref ref="elmahAppender" />

      <!-- Use DebugView (Microsoft/SysInternals) and enable Capture Win32 + Capture Global Win32 (http://technet.microsoft.com/en-us/sysinternals/bb896647). -->
        <!--appender-ref ref="outputDebugStringAppender" /-->
    </root>
</log4net>

Log4net trace levels

  • ALL
  • DEBUG
  • INFO
  • WARN
  • ERROR
  • FATAL
  • OFF

To change trace level change <log4net><root><level> and <log4net><appender><treshold>. You just have to change root when you change back to “Error”, when not using trace.

Elmah

Elmah is configured in \Web.config:

<configuration>
  <configSections>
    <sectionGroup name="elmah">
      <section name="security" requirePermission="false" type="Elmah.SecuritySectionHandler, Elmah" />
      <section name="errorLog" requirePermission="false" type="Elmah.ErrorLogSectionHandler, Elmah" />
      <section name="errorMail" requirePermission="false" type="Elmah.ErrorMailSectionHandler, Elmah" />
      <section name="errorFilter" requirePermission="false" type="Elmah.ErrorFilterSectionHandler, Elmah" />
    </sectionGroup>
  </configSections>
  <elmah>
    <!--
        See http://code.google.com/p/elmah/wiki/SecuringErrorLogPages for 
        more information on remote access and securing ELMAH.
    -->
    <security allowRemoteAccess="true" />
    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="EPiServerDB" applicationName="MyPages" size="1999" />
  </elmah>
  <location path="elmah.axd" inheritInChildApplications="false">
    <system.web>
      <httpHandlers>
        <add verb="POST,GET,HEAD" path="elmah.axd" type="Elmah.ErrorLogPageFactory, Elmah" />
      </httpHandlers>
      <authorization>
        <allow roles="WebEditors, WebAdmins, Administrators" />
        <deny users="*" />
      </authorization>
    </system.web>
    <system.webServer>
      <handlers>
        <add name="ELMAH" verb="POST,GET,HEAD" path="elmah.axd" type="Elmah.ErrorLogPageFactory, Elmah" preCondition="integratedMode" />
      </handlers>
    </system.webServer>
  </location>
</configuration>

Elmah table

The Elmah logging table is created with

/****** Object:  Table [dbo].[ELMAH_Error]    Script Date: 19-02-2015 07:37:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ELMAH_Error](
	[ErrorId] [uniqueidentifier] NOT NULL,
	[Application] [nvarchar](60) NOT NULL,
	[Host] [nvarchar](50) NOT NULL,
	[Type] [nvarchar](100) NOT NULL,
	[Source] [nvarchar](60) NOT NULL,
	[Message] [nvarchar](500) NOT NULL,
	[User] [nvarchar](50) NOT NULL,
	[StatusCode] [int] NOT NULL,
	[TimeUtc] [datetime] NOT NULL,
	[Sequence] [int] IDENTITY(1,1) NOT NULL,
	[AllXml] [ntext] NOT NULL,
 CONSTRAINT [PK_ELMAH_Error] PRIMARY KEY NONCLUSTERED 
(
	[ErrorId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[ELMAH_Error] ADD  CONSTRAINT [DF_ELMAH_Error_ErrorId]  DEFAULT (newid()) FOR [ErrorId]
GO

Refs

The End

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: