Dynamically Read event_file Data

In the previous article I covered the basics on extracting the payload for the events in a session from the event_file target. That article was a basic introduction.

In this article I want to take that basic knowledge and take it one step further. Being able to extract the data is fine and well. Being able to just do that task at the basic level is hardly sufficient, nor is it very efficient. I’ll explain that part in a little bit.

Pulling the data from the event_file target in the most basic of manners requires that one know the directory where the files are placed and the names of the files for the session. Granted, one should know this information if they are the one to have created the session. What if you didn’t create the session? What if you inherited the server with a bunch of sessions already deployed? Worse yet is the case of the magically changing file or location for the session (you know, somebody changed it and didn’t document the change).

There needs to be a more efficient means of grabbing the file without having to know the location. Knowing the session name should be adequate enough to parse the payload data from the target. This is exactly what I am going to show in this article – retrieving the file and path based strictly on the name of the session.

Dynamically Read event_file Data

I will take the basic example provided previously and use it as a starting point.

SELECT CONVERT(XML, t2.event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('C:DatabaseXEDemoSession*.xel',NULL,NULL,NULL) t2

Now that I have the basics in place, I need to see how I can get the path of the file based on the name of the session. Looking in sys.dm_xe_session_targets, a column called target_data is revealed. On closer inspection, it is apparent that this column contains xml data and part of that data involves the location of the event_file. With the first clue out of the way, I can build a query such as the following to help build my file path dynamically.

SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'demosession'

Clicking on the “hyperlink” XML in the target_data column will give me a result such as this:

<EventFileTarget truncated="0">
  <Buffers logged="0" dropped="0" />
  <File name="C:DatabaseXEDemoSession_0_130892666554200000.xel" />
</EventFileTarget>

This is good stuff so far. Now I need to be able to tie that into the previous “base” type query. This can be done beautifully thanks to the APPLY operator. Here is an example of that:

SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'demosession'
) cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name',
'varchar(1000)'),
NULL, NULL, NULL) t2

Knowing that I need to follow the path //EventFileTarget/File, I can take advantage of parsing the XML via the APPLY operator and then pass the node data to the next APPLY which is where I am calling the fn_xe_file_target_read_file function.

Running the query will yield a row for each event in the session should there be any events that have occurred since the session was last started.

Now that I have two stages of this thing built, it the next step is ridiculously easy. All that I have to do now is bring in the columns that I want to query from this session.

/* running session */SELECT event_data.value('(event/@name)[1]', 'varchar(50)') AS event_name
, event_data.value('(event/@timestamp)[1]', 'varchar(50)') AS [TIMESTAMP]
,event_data.value('(event/data[@name="collect_database_name"]/value)[1]','bit') AS collect_database_name
,event_data.value('(event/data[@name="database_id"]/value)[1]','int') AS database_id
,event_data.value('(event/data[@name="object_id"]/value)[1]','int') AS object_id
,event_data.value('(event/data[@name="index_id"]/value)[1]','int') AS index_id
,event_data.value('(event/data[@name="job_id"]/value)[1]','int') AS job_id
,event_data.value('(event/data[@name="job_type"]/value)[1]','varchar(max)') AS job_type
,event_data.value('(event/data[@name="status"]/value)[1]','varchar(max)') AS status
,event_data.value('(event/data[@name="duration"]/value)[1]','int') AS duration
,event_data.value('(event/data[@name="retries"]/value)[1]','int') AS retries
,event_data.value('(event/data[@name="success"]/value)[1]','bit') AS success
,event_data.value('(event/data[@name="last_error"]/value)[1]','int') AS last_error
,event_data.value('(event/data[@name="count"]/value)[1]','int') AS EventCount
,event_data.value('(event/data[@name="statistics_list"]/value)[1]','varchar(max)') AS statistics_list
,event_data.value('(event/data[@name="database_name"]/value)[1]','varchar(max)') AS database_name
FROM ( SELECT CONVERT(XML, t2.event_data) AS event_data
FROM ( SELECT target_data = CONVERT(XML, target_data)
FROM sys.dm_xe_session_targets t
INNER JOIN sys.dm_xe_sessions s
ON t.event_session_address = s.address
WHERE t.target_name = 'event_file'
AND s.name = 'demosession'
) cte1
CROSS APPLY cte1.target_data.nodes('//EventFileTarget/File') FileEvent ( FileTarget )
CROSS APPLY sys.fn_xe_file_target_read_file(FileEvent.FileTarget.value('@name',
'varchar(1000)'),
NULL, NULL, NULL) t2
) AS evts ( event_data )
ORDER BY [TIMESTAMP];

Great! I now no longer need to know exactly the path for the session in order to query the data in it. Despite that, I really should figure out what that path is and make sure I have it documented. I now want to draw attention to the comment made at the beginning of the last script. This particular script will only work when the session is running. Don’t despair, I will cover how to build this dynamically for any session that is not currently running. That will be covered in the next article where I discuss “Better Practices” for reading event_file data.

This has been another article in the 60 Days of XE series. If you have missed any of the articles, or just want a refresher, check out the TOC.