CODEWITHSUNDEEP
sql-server
Manjunath
Manjunath

Reputation: 381

How to read extended events .xel file

I have a certain request to read/parse the .xel file which is extended events file , How can I do this effectively and efficiently . One of my colleague advised to use API to parse the file , However I believe there should be a way that this can be achieved through SQL code itself . Help is much appreciated .

Upvotes: 18

Views: 36226

Answers (2)

Henrik Staun Poulsen
Henrik Staun Poulsen

Reputation: 13874

I had an XEL file from the Auditing system on Azure SQL Database, so I found this post. But it would not run with fn_xe_file_target_read_file, so I had to use fn_get_audit_file like this

select * from sys.fn_get_audit_file('c:\temp\08_11_22_921_16*.xel', null, null)

Upvotes: 2

Andrey Nikolov
Andrey Nikolov

Reputation: 13460

To read .xel files, you can use sys.fn_xe_file_target_read_file function. For example:

select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\Folder\MySession*.xel', null, null, null)

Further you may want to parse the returned XML to get the data in table format. To do this, you need to decide what data to extract from the XML and write the appropriate XPath expressions. For example:

-- You have to know element names and their data types
select
    n.value('(@name)[1]', 'varchar(50)') as event_name,
    n.value('(@package)[1]', 'varchar(50)') AS package_name,
    n.value('(@timestamp)[1]', 'datetime2') AS [utc_timestamp],
    n.value('(data[@name="duration"]/value)[1]', 'int') as duration,
    n.value('(data[@name="cpu_time"]/value)[1]', 'int') as cpu,
    n.value('(data[@name="physical_reads"]/value)[1]', 'int') as physical_reads,
    n.value('(data[@name="logical_reads"]/value)[1]', 'int') as logical_reads,
    n.value('(data[@name="writes"]/value)[1]', 'int') as writes,
    n.value('(data[@name="row_count"]/value)[1]', 'int') as row_count,
    n.value('(data[@name="last_row_count"]/value)[1]', 'int') as last_row_count,
    n.value('(data[@name="line_number"]/value)[1]', 'int') as line_number,
    n.value('(data[@name="offset"]/value)[1]', 'int') as offset,
    n.value('(data[@name="offset_end"]/value)[1]', 'int') as offset_end,
    n.value('(data[@name="statement"]/value)[1]', 'nvarchar(max)') as statement,
    n.value('(action[@name="database_name"]/value)[1]', 'nvarchar(128)') as database_name
from (select cast(event_data as XML) as event_data
from sys.fn_xe_file_target_read_file('D:\Folder\MySession*.xel', null, null, null)) ed
cross apply ed.event_data.nodes('event') as q(n)

Upvotes: 30

Related Questions