Hello everyone,

I'm fairly new to DNN.  Recently my boss asked me to look into the EventLog of the production site and find the most common exceptions that were not being handled.  I was able to create a t-sql script to accomplish this, but the only solution I found was to use a cursor.  The problem is that I had to run the OpenXML command on every record.  Another issue I ran into was formatting the XML into one record.  Each XML blob came back as a table because of how the XML is formatted:

<LogProperties>
 <LogProperty>
  <PropertyName>EventQueue.ProcessMessage</PropertyName>
  <PropertyValue>Message Processing Failed</PropertyValue>
 </LogProperty>
    .. more properties
</LogProperties>

Does anyone have a solution that does not require a cursor?  Is anyone good with the OpenXML command and know how to get the results back in one record?

Below is the script.  Modify the select statements at the bottom for desired results.  Of course you could put this into a sproc and handle the results elsewere.

@exceptionTable TABLEDATETIME,NVARCHAR(1000),INT,NVARCHAR(1000),INT,NVARCHAR(1000),INT,NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000),UNIQUEIDENTIFIER,NVARCHAR(1000),NVARCHAR(1000),INT,INT,NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000),NVARCHAR(1000) @idoc INT @doc NVARCHAR(MAX) @createDate DATETIME db_cursor CURSOR FOR LogProperties, LogCreateDate EventLog LogTypeKey LIKE '%Exception' LogCreateDate > '2009-1-01' db_cursor NEXT FROM db_cursor INTO @doc, @createDate @@FETCH_STATUS = 0 EXEC sp_xml_preparedocument @idoc output, @doc;INSERT @exceptionTableSELECT ,MAX(CASE WHEN PropertyName = 'AssemblyVersion' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'PortalID' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'PortalName' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'UserID' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'UserName' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'ActiveTabID' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'ActiveTabName' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'RawURL' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'AbsoluteURL' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'AbsoluteURLReferrer' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'UserAgent' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'DefaultDataProvider' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'ExceptionGUID' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'InnerException' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'FileName' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'FileLineNumber' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'FileColumnNumber' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'Method' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'StackTrace' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'Message' THEN PropertyValue END),MAX(CASE WHEN PropertyName = 'AssemblyVersion' THEN PropertyValue END)FROM OPENXML (@idoc, '/LogProperties/LogProperty',2)WITH(nvarchar(1000),nvarchar(1000) )EXEC sp_xml_removedocument @idoc FETCH NEXT FROM db_cursor INTO @doc , @createDate db_cursor db_cursor * FROM @exceptionTableMAX(CreateDate), PortalName, ActiveTabName, ActiveTabID, RawURL, [Message], InnerException, COUNT(InnerException) as NumberOfOccurrances @exceptionTable BY , PortalName, ActiveTabName, ActiveTabId, RawURL, [Message] BY NumberOfOccurrances DESC

DECLARE

(

CreateDate

AssemblyVersion

PortalID

PortalName

UserID

UserName

ActiveTabID

ActiveTabName

RawURL

AbsoluteURL

AbsoluteURLReferrer

UserAgent

DefaultDataProvider

ExceptionGUID

InnerException

[FileName]

FileLineNumber

FileColumnNumber

Method

StackTrace

[Message]

Source

)

DECLARE

DECLARE

DECLARE

DECLARE

SELECT

FROM

WHERE

AND

OPEN

FETCH

WHILE

BEGIN

@createDate

PropertyName

PropertyValue

END

CLOSE

DEALLOCATE

SELECT

SELECT

FROM

GROUP

InnerException

ORDER