dotnetco.de

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).