Xquery for Extended events

Question

Post reply

Xquery for Extended events

pamozer

SSCarpal Tunnel

Points: 4943
More actions
February 25, 2014 at 11:38 am

#286181

I am trying to use xquery to get event data back from extended events. My xquery skills are horrible. I am trying to use some sample data from Grant Fritchey but I am getting null records back. Below is the xml - I just want to retrieve a distinct list of the client_hostname and client_app_name.
<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z"><data name="is_cached"><value>true</value></data><data name="is_dac"><value>false</value></data><data name="database_id"><value>7</value></data><data name="packet_size"><value>8000</value></data><data name="options"><value>2000002838f4010000000000</value></data><data name="options_text"><value><![CDATA[]]></value></data><data name="database_name"><value><![CDATA[]]></value></data><action name="client_app_name" package="sqlserver"><value><![CDATA[CustomerProvider]]></value></action><action name="client_hostname" package="sqlserver"><value><![CDATA[PDW-PROVIDER05]]></value></action><action name="database_name" package="sqlserver"><value><![CDATA[superbill_shared]]></value></action><action name="sql_text" package="sqlserver"><value><![CDATA[(@SQLConditionCustomerID int)SELECT * FROM Customer WHERE CustomerID = @SQLConditionCustomerID]]></value></action><action name="username" package="sqlserver"><value><![CDATA[dev]]></value></action></event>
The query I have that doesn't work is :
WITH xEvents AS
(SELECT object_name AS xEventName,
CAST (event_data AS xml) AS xEventData
FROM sys.fn_xe_file_target_read_file
('C:\LoginTrace\Shared_0*.xel', NULL, NULL, NULL))
SELECT distinct top 1000 xEventName,
xEventData.value('(/event/data[@action_name=''Client_APP_Name'']/value)[1]','varchar') Client_APP_Name,
xEventData.value('(/event/data[@action_name=''Client_Host_Name'']/value)[1]','varchar') Client_Host_Name
FROM xEvents
Any help would be greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Hunchback SSCrazy Points: 2649 More actions · Answer 1

Just keep in mind that the xml methods are case sensitive.

DECLARE @x xml = '<event name="login" package="sqlserver" timestamp="2014-02-19T23:53:54.299Z">

<data name="is_cached">

<value>true</value>

</data>

<data name="is_dac">

<value>false</value>

</data>

<data name="database_id">

<value>7</value>

</data>

<data name="packet_size">

<value>8000</value>

</data>

<data name="options">

<value>2000002838f4010000000000</value>

</data>

<data name="options_text">

<value />

</data>

<data name="database_name">

<value />

</data>

<action name="client_app_name" package="sqlserver">

<value>CustomerProvider</value>

</action>

<action name="client_hostname" package="sqlserver">

<value>PDW-PROVIDER05</value>

</action>

<action name="database_name" package="sqlserver">

<value>superbill_shared</value>

</action>

<action name="sql_text" package="sqlserver">

<value>(@SQLConditionCustomerID int)SELECT * FROM Customer WHERE CustomerID = @SQLConditionCustomerID</value>

</action>

<action name="username" package="sqlserver">

<value>dev</value>

</action>

</event>';

SELECT

xEventData.value('(/event/action[@name="client_app_name"]/value/text())[1]','varchar(128)') Client_APP_Name,

xEventData.value('(/event/action[@name="client_hostname"]/value/text())[1]','varchar(128)') Client_Host_Name

FROM

@x.nodes('.') AS T(xEventData);

GO

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 2

Thank you so much. I always forget about the case sensitive property of xml

Grant Fritchey SSC Guru Points: 398878 More actions · Answer 3

Yep, that one gets us all at one point or another.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning