January 4, 2017 at 11:50 am
I am starting to learn to use Extended Events and it seems like it has a lot of very useful elements to it but there is a huge issue for me. It is stored in XML. Now, I loath XML but I can deal with it but my main issue is that you have to know the events in the extended events before you can query them.
So for a given event (that you may or may not have created) you have to manually parse the XML file and write a query like...
-- select for the evtsRollback event session
SELECT
xed.event_data.value('(data[@name="duration"]/text)[1]', 'varchar(25)') AS duration,
xed.event_data.value('(data[@name="transaction_state"]/text)[1]', 'varchar(25)') AS transaction_state,
xed.event_data.value('(data[@name="transaction_type"]/text)[1]', 'varchar(25)') AS transaction_type,
xed.event_data.value('(data[@name="transaction_id"]/text)[1]', 'varchar(25)') AS transaction_id,
xed.event_data.value('(data[@name="object_name"]/text)[1]', 'varchar(25)') AS objname,
xed.event_data.value('(data[@name="savepoint_name"]/text)[1]', 'varchar(25)') AS savepoint_name,
xed.event_data.value('(data[@name="session_id"]/text)[1]', 'varchar(25)') AS session_id,
xed.event_data.value('(data[@name="database_id"]/text)[1]', 'varchar(25)') AS database_id,
xed.event_data.value('(data[@name="sql_text"]/text)[1]', 'varchar(25)') AS sql_text
FROM #capture_ex
CROSS APPLY targetdata.nodes('//RingBufferTarget/event') AS xed (event_data);
Is there any way to parse the XML in SQL to get the list of nodes and then use that to create the query? Or am I just doing it wrong? Or is there a better way that I am not aware of? It seems to me this is a huge drawback to using this. Some of the events have complex XLM layouts that are very confusing. Do I have to become an expert in XML to be able to use this?
Thanks in advance!!
Jim
January 4, 2017 at 12:12 pm
yes there is Jim!
i had worked diligently on this same issue, and put together a pretty good version code base that creates a script by reading the metadata, to create views with all the xml shredding generated automatically.
Eventually, I'll finish an article I was building for this, but this script will generate a view definition for every Extended Event, with a target type of file. I'd need to regroup to build one for type ring buffer or anything else that was possible.
Important: it has a dependency of having master.dbo.DelimitedSplit function laying around in the master database , which is a bastard version of DelimitedSplit8K, but that takes varchar(max), and more than one char for the delimiter:
see if this helps you at all.
an example:
--#################################################################################################
-- ApplicationErrors
--#################################################################################################
IF OBJECT_ID('[dbo].[vwExtendedEvent_ApplicationErrors]') IS NOT NULL
DROP VIEW [dbo].[vwExtendedEvent_ApplicationErrors]
GO
CREATE VIEW vwExtendedEvent_ApplicationErrors AS
SELECT
n.value('(@timestamp)[1]', 'datetime2') AS [timestamp]
,n.value('(@name)[1]', 'varchar(128)') AS EventName
,n.value('(@package)[1]', 'varchar(128)') AS PackageName
,n.value('(@id)[1]', 'int') AS SessionIdentifier
,n.value('(@version)[1]', 'int') AS PackageVersion
-- data
,n.value('(data[@name="destination"]/value)[1]', 'varchar(max)') AS destination
,n.value('(data[@name="is_intercepted"]/value)[1]', 'bit') AS is_intercepted
,n.value('(data[@name="message"]/value)[1]', 'varchar(max)') AS message
,n.value('(data[@name="state"]/value)[1]', 'int') AS state
-- actions
,n.value('(action[@name="database_id"]/value)[1]', 'varchar(max)') AS database_id
,n.value('(action[@name="event_sequence"]/value)[1]', 'varchar(max)') AS event_sequence
,n.value('(action[@name="last_error"]/value)[1]', 'varchar(max)') AS last_error
,n.value('(action[@name="nt_username"]/value)[1]', 'varchar(max)') AS nt_username
,n.value('(action[@name="query_hash"]/value)[1]', 'varchar(max)') AS query_hash
,n.value('(action[@name="query_plan_hash"]/value)[1]', 'varchar(max)') AS query_plan_hash
FROM
(SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(N'ApplicationErrors*.xel',null, null, null)
) as tab
CROSS APPLY event_data.nodes('event') as q(n)
--#################################################################################################
GO
Lowell
January 4, 2017 at 12:26 pm
WOW!
That is exactly what I was looking for!!
Thank you so much!!!
Jim
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply