Having covered the metadata for a deployed session from just about every angle (yes there is more on metadata that could be discussed, has not yet been discussed, and that I am not planning on covering at this time), it is high-time to start figuring out how to query the payload that is desired to be trapped by the session that was deployed.

Early on in this series, I introduced the catalog views, DMVs and the supporting cast for Extended Events. In those articles introducing the supporting cast and views for XEvents, there is a critical player in this arena that was intentionally neglected. That player is the function that helps one to read the payload data that was captured via the session definition. That function is sys.fn_xe_file_target_read_file.

Functions and Files – an intro to Reading Event_File Target Data

The event_file target is an asynchronous consumer for Extended Events. This target stores the received payload in a proprietary binary format. Because of this, one needs to use the sys.fn_xe_file_target_read_file function. Using the function will then convert the data into a somewhat usable and more friendly format called XML.

With the session data being in a more human friendly form, a little bit more work needs to be done for that data to be really helpful to the data professional. To be able to get the results into a format more conducive to the consumption of most data professionals, one must use XQuery. This article is just going to focus on getting the data out of the event_file target and into the XML format. Working with the XML is a topic for another time.

The sys.fn_xe_file_target_read_file function takes a few parameters. The most important of these parameters are the first two parameters. And even then, that only applies to SQL Server 2008 and R2. Since SQL Server 2012, one really only needs to focus on the first parameter – path. That said, the file_offset can be an extremely useful field, especially under the circumstances where the session data is to be “warehoused” or a monitoring and alerting solution is to be built from Extended Events.

The path parameter is used to specify the on-disk path to the trace file that has been created. Not just the trace file, but all of the trace files associated to the session. A single file name (with path) can be specified here. Or a more common use would be to add a wildcard to the file name (with path) so all files could be included. If a wildcard is not used, then the initial_file_name parameter is pretty useless since only one file will be read anyway. If the wildcard is used, then the initial_file_name parameter can be used to determine the starting point for reading the session data.

The second parameter is mdpath and is only applicable to SQL Server 2008 and R2. This is to specify the path of the metadata file that would have been created along with the event file as a part of the session in those versions of SQL Server. If SQL Server 2012 is being used, then this parameter is unnecessary.

The initial_offset parameter helps instruct the function what to ignore and what to process when reading in the session data from the target. If storing all session data into a table (basically warehousing the data), this is an extremely helpful parameter. The use of this parameter would help the import process only import new data from the target. It would be a nightmare to import the same monitoring data every time the load process ran.

Using the same demosession session that I have used throughout the series, here is a basic example of how to retrieve that session data.

SELECT CONVERT(XML, t2.event_data) AS event_data
FROM sys.fn_xe_file_target_read_file('C:\Database\XE\DemoSession*.xel',NULL,NULL,NULL) t2

In this example, I am just pulling the session data straight back without any manipulation. I have CONVERTED the event_data to XML only as an exercise to make it easier to evaluate. If I did not convert the event_data, it would return an XML string in NVARCHAR format.

If I needed to be able to explore the data in a more friendly format, then I need to shred the XML such as I have done in the following example:

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 sys.fn_xe_file_target_read_file('C:\Database\XE\DemoSession*.xel',NULL,NULL,NULL) t2
) AS evts ( event_data )
ORDER BY [TIMESTAMP];

Using the function in each of these ways will work consistently and reliably. One major drawback is the need to know what the filepath is for the session in question. Even if the filepath was known at one time and saved in a script (such as the previous examples), it is not far-fetched to have another person to change the filepath and not notify anybody or update any of the scripts.

In the next article, I show a more robust means to access this target data from the files.

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.