sp_server_diagnostics - dynamic EVENTS parser

(deprecated - see newer post for a much faster parser with more info)

Here's part 2 - parsing the error logs out of sp_server_diagnostics.  Copy and paste the entire article; code overflows the column but is still copyable.

First, get the data. I use a global temp table for it, and I don't drop it until I'm done troubleshooting.
sp_server_diagnostics should run continuously and you should be able to pull it that way, but I can't remember from where.  Thus, I just run it for 5 seconds to populate the table.

Feel free to share, feel free to send me changes and enhancements.  FYI - if you have a lot of errors, the XML parsing is SLOW if you have a lot of errors.  This runs in under 20 seconds on a normal box, but on one of my trouble boxes it can take 2.5-5 minutes.  I have no doubt someone GOOD at XQuery can make this go considerably faster - and I'd love to know how.  If there are no errors in the results, nothing will be returned.

I hope this helps you troubleshoot SQL Server 2012.

--takes 5 seconds to run, only valid on 2012 servers
if object_id('tempdb..##SpServerDiagnosticsResult') is null 
BEGIN
CREATE TABLE ##SpServerDiagnosticsResult 
(
  rowId INT IDENTITY PRIMARY KEY,
  create_time DateTime,
  component_type varchar(128),
  component_name varchar(128),
  state int,
  state_desc varchar(20),
  data varchar(max)
)

INSERT INTO ##SpServerDiagnosticsResult
EXEC sys.sp_server_diagnostics
END

--SP_SERVER_DIAGNOSTICS Dynamic Event Parser V 1.11
--You may use this at will, you may share it provided this header remains.  
-- Written 2012 Michael Bourgon
--Commercial use prohibited without permission - if you charge money for using this, I want a cut.
-- If you're just running this as your job as a DBA, enjoy.
-- Due to the XML parsing this takes about 4 minutes to run, of which over half is the parsing to generate the selects
-- Please feel free to share, and feel free to send corrections or enhancements - thebakingdba.blogspot.com
-- Thanks to Marc_S on Stackoverflow for the help on parsing XML!
SET NOCOUNT ON 
DECLARE @events TABLE (id INT IDENTITY PRIMARY KEY, EventName VARCHAR(100))
DECLARE @sql NVARCHAR(max)
DECLARE @min int, @max INT, @eventtype VARCHAR(100)

--get a list of event types, then walk through each separately; columns won't match 
INSERT INTO @events (EventName)
select 
    DISTINCT EventName = Evt.value('(@name)[1]', 'varchar(100)')
FROM 
(
SELECT CAST(data AS XML) AS xml_data 
FROM ##SpServerDiagnosticsResult 
WHERE component_name = 'events'
)getlistofsubevents
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event') Tbl(Evt)

--Loop - for each event type, generate a SQL script for those columns
SELECT @min = MIN(id), @max = MAX(id) FROM @events
WHILE @min <= @max
BEGIN
SET @sql = NULL 
SELECT @eventtype = EventName FROM @events WHERE id = @min

--header for the query
SELECT @sql = N'select 
EventName = Evt.value(''(@name)[1]'', ''varchar(100)'')
,OriginalTime = Evt.value(''(@timestamp)[1]'', ''varchar(100)'')' + CHAR(10) + CHAR(9)

--meat of the query - get the data for each unique TYPE, if a normal value.
-- if the subdatatype is not a "normal" type, we assume we want a name/text/value
-- we use varchar(100) for that, rather than a separate CASE, for speed
-- SO, don't just add varchar(100) to this CASE without understanding why.
SELECT @sql = @sql + 
N' ,' + SubEventName + 
+ CASE SubDataType
when N'int' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bigint' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value'+ ')[1]'', ''' + SubDataType + ''')'  + CHAR(10) + CHAR(9)
WHEN N'unicode_string' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'uniqueidentifier' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'nvarchar(100)' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
WHEN N'bit' THEN N' = Evt.value(''(data[@name="' + SubEventName + '"]/value' + ')[1]'', ''' + SubDataType + ''')' + CHAR(10) + CHAR(9)
ELSE N' = isnull(Evt.value(''(data[@name="' + SubEventName + '"]/type/@name)[1]'', ''varchar(100)''),'''') + '' : ''
    + isnull(Evt.value(''(data[@name="' + SubEventName + '"]/text)[1]'', ''varchar(100)''),'''') + '' : ''
    + isnull(Evt.value(''(data[@name="' + SubEventName + '"]/value)[1]'', ''varchar(100)''),'''')' + CHAR(10) + CHAR(9)
end
FROM 
--break out each event type for the larger query; could just use nvarchar/varchar, but returning the right data type is cleaner
-- (and we need to know when it's a non-standard type for the name/text/value)
(select distinct
--EventName = Evt.value('(../@name)[1]', 'nvarchar(100)'), --disabled since it's not actually used
SubEventName = Evt.value('(@name)[1]', 'nvarchar(100)'),
SubDataType = CASE Evt.value('(type/@name)[1]', 'nvarchar(100)') 
WHEN 'int32' THEN N'int'
WHEN 'uint16' THEN N'int'
WHEN 'boolean' THEN N'bit'
WHEN 'unicode_string' THEN N'nvarchar(100)'
WHEN 'uint32' THEN N'bigint'
WHEN 'uint64' THEN N'nvarchar(100)'
WHEN 'guid' THEN N'uniqueidentifier'
WHEN 'ansi_string' THEN N'nvarchar(100)'
ELSE N'varchar(100)' END --if unknown, then probably name/text/value. 
FROM 
(
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult WHERE component_name = 'events'
)event_xml_record
CROSS APPLY xml_data.nodes('/events/session/RingBufferTarget/event/data') Tbl(Evt)
WHERE Evt.value('(../@name)[1]', 'varchar(100)') = @eventtype
)full_data_info

--and the footer for our query; might be able to do a dual CROSS APPLY, but this is more readable
SELECT @sql = @sql + N'
from (
SELECT CAST(data AS XML) AS xml_data FROM ##SpServerDiagnosticsResult WHERE component_name = ''events''
)spserverdiageventparser
CROSS APPLY xml_data.nodes(''/events/session/RingBufferTarget/event'') Tbl(Evt)
WHERE Evt.value(''(@name)[1]'', ''varchar(100)'') = ''' + @eventtype + ''''

EXEC sp_executesql @sql
-- PRINT @sql

SET @min = @min + 1
END