querying extended event event file (xel)

the following select string will query an even file for the following sample xml

SELECT q.duration,q.database_id,FILE_ID,file_type,total_size_kb,size_change_kb,timestamp,name,file_name,username
FROM (
SELECT duration=e.event_data_XML.value(‘(//data[@name=”duration”]/value)[1]’,’int’)
, database_id=e.event_data_XML.value(‘(//data[@name=”database_id”]/value)[1]’,’int’)
, file_id=e.event_data_XML.value(‘(//data[@name=”file_id”]/value)[1]’,’int’)
, file_type=e.event_data_XML.value(‘(//data[@name=”file_type”]/value)[1]’,’varchar(max)’)
, total_size_kb=e.event_data_XML.value(‘(//data[@name=”total_size_kb”]/value)[1]’,’int’)
, size_change_kb=e.event_data_XML.value(‘(//data[@name=”size_change_kb”]/value)[1]’,’int’)
, file_name=e.event_data_XML.value(‘(//data[@name=”file_name”]/value)[1]’,’varchar(max)’)
, username=e.event_data_XML.value(‘(//action[@name=”username”]/value)[1]’,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]’,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘\\server_name\d$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\aaa_0_130385763224100000.xel’, NULL, NULL, NULL)

)e
)q

 

<event name=”database_file_size_change” package=”sqlserver” timestamp=”2014-03-06T12:00:04.561Z”>
<data name=”duration”>
<value>11111</value>
</data>
<data name=”database_id”>
<value>1</value>
</data>
<data name=”file_id”>
<value>1</value>
</data>
<data name=”file_type”>
<value>0</value>
<text>Data file</text>
</data>
<data name=”is_automatic”>
<value>true</value>
</data>
<data name=”total_size_kb”>
<value>111111</value>
</data>
<data name=”size_change_kb”>
<value>1111</value>
</data>
<data name=”file_name”>
<value>FILE_NAME</value>
</data>
<data name=”database_name”>
<value />
</data>
<action name=”username” package=”sqlserver”>
<value>USERNAME</value>
</action>
</event>

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: