• Logging extended Properties to Azure SQL with Enterprise Library 6

    As written in my previous posts I’m currently playing around with migrating an application to MS Azure. This includes the migration from Enterprise Library 5 to Enterprise Library 6. And as it’s Azure, logging could not be done to the file system. I played around with semantic logging for a while, but unsuccessfully. So now I’ve just switched to Logging to Azure SQL. This does not require much changes in my code. Of course some configuration, but not comparable to the new semantic logging.

    Extended Properties

    Enterprise Library Logging block allows the usage of extended Properties. So next to category, priority, message etc. it’s possible to have additional attributes. For a web application this might contain e.g. the username, the session id, the IP address etc. Having a unique key like session id as extended property in every log message makes it awards easy to focus just on this single user session where an error appeared.

    By default, Enterprise library supports having the extended properties just within a string ‘FormattedMessage’. This formatted message could be set up in your web.config. But then you need to process a ‘LIKE’-Query later on so I tried to add the values into dedicated columns, making it easier to search later on. And easy to disable it in the stored procedure later on for performance reasons or else.

    So here is my solution how to split the extended properties of the logging application block into separate columns in Azure SQL.

    Update Database Table

    For this example I just use 2 extended properties: Session ID and Username. So I need to add the 2 new columns to the existing Logging Table. I just added the 2 columns ‘Username’ and ‘SessionID’, but here is the full structure:

    ALTER TABLE [dbo].[Log](
    [LogID] [int] IDENTITY(1,1) NOT NULL,
    [EventID] [int] NULL,
    [Priority] [int] NOT NULL,
    [Severity] [nvarchar](32) NOT NULL,
    [Title] [nvarchar](256) NOT NULL,
    [Timestamp] [datetime] NOT NULL,
    [MachineName] [nvarchar](32) NOT NULL,
    [AppDomainName] [nvarchar](512) NOT NULL,
    [ProcessID] [nvarchar](256) NOT NULL,
    [ProcessName] [nvarchar](512) NOT NULL,
    [ThreadName] [nvarchar](512) NULL,
    [Win32ThreadId] [nvarchar](128) NULL,
    [Message] [nvarchar](1500) NULL,
    [FormattedMessage] [ntext] NULL,
    [Username] [varchar](200) NULL,
    [SessionID] [varchar](50) NULL)

    Split multi values

    The extended properties are supplied by Enterprise Library within parameter @FormattedMessage as a string so I need to find a separator which would not appear in Session ID and Username. For this example I used an exclamation mark ! . So first I need to split the supplied @FormattedMessage into 2 values. Therefore I’ve implement the solution proposed at SqlServerCentral into the Logging Database on Azure SQL.

    CREATE FUNCTION [dbo].[fnSplitString]
    (
      @string NVARCHAR(MAX),
      @delimiter CHAR(1)
    )
      RETURNS @output TABLE(rowID INT,splitdata NVARCHAR(MAX)
    )
    BEGIN
      DECLARE @start INT, @end INT, @count INT
      SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) , @count = 1
      WHILE @start < LEN(@string) + 1
      BEGIN
        IF @end = 0
          SET @end = LEN(@string) + 1
        INSERT INTO @output (rowID,splitdata)
        VALUES(@count, SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)
        SET @count = @count + 1
      END
      RETURN
    END

    Update WriteLog Stored Procedure

    To split the extended properties, existing stored procedure WriteLog needs to be updated. So here is the full procedure even though only the block at the beginning and of course the parameters in the INSERT-statement have been added.

    ALTER PROCEDURE [dbo].[WriteLog]
    (
      @EventID int,
      @Priority int,
      @Severity nvarchar(32),
      @Title nvarchar(256),
      @Timestamp datetime,
      @MachineName nvarchar(32),
      @AppDomainName nvarchar(512),
      @ProcessID nvarchar(256),
      @ProcessName nvarchar(512),
      @ThreadName nvarchar(512),
      @Win32ThreadId nvarchar(128),
      @Message nvarchar(1500),
      @FormattedMessage nvarchar(max),
      @LogId int OUTPUT
    )
    AS
    BEGIN
      DECLARE @Username VARCHAR(200)
      DECLARE @SessionID VARCHAR(50)
      if @FormattedMessage is not null and len(@FormattedMessage) > 0
      BEGIN
        SELECT @Username = splitdata from [dbo].[fnSplitString] (@FormattedMessage, '!') Where rowID = 1
        SELECT @SessionID = splitdata from [dbo].[fnSplitString] (@FormattedMessage, '!') Where rowID = 2
      END
      ELSE
      BEGIN
        SET @Username = NULL
        SET @SessionID = NULL
      END
      INSERT INTO [Log] (EventID, Priority, Severity, Title, [Timestamp], MachineName, AppDomainName, ProcessID,
        ProcessName, ThreadName, Win32ThreadId, Message, FormattedMessage, Username, SessionID)
      VALUES (@EventID, @Priority, @Severity, @Title, @Timestamp, @MachineName, @AppDomainName, @ProcessID,
        @ProcessName, @ThreadName, @Win32ThreadId, @Message, @FormattedMessage, @Username, @SessionID)
      SET @LogID = @@IDENTITY
      RETURN @LogID
    END

    Update Text Formatter in web.config

    Finally we need to update the text formatter in the web.config. In current example, the formatted message should only contain our extended properties. Of course you might add more data here, but take care to update the process in the stored procedure accordingly then.

    <add template="{dictionary({value}!)}"
    type="Microsoft.Practices.EnterpriseLibrary.Logging.Formatters.TextFormatter,
    Microsoft.Practices.EnterpriseLibrary.Logging, Version=6.0.0.0,
    Culture=neutral, PublicKeyToken=31bf3856ad364e35" name="Database Text Formatter" />

    Hope this helps! Of course it would be possible to have it more dynamically so e.g. you only add new extended property and it’s added to a separated table etc. But I wanted to keep it simple to understand, maintain (e.g. disable process by just updating the stored procedure) and use (and also extend if you like).

Leave a comment

If you want to share your opinion, leave a comment.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>