Xquery for Extended events

  • 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.

  • 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

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

  • 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

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

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