none
Parsing Cluster Diagnostic files (SQLDiag) RRS feed

  • Question

  • On 1 SQL 2014 I started to receive cluster diagnostic files in the log folder way more often then before . I need the XQuery to parse the events, but I feel pretty novice to XQuery.

    from https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/view-and-read-failover-cluster-instance-diagnostics-log?view=sql-server-2014

    I ran specified query from the site (into a temp table first)

    SELECT 

    xml_data.value('(event/@name)[1]','varchar(max)') AS 'Name' 

    ,xml_data.value('(event/@package)[1]','varchar(max)') AS 'Package' 

    ,xml_data.value('(event/@timestamp)[1]','datetime') AS 'Time' 

    ,xml_data.value('(event/data[@name=''state'']/value)[1]','int') AS 'State' 

    ,xml_data.value('(event/data[@name=''state_desc'']/text)[1]','varchar(max)') AS 'State Description' 

    ,xml_data.value('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'Failure Conditions' 

    ,xml_data.value('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name' 

    ,xml_data.value('(event/data[@name=''instancename'']/value)[1]','varchar(max)') AS 'Instance Name' 

    ,xml_data.value('(event/data[@name=''creation time'']/value)[1]','datetime') AS 'Creation Time' 

    ,xml_data.value('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component' 

    ,xml_data.value('(event/data[@name=''data'']/value)[1]','varchar(max)') AS 'Data' 

    ,xml_data.value('(event/data[@name=''info'']/value)[1]','varchar(max)') AS 'Info'

    ,xml_data

    INTO #ServerStats 

    FROM 

     ( SELECT object_name AS 'event' 

      ,CONVERT(xml,event_data) AS 'xml_data' 

      FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLNODE1_MSSQLSERVER_SQLDIAG_0_129936003752530000.xel',NULL,NULL,NULL)  

    )  

    AS XEventData; 

    BUT I NEED TO DRILL DOWN INSIDE THE EVENTS COMPONENTS AND READ THE DATA NODES VALUES FROM XML

    --LOOKING AT THE xml_data which does not get parsed properly

    select * from #ServerStats where component = 'events' ORDER BY Time;

    Question: is there a hyperlink or blog that I can use for XQuery Cluster Diagnostic files for events? Also from where I can get the schema?

    Thank you,


    • Edited by HSever Tuesday, August 20, 2019 7:06 PM
    Tuesday, August 20, 2019 7:06 PM

Answers

  • Hi HServer,

    Please try the following in SSMS:

    -- DDL and sample data population, start
    DECLARE @mockTbl TABLE (ID INT PRIMARY KEY, xmlData XML);
    INSERT INTO @mockTbl
    VALUES
    (1, N'<event name="component_health_result" package="sql_cluster_resource" timestamp="2019-08-12T14:06:12.454Z">
    	<data name="state">
    		<value>0</value>
    	</data>
    	<data name="failure_condition_level">
    		<value>3</value>
    	</data>
    	<data name="creation_time">
    		<value>2019-08-12 10:06:12.453</value>
    	</data>
    	<data name="component_type">
    		<value>instance</value>
    	</data>
    	<data name="component">
    		<value>events</value>
    	</data>
    	<data name="state_desc">
    		<value>unknown</value>
    	</data>
    	<data name="data">
    		<value>
    			<events>
    				<session startTime="2019-07-13T01:55:50.300" droppedEvents="0" largestDroppedEvent="0">
    					<RingBufferTarget truncated="0" processingTime="3891" totalEventsProcessed="46481" eventCount="43" droppedCount="0" memoryUsed="24560">
    						<event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:46.283Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>1604342839</value>
    							</data>
    							<data name="opcode">
    								<type name="event_opcode" package="sqlos"/>
    								<value>0</value>
    								<text>Begin</text>
    							</data>
    							<data name="node_id">
    								<type name="uint16" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="scheduler">
    								<type name="uint32" package="package0"/>
    								<value>5</value>
    							</data>
    							<data name="worker">
    								<type name="ptr" package="package0"/>
    								<value>0x00000005238f8160</value>
    							</data>
    							<data name="yields">
    								<type name="uint64" package="package0"/>
    								<value>195936512</value>
    							</data>
    							<data name="worker_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="process_utilization">
    								<type name="uint32" package="package0"/>
    								<value>25</value>
    							</data>
    							<data name="system_idle">
    								<type name="uint32" package="package0"/>
    								<value>66</value>
    							</data>
    							<data name="user_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>100156250</value>
    							</data>
    							<data name="kernel_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="page_faults">
    								<type name="uint32" package="package0"/>
    								<value>12</value>
    							</data>
    							<data name="working_set_delta">
    								<type name="int64" package="package0"/>
    								<value>8192</value>
    							</data>
    							<data name="memory_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:51.298Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47382888</value>
    							</data>
    							<data name="opcode">
    								<type name="event_opcode" package="sqlos"/>
    								<value>1</value>
    								<text>End</text>
    							</data>
    							<data name="node_id">
    								<type name="uint16" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="scheduler">
    								<type name="uint32" package="package0"/>
    								<value>5</value>
    							</data>
    							<data name="worker">
    								<type name="ptr" package="package0"/>
    								<value>0x00000005238f8160</value>
    							</data>
    							<data name="yields">
    								<type name="uint64" package="package0"/>
    								<value>195936526</value>
    							</data>
    							<data name="worker_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="process_utilization">
    								<type name="uint32" package="package0"/>
    								<value>25</value>
    							</data>
    							<data name="system_idle">
    								<type name="uint32" package="package0"/>
    								<value>66</value>
    							</data>
    							<data name="user_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>100156250</value>
    							</data>
    							<data name="kernel_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="page_faults">
    								<type name="uint32" package="package0"/>
    								<value>12</value>
    							</data>
    							<data name="working_set_delta">
    								<type name="int64" package="package0"/>
    								<value>8192</value>
    							</data>
    							<data name="memory_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:47:51.170Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47264855</value>
    							</data>
    							<data name="session_id">
    								<type name="int16" package="package0"/>
    								<value>82</value>
    							</data>
    							<data name="error_code">
    								<type name="uint32" package="package0"/>
    								<value>1332</value>
    							</data>
    							<data name="api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSid</value>
    							</data>
    							<data name="calling_api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSidInternal</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:48:41.554Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47264855</value>
    							</data>
    							<data name="session_id">
    								<type name="int16" package="package0"/>
    								<value>82</value>
    							</data>
    							<data name="error_code">
    								<type name="uint32" package="package0"/>
    								<value>1332</value>
    							</data>
    							<data name="api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSid</value>
    							</data>
    							<data name="calling_api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSidInternal</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    					</RingBufferTarget>
    				</session>
    			</events>
    		</value>
    	</data>
    	<data name="instance_name">
    		<value>InstanceName</value>
    	</data>
    	<data name="node_name">
    		<value>ServerName</value>
    	</data>
    </event>');
    -- DDL and sample data population, end
    
    SELECT col.value('(@name)[1]','VARCHAR(100)') AS [eventName]
    	, col.value('(data[@name="timestamp"]/value/text())[1]','VARCHAR(100)') AS [timestamp]
    	, col.value('(data[@name="opcode"]/value/text())[1]','INT') AS [opcode]
    	, col.value('(data[@name="worker"]/value/text())[1]','VARCHAR(100)') AS [worker]
    FROM @mockTbl tbl
        CROSS APPLY tbl.[xmlData].nodes('/event/data/value/events/session/RingBufferTarget/event[@name="scheduler_monitor_non_yielding_ring_buffer_recorded"]') AS tab(col)
    

    Output:

    eventName	timestamp	opcode	worker
    scheduler_monitor_non_yielding_ring_buffer_recorded	1604342839	0	0x00000005238f8160
    scheduler_monitor_non_yielding_ring_buffer_recorded	47382888	1	0x00000005238f8160

    • Marked as answer by HSever Wednesday, August 21, 2019 4:41 PM
    Wednesday, August 21, 2019 2:30 PM

All replies

  • Hi HServer,

    Please ask a more specific question about XQuery.

    For example, I used a particular event: "connectivity_ring_buffer_recorded". All real data is residing inside different data elements. Each of them has a name attribute. Two meaningful elements are value and text.

    Useful link: sys.fn_xe_file_target_read_file (Transact-SQL)

    XML for event name="connectivity_ring_buffer_recorded" and XQuery:

    SELECT 
     c.value('(event/@name)[1]','varchar(50)') AS 'Name' 
    ,c.value('(event/@package)[1]','varchar(30)') AS 'Package' 
    ,c.value('(event/@timestamp)[1]','datetime') AS 'Time' 
    ,c.value('(event/data[@name="state"]/value/text())[1]','int') AS 'State' 
    ,c.value('(event/data[@name="connection_id"]/value/text())[1]','VARCHAR(36)') AS [connection_id] 
    ,c
    FROM 
     ( SELECT object_name AS 'event' 
      ,CAST(event_data AS XML) AS [c]
      FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\system_health_0_132107469568880000.xel',NULL,NULL,NULL)  
    )  
    AS XEventData; 

     
    <event name="connectivity_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-20T05:06:35.037Z">
      <data name="id">
        <value>0</value>
      </data>
      <data name="timestamp">
        <value>0</value>
      </data>
      <data name="type">
        <value>2</value>
        <text>LoginTimers</text>
      </data>
      <data name="source">
        <value>1</value>
        <text>TDS</text>
      </data>
      <data name="session_id">
        <value>0</value>
      </data>
      <data name="os_error">
        <value>10054</value>
      </data>
      <data name="sni_error">
        <value>0</value>
      </data>
      <data name="sni_consumer_error">
        <value>17830</value>
      </data>
      <data name="sni_provider">
        <value>7</value>
      </data>
      <data name="state">
        <value>11</value>
      </data>
      <data name="local_port">
        <value>1433</value>
      </data>
      <data name="remote_port">
        <value>30555</value>
      </data>
      <data name="tds_input_buffer_error">
        <value>10054</value>
      </data>
      <data name="tds_output_buffer_error">
        <value>0</value>
      </data>
      <data name="tds_input_buffer_bytes">
        <value>0</value>
      </data>
      <data name="tds_flags">
        <value>0x0000003e</value>
        <text>DisconnectDueToReadError, NetworkErrorFoundInInputStream, ErrorFoundBeforeLogin, SessionIsKilled, NormalDisconnect</text>
      </data>
      <data name="total_login_time_ms">
        <value>8</value>
      </data>
      <data name="login_task_enqueued_ms">
        <value>0</value>
      </data>
      <data name="network_writes_ms">
        <value>0</value>
      </data>
      <data name="network_reads_ms">
        <value>7</value>
      </data>
      <data name="ssl_processing_ms">
        <value>0</value>
      </data>
      <data name="ssl_net_reads_ms">
        <value>0</value>
      </data>
      <data name="ssl_net_writes_ms">
        <value>0</value>
      </data>
      <data name="ssl_secure_calls_ms">
        <value>0</value>
      </data>
      <data name="ssl_enqueue_ms">
        <value>0</value>
      </data>
      <data name="sspi_processing_ms">
        <value>0</value>
      </data>
      <data name="sspi_net_reads_ms">
        <value>0</value>
      </data>
      <data name="sspi_net_writes_ms">
        <value>0</value>
      </data>
      <data name="sspi_secure_calls_ms">
        <value>0</value>
      </data>
      <data name="sspi_enqueue_ms">
        <value>0</value>
      </data>
      <data name="login_trigger_and_resource_governor_processing_ms">
        <value>0</value>
      </data>
      <data name="find_login_ms">
        <value>0</value>
      </data>
      <data name="logon_triggers_ms">
        <value>0</value>
      </data>
      <data name="exec_classifier_ms">
        <value>0</value>
      </data>
      <data name="session_recover_ms">
        <value>0</value>
      </data>
      <data name="is_client">
        <value>false</value>
      </data>
      <data name="connection_id">
        <value>597C0B23-7ED8-4AA1-B495-02AFEB87D204</value>
      </data>
      <data name="connection_peer_id">
        <value>00000000-0000-0000-0000-000000000000</value>
      </data>
      <data name="local_host">
        <value>2601:583:200:21e8:6030:32e8:507c:42f1</value>
      </data>
      <data name="remote_host">
        <value>2601:583:200:21e8:6030:32e8:507c:42f1</value>
      </data>
      <data name="call_stack">
        <value>sqllang.dll+0x0000000000A1EF4F
    sqllang.dll+0x0000000000A2BFB3
    sqllang.dll+0x0000000000A2B060
    sqllang.dll+0x000000000001A641
    sqllang.dll+0x000000000001A7F3
    sqldk.dll+0x000000000000711D
    sqldk.dll+0x0000000000007688
    sqldk.dll+0x00000000000072FD
    sqldk.dll+0x00000000000240C8
    sqldk.dll+0x00000000000241C0
    sqldk.dll+0x0000000000023937
    sqldk.dll+0x0000000000023E89
    KERNEL32.DLL+0x0000000000017BD4
    ntdll.dll+0x000000000006CE71</value>
      </data>
    </event>

    Tuesday, August 20, 2019 7:42 PM
  • Hi Yitzak, I have used this Table valued function in my original select. 

    For beginning, is there any where that I can get the XML schema for the Cluster Diagnostic files? 

    Thank you,

    Tuesday, August 20, 2019 8:38 PM
  • Hi HServer,

    I am afraid, they are not documented.

    But as I showed, the XML structure is very similar for each event.

    You just need to open one single XML for a particular event and it is all yours.

    SELECT DISTINCT object_name FROM ... will give all the unique event types. Open them one by one.

    name attribute value defines what it is really. Please see below.

    <data name="timestamp">
        <value>0</value>
      </data>
      <data name="type">
        <value>2</value>
        <text>LoginTimers</text>
      </data>
    Tuesday, August 20, 2019 8:50 PM
  • OK, let's apply on a practical example. Below a simplified event XML (2 events of each : scheduler_monitor_non_yielding_ring_buffer_recorded and security_error_ring_buffer_recorded)

    I look for assistance with the XQuery to get for one event only (lets say scheduler_monitor_non_yielding_ring_buffer_recorded ) ex: 


    event                                                                       timestamp     opcode worker

    ======================================================================

    scheduler_monitor_non_yielding_ring_buffer_recorded 47264855    0          0x00000005238f8160

    scheduler_monitor_non_yielding_ring_buffer_recorded 47264855    0          0x00000005238f8160

    Shortened XML below:

    <event name="component_health_result" package="sql_cluster_resource" timestamp="2019-08-12T14:06:12.454Z">
      <data name="state">
        <value>0</value>
      </data>
      <data name="failure_condition_level">
        <value>3</value>
      </data>
      <data name="creation_time">
        <value>2019-08-12 10:06:12.453</value>
      </data>
      <data name="component_type">
        <value>instance</value>
      </data>
      <data name="component">
        <value>events</value>
      </data>
      <data name="state_desc">
        <value>unknown</value>
      </data>
      <data name="data">
        <value>
          <events>
            <session startTime="2019-07-13T01:55:50.300" droppedEvents="0" largestDroppedEvent="0">
              <RingBufferTarget truncated="0" processingTime="3891" totalEventsProcessed="46481" eventCount="43" droppedCount="0" memoryUsed="24560">
                <event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:46.283Z">
                  <data name="id">
                    <type name="uint32" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="timestamp">
                    <type name="uint64" package="package0" />
                    <value>1604342839</value>
                  </data>
                  <data name="opcode">
                    <type name="event_opcode" package="sqlos" />
                    <value>0</value>
                    <text>Begin</text>
                  </data>
                  <data name="node_id">
                    <type name="uint16" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="scheduler">
                    <type name="uint32" package="package0" />
                    <value>5</value>
                  </data>
                  <data name="worker">
                    <type name="ptr" package="package0" />
                    <value>0x00000005238f8160</value>
                  </data>
                  <data name="yields">
                    <type name="uint64" package="package0" />
                    <value>195936512</value>
                  </data>
                  <data name="worker_utilization">
                    <type name="uint32" package="package0" />
                    <value>99</value>
                  </data>
                  <data name="process_utilization">
                    <type name="uint32" package="package0" />
                    <value>25</value>
                  </data>
                  <data name="system_idle">
                    <type name="uint32" package="package0" />
                    <value>66</value>
                  </data>
                  <data name="user_mode_time">
                    <type name="uint64" package="package0" />
                    <value>100156250</value>
                  </data>
                  <data name="kernel_mode_time">
                    <type name="uint64" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="page_faults">
                    <type name="uint32" package="package0" />
                    <value>12</value>
                  </data>
                  <data name="working_set_delta">
                    <type name="int64" package="package0" />
                    <value>8192</value>
                  </data>
                  <data name="memory_utilization">
                    <type name="uint32" package="package0" />
                    <value>99</value>
                  </data>
                  <data name="call_stack">
                    <type name="callstack" package="package0" />
                    <value />
                  </data>
                </event>
                <event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:51.298Z">
                  <data name="id">
                    <type name="uint32" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="timestamp">
                    <type name="uint64" package="package0" />
                    <value>47382888</value>
                  </data>
                  <data name="opcode">
                    <type name="event_opcode" package="sqlos" />
                    <value>1</value>
                    <text>End</text>
                  </data>
                  <data name="node_id">
                    <type name="uint16" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="scheduler">
                    <type name="uint32" package="package0" />
                    <value>5</value>
                  </data>
                  <data name="worker">
                    <type name="ptr" package="package0" />
                    <value>0x00000005238f8160</value>
                  </data>
                  <data name="yields">
                    <type name="uint64" package="package0" />
                    <value>195936526</value>
                  </data>
                  <data name="worker_utilization">
                    <type name="uint32" package="package0" />
                    <value>99</value>
                  </data>
                  <data name="process_utilization">
                    <type name="uint32" package="package0" />
                    <value>25</value>
                  </data>
                  <data name="system_idle">
                    <type name="uint32" package="package0" />
                    <value>66</value>
                  </data>
                  <data name="user_mode_time">
                    <type name="uint64" package="package0" />
                    <value>100156250</value>
                  </data>
                  <data name="kernel_mode_time">
                    <type name="uint64" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="page_faults">
                    <type name="uint32" package="package0" />
                    <value>12</value>
                  </data>
                  <data name="working_set_delta">
                    <type name="int64" package="package0" />
                    <value>8192</value>
                  </data>
                  <data name="memory_utilization">
                    <type name="uint32" package="package0" />
                    <value>99</value>
                  </data>
                  <data name="call_stack">
                    <type name="callstack" package="package0" />
                    <value />
                  </data>
                </event>
                <event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:47:51.170Z">
                  <data name="id">
                    <type name="uint32" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="timestamp">
                    <type name="uint64" package="package0" />
                    <value>47264855</value>
                  </data>
                  <data name="session_id">
                    <type name="int16" package="package0" />
                    <value>82</value>
                  </data>
                  <data name="error_code">
                    <type name="uint32" package="package0" />
                    <value>1332</value>
                  </data>
                  <data name="api_name">
                    <type name="unicode_string" package="package0" />
                    <value>LookupAccountSid</value>
                  </data>
                  <data name="calling_api_name">
                    <type name="unicode_string" package="package0" />
                    <value>LookupAccountSidInternal</value>
                  </data>
                  <data name="call_stack">
                    <type name="callstack" package="package0" />
                    <value />
                  </data>
                </event>
                <event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:48:41.554Z">
                  <data name="id">
                    <type name="uint32" package="package0" />
                    <value>0</value>
                  </data>
                  <data name="timestamp">
                    <type name="uint64" package="package0" />
                    <value>47264855</value>
                  </data>
                  <data name="session_id">
                    <type name="int16" package="package0" />
                    <value>82</value>
                  </data>
                  <data name="error_code">
                    <type name="uint32" package="package0" />
                    <value>1332</value>
                  </data>
                  <data name="api_name">
                    <type name="unicode_string" package="package0" />
                    <value>LookupAccountSid</value>
                  </data>
                  <data name="calling_api_name">
                    <type name="unicode_string" package="package0" />
                    <value>LookupAccountSidInternal</value>
                  </data>
                  <data name="call_stack">
                    <type name="callstack" package="package0" />
                    <value />
                  </data>
                </event>
               </RingBufferTarget>
            </session>
          </events>
        </value>
      </data>
      <data name="instance_name">
        <value>InstanceName</value>
      </data>
      <data name="node_name">
        <value>ServerName</value>
      </data>
    </event>

    Thank you,

    Wednesday, August 21, 2019 1:55 PM
  • Hi HServer,

    Please try the following in SSMS:

    -- DDL and sample data population, start
    DECLARE @mockTbl TABLE (ID INT PRIMARY KEY, xmlData XML);
    INSERT INTO @mockTbl
    VALUES
    (1, N'<event name="component_health_result" package="sql_cluster_resource" timestamp="2019-08-12T14:06:12.454Z">
    	<data name="state">
    		<value>0</value>
    	</data>
    	<data name="failure_condition_level">
    		<value>3</value>
    	</data>
    	<data name="creation_time">
    		<value>2019-08-12 10:06:12.453</value>
    	</data>
    	<data name="component_type">
    		<value>instance</value>
    	</data>
    	<data name="component">
    		<value>events</value>
    	</data>
    	<data name="state_desc">
    		<value>unknown</value>
    	</data>
    	<data name="data">
    		<value>
    			<events>
    				<session startTime="2019-07-13T01:55:50.300" droppedEvents="0" largestDroppedEvent="0">
    					<RingBufferTarget truncated="0" processingTime="3891" totalEventsProcessed="46481" eventCount="43" droppedCount="0" memoryUsed="24560">
    						<event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:46.283Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>1604342839</value>
    							</data>
    							<data name="opcode">
    								<type name="event_opcode" package="sqlos"/>
    								<value>0</value>
    								<text>Begin</text>
    							</data>
    							<data name="node_id">
    								<type name="uint16" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="scheduler">
    								<type name="uint32" package="package0"/>
    								<value>5</value>
    							</data>
    							<data name="worker">
    								<type name="ptr" package="package0"/>
    								<value>0x00000005238f8160</value>
    							</data>
    							<data name="yields">
    								<type name="uint64" package="package0"/>
    								<value>195936512</value>
    							</data>
    							<data name="worker_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="process_utilization">
    								<type name="uint32" package="package0"/>
    								<value>25</value>
    							</data>
    							<data name="system_idle">
    								<type name="uint32" package="package0"/>
    								<value>66</value>
    							</data>
    							<data name="user_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>100156250</value>
    							</data>
    							<data name="kernel_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="page_faults">
    								<type name="uint32" package="package0"/>
    								<value>12</value>
    							</data>
    							<data name="working_set_delta">
    								<type name="int64" package="package0"/>
    								<value>8192</value>
    							</data>
    							<data name="memory_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="scheduler_monitor_non_yielding_ring_buffer_recorded" package="sqlos" timestamp="2019-07-31T13:42:51.298Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47382888</value>
    							</data>
    							<data name="opcode">
    								<type name="event_opcode" package="sqlos"/>
    								<value>1</value>
    								<text>End</text>
    							</data>
    							<data name="node_id">
    								<type name="uint16" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="scheduler">
    								<type name="uint32" package="package0"/>
    								<value>5</value>
    							</data>
    							<data name="worker">
    								<type name="ptr" package="package0"/>
    								<value>0x00000005238f8160</value>
    							</data>
    							<data name="yields">
    								<type name="uint64" package="package0"/>
    								<value>195936526</value>
    							</data>
    							<data name="worker_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="process_utilization">
    								<type name="uint32" package="package0"/>
    								<value>25</value>
    							</data>
    							<data name="system_idle">
    								<type name="uint32" package="package0"/>
    								<value>66</value>
    							</data>
    							<data name="user_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>100156250</value>
    							</data>
    							<data name="kernel_mode_time">
    								<type name="uint64" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="page_faults">
    								<type name="uint32" package="package0"/>
    								<value>12</value>
    							</data>
    							<data name="working_set_delta">
    								<type name="int64" package="package0"/>
    								<value>8192</value>
    							</data>
    							<data name="memory_utilization">
    								<type name="uint32" package="package0"/>
    								<value>99</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:47:51.170Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47264855</value>
    							</data>
    							<data name="session_id">
    								<type name="int16" package="package0"/>
    								<value>82</value>
    							</data>
    							<data name="error_code">
    								<type name="uint32" package="package0"/>
    								<value>1332</value>
    							</data>
    							<data name="api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSid</value>
    							</data>
    							<data name="calling_api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSidInternal</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    						<event name="security_error_ring_buffer_recorded" package="sqlserver" timestamp="2019-08-08T19:48:41.554Z">
    							<data name="id">
    								<type name="uint32" package="package0"/>
    								<value>0</value>
    							</data>
    							<data name="timestamp">
    								<type name="uint64" package="package0"/>
    								<value>47264855</value>
    							</data>
    							<data name="session_id">
    								<type name="int16" package="package0"/>
    								<value>82</value>
    							</data>
    							<data name="error_code">
    								<type name="uint32" package="package0"/>
    								<value>1332</value>
    							</data>
    							<data name="api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSid</value>
    							</data>
    							<data name="calling_api_name">
    								<type name="unicode_string" package="package0"/>
    								<value>LookupAccountSidInternal</value>
    							</data>
    							<data name="call_stack">
    								<type name="callstack" package="package0"/>
    								<value/>
    							</data>
    						</event>
    					</RingBufferTarget>
    				</session>
    			</events>
    		</value>
    	</data>
    	<data name="instance_name">
    		<value>InstanceName</value>
    	</data>
    	<data name="node_name">
    		<value>ServerName</value>
    	</data>
    </event>');
    -- DDL and sample data population, end
    
    SELECT col.value('(@name)[1]','VARCHAR(100)') AS [eventName]
    	, col.value('(data[@name="timestamp"]/value/text())[1]','VARCHAR(100)') AS [timestamp]
    	, col.value('(data[@name="opcode"]/value/text())[1]','INT') AS [opcode]
    	, col.value('(data[@name="worker"]/value/text())[1]','VARCHAR(100)') AS [worker]
    FROM @mockTbl tbl
        CROSS APPLY tbl.[xmlData].nodes('/event/data/value/events/session/RingBufferTarget/event[@name="scheduler_monitor_non_yielding_ring_buffer_recorded"]') AS tab(col)
    

    Output:

    eventName	timestamp	opcode	worker
    scheduler_monitor_non_yielding_ring_buffer_recorded	1604342839	0	0x00000005238f8160
    scheduler_monitor_non_yielding_ring_buffer_recorded	47382888	1	0x00000005238f8160

    • Marked as answer by HSever Wednesday, August 21, 2019 4:41 PM
    Wednesday, August 21, 2019 2:30 PM
  • Thank you Yitzhak, it works like a charm.

    I also need the query to bring all the distinct names and values/text from data elements of one of event type (lets say "scheduler_monitor_non_yielding_ring_buffer_recorded" ). Names attributes are needed, if text exists then use text, if not value.

    On a separate note, with your guidance I could parse the cluster diagnostic file

    SELECT  
    xml_data.value('(event/@name)[1]','varchar(max)') AS 'Name'  
    ,xml_data.value('(event/@package)[1]','varchar(max)') AS 'Package' 
    ,xml_data.value('(event/data[@name=''creation_time'']/value)[1]','datetime') AS 'EventCreationTime'  
    ,xml_data.value('(event/data[@name=''state'']/value)[1]','int') AS 'State'  
    ,xml_data.value('(event/data[@name=''state_desc'']/value)[1]','varchar(max)') AS 'State_Desc'  
    ,xml_data.value('(event/data[@name=''failure_condition_level'']/value)[1]','int') AS 'FailureCondition'  
    ,xml_data.value('(event/data[@name=''node_name'']/value)[1]','varchar(max)') AS 'Node_Name'  
    ,xml_data.value('(event/data[@name=''instance_name'']/value)[1]','varchar(max)') AS 'Instance_Name'  
    ,xml_data.value('(event/data[@name=''component'']/value)[1]','varchar(max)') AS 'Component'  
    ,xml_data.value('(event/data[@name=''component_type'']/value)[1]','varchar(max)') AS 'Component_type' 
    ,xml_data
    INTO #ServerStats
    FROM  
     ( SELECT object_name AS 'event'  
      ,CONVERT(xml,event_data) AS 'xml_data'  
    FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLNODE1_MSSQLSERVER_SQLDIAG_0_129936003752530000.xel',NULL,NULL,NULL))
      AS XEventData 


    select tbl.name,tbl.Package, tbl.EventCreationTime,tbl.state,tbl.State_Desc, tbl.FailureCondition,tbl.Node_Name, tbl.Instance_Name, tbl.Component, tbl.Component_type,
    col.value('(@name)[1]','VARCHAR(100)') AS [eventName]
    , col.value('(data[@name="timestamp"]/value/text())[1]','VARCHAR(100)') AS [timestamp]
    , col.value('(data[@name="opcode"]/value/text())[1]','INT') AS [opcode]
    , col.value('(data[@name="worker"]/value/text())[1]','VARCHAR(100)') AS [worker]
     from #ServerStats tbl 
    CROSS APPLY tbl.[xml_Data].nodes('/event/data/value/events/session/RingBufferTarget/event[@name="scheduler_monitor_non_yielding_ring_buffer_recorded"]') AS tab(col)
    where component = 'events'
    order by EventCreationTime, timestamp

    Thank you,


    Wednesday, August 21, 2019 4:41 PM