dynamically build an extractor for extended events xml
-
Back with server side traces, i would build a view for each trace that would extract the details out for me, and made it extremely queryable
I want to do the same with Extended Events, but because each item is saved as XML, it makes it harder.
it is important to note, i do not want to hardcode xml...i want to discover the events, and dynamically build the right script for any events defined in a EE's actions.
here's a solid setup: i'm capturing any errors greater than Level 14.
CREATE EVENT SESSION [ApplicationErrors] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(package0.event_sequence,
package0.last_error,
sqlserver.client_app_name,
sqlserver.client_hostname,
sqlserver.database_id,
sqlserver.database_name,
sqlserver.nt_username,
sqlserver.plan_handle,
sqlserver.query_hash,
sqlserver.query_plan_hash,
sqlserver.session_nt_username,
sqlserver.sql_text,
sqlserver.username)
WHERE ([package0].[greater_than_equal_int64]([severity],(14))
--AND [sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_hostname],N'SolarWinds')
)
)
ADD TARGET package0.event_file(SET filename=N'ApplicationErrors.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO
ALTER EVENT SESSION [ApplicationErrors] ON SERVER STATE = START
--ALTER EVENT SESSION [ApplicationErrors] ON SERVER STATE = STOP
so after a while, i'll get a few errors in the extended event, which i can see in raw form like this:
--details
SELECT * FROM sys.fn_xe_file_target_read_file(N'ApplicationErrors*.xel',null, null, null) fn
--details as xml
SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(N'ApplicationErrors*.xel',null, null, null) fn
An article from Jonathan Kehayias has an example, that I trivially adapted to match my specific Extended Event, and for the columns he defined, i get values:
--copied and adapted from http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/12/06/an-xevent-a-day-6-of-31-targets-week-asynchronous-file-target.aspx
SELECT
n.value('(@name)[1]', 'varchar(50)') AS event_name,
n.value('(@package)[1]', 'varchar(50)') AS package_name,
n.value('(@id)[1]', 'int') AS id,
n.value('(@version)[1]', 'int') AS version,
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
n.value('(@timestamp)[1]', 'datetime2')) AS [timestamp],
n.value('(data[@name="error"]/value)[1]', 'int') as error,
n.value('(data[@name="severity"]/value)[1]', 'int') as severity,
n.value('(data[@name="duration"]/value)[1]', 'int') as state,
n.value('(data[@name="user_defined"]/value)[1]', 'varchar(5)') as user_defined,
n.value('(data[@name="message"]/value)[1]', 'varchar(max)') as message
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)
so Jonathans Script gets values that exist in my EE, but I have additional ones defined, and I want to add them.
For the additional the columns i havedefined, i can see in the EE are clearly the ones i defined above; so where in the metadata is that collection of columns, so i can dynamically build a string to pull the columns like host_name, sql_handle, etc?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Quick thought, although not too efficient Jacob's xmlTable function could be what you are looking for.
😎
-
Lowell, why don't you use the API for that?
You can stream the event in (near) realtime if you have a session running, or you can post-process the event reading a XEL file.
I presented a session on this topic at several SQL Saturdays. You can find the presentation here: http://www.sqlsaturday.com/496/Sessions/Details.aspx?sid=42864
I understand that you may want to keep it in the T-SQL realm, but shredding XML is the worst thing you can spend your pricey SQL Server core licences on :-).
-- Gianluca Sartori
-
thanks for the info guys, i'm diging through the details now.
I've got a solid handle on what i was after now, a fresh set of eyes got me started.
I'll post my results and an article on it when i finish it, but i'm able to script the [data] and [action] items as they appear int he session definitions, but now i want a single coherent script to build it all together.
for example, here's how i'm getting the actions that were definied for a given extended events session:
--actions
;WITH MyCTE
AS
(
select
sess.name,
targz.target_name ,
actz.action_name
from sys.dm_xe_sessions sess
INNER JOIN sys.dm_xe_session_targets AS targz
ON sess.address = targz.event_session_address
AND targz.Target_name = 'event_file'
INNER JOIN sys.dm_xe_session_events evtz
ON targz.event_session_address = evtz.event_session_address
inner join sys.dm_xe_session_event_actions actz
ON targz.event_session_address = actz.event_session_address AND evtz.event_name = actz.event_name
WHERE sess.name = 'ApplicationErrors'
)
SELECT s.Colzs
FROM(SELECT
Colzs = STUFF((SELECT ',' + 'n.value(''(action[@name="' + action_name + '"]/value)[1]'', ''varchar(max)'') ' + CHAR(13) + CHAR(10)
FROM MyCTE
ORDER BY action_name
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,1,'')
) s
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply