SQL Server Connectivity Ring Buffer Schema

# As of SQL 2017
/Record @id @type @time

/Record/ConnectivityTraceRecord/ClientConnectionId
/Record/ConnectivityTraceRecord/IsClient
/Record/ConnectivityTraceRecord/LocalHost
/Record/ConnectivityTraceRecord/LocalPort
/Record/ConnectivityTraceRecord/OSError
/Record/ConnectivityTraceRecord/RecordSource
/Record/ConnectivityTraceRecord/RecordTime
/Record/ConnectivityTraceRecord/RecordType
/Record/ConnectivityTraceRecord/RemoteHost
/Record/ConnectivityTraceRecord/RemotePort
/Record/ConnectivityTraceRecord/SniConnectionId
/Record/ConnectivityTraceRecord/SniConnId
/Record/ConnectivityTraceRecord/SniConsumerError
/Record/ConnectivityTraceRecord/SniError
/Record/ConnectivityTraceRecord/SniProvider
/Record/ConnectivityTraceRecord/Spid
/Record/ConnectivityTraceRecord/State

/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/EnqueueTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetReadsTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetWritesTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TotalTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/EnqueueTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/NetReadsTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/NetWritesTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/SecureCallsTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/TotalTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/EnqueueTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/NetReadsTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/NetWritesTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/SecureCallsTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/TotalTime
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/ExecClassifier
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/FindLogin
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/LogonTriggers
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/SessionRecover
/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/TotalTime

/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes
/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError
/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError
/Record/ConnectivityTraceRecord/TdsBufInfo/InputBufBytes
/Record/ConnectivityTraceRecord/TdsBufInfo/InputBufError
/Record/ConnectivityTraceRecord/TdsBufInfo/OutputBufError

/Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/RoutingCompleted
/Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled

/Record/Stack/frame

# Also note that some fields seem to have changed names and can be coalesced. This
# extracts all of the above information in flat format except the stack frame

# Below updated for SQL 2019

; WITH RingBuffer AS (
        SELECT  records.record.value('(/Record/@id)[1]', 'int') AS Id,
                records.record.value('(/Record/@type)[1]', 'varchar(50)') AS Type,
                records.record.value('(/Record/@time)[1]', 'bigint') AS Time,

                records.record.value('(/Record/ConnectivityTraceRecord/ClientConnectionId)[1]', 'uniqueidentifier') AS ClientConnectionId,
                records.record.value('(/Record/ConnectivityTraceRecord/IsClient)[1]', 'bit') AS IsClient,
                records.record.value('(/Record/ConnectivityTraceRecord/LocalHost)[1]', 'varchar(max)') AS LocalHost,
                records.record.value('(/Record/ConnectivityTraceRecord/LocalPort)[1]', 'int') AS LocalPort,
                records.record.value('(/Record/ConnectivityTraceRecord/OSError)[1]', 'int') AS OSError,
                records.record.value('(/Record/ConnectivityTraceRecord/RecordSource)[1]', 'varchar(max)') AS RecordSource,
                records.record.value('(/Record/ConnectivityTraceRecord/RecordTime)[1]', 'datetime') AS RecordTime,
                records.record.value('(/Record/ConnectivityTraceRecord/RecordType)[1]', 'varchar(max)') AS RecordType,
                records.record.value('(/Record/ConnectivityTraceRecord/RemoteHost)[1]', 'varchar(max)') AS RemoteHost,
                records.record.value('(/Record/ConnectivityTraceRecord/RemotePort)[1]', 'varchar(max)') AS RemotePort,
                
                records.record.value('(/Record/ConnectivityTraceRecord/SniConnectionId)[1]', 'uniqueidentifier') AS SniConnectionId,
                records.record.value('(/Record/ConnectivityTraceRecord/SniConnId)[1]', 'uniqueidentifier') AS SniConnId,
                
                records.record.value('(/Record/ConnectivityTraceRecord/SniConsumerError)[1]', 'int') AS SniConsumerError,
                records.record.value('(/Record/ConnectivityTraceRecord/SniError)[1]', 'int') AS SniError,
                records.record.value('(/Record/ConnectivityTraceRecord/SniProvider)[1]', 'int') AS SniProvider,
                records.record.value('(/Record/ConnectivityTraceRecord/Spid)[1]', 'int') AS Spid,
                records.record.value('(/Record/ConnectivityTraceRecord/State)[1]', 'int') AS State,

                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/EnqueueTime)[1]', 'int') AS EnqueueTime,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetReadsTime)[1]', 'int') AS NetReadsTime,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/NetWritesTime)[1]', 'int') AS NetWritesTime,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TotalTime)[1]', 'int') AS TotalTime,

                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/EnqueueTime)[1]', 'int') AS EnqueueTimeSsl,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/NetReadsTime)[1]', 'int') AS NetReadsTimeSsl,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/NetWritesTime)[1]', 'int') AS NetWritesTimeSsl,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/SecureCallsTime)[1]', 'int') AS SecureCallsTimeSsl,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Ssl/TotalTime)[1]', 'int') AS TotalTimeSsl,

                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/EnqueueTime)[1]', 'int') AS EnqueueTimeSspi,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/NetReadsTime)[1]', 'int') AS NetReadsTimeSspi,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/NetWritesTime)[1]', 'int') AS NetWritesTimeSspi,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/SecureCallsTime)[1]', 'int') AS SecureCallsTimeSspi,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/Sspi/TotalTime)[1]', 'int') AS TotalTimeSspi,

                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/ExecClassifier)[1]', 'int') AS ExecClassifierGov,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/FindLogin)[1]', 'int') AS FindLoginGov,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/LogonTriggers)[1]', 'int') AS LogonTriggersGov,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/SessionRecover)[1]', 'int') AS SessionRecoverGov,
                records.record.value('(/Record/ConnectivityTraceRecord/LoginTimersInMilliseconds/TriggerAndResGovTime/TotalTime)[1]', 'int') AS TotalTimeGov,

                records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferBytes)[1]', 'int') AS TdsInputBufferBytes,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsInputBufferError)[1]', 'int') AS TdsInputBufferError,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsBuffersInformation/TdsOutputBufferError)[1]', 'int') AS TdsOutputBufferError,
                
                records.record.value('(/Record/ConnectivityTraceRecord/TdsBufInfo/InputBufBytes)[1]', 'int') AS TdsInputBufBytes,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsBufInfo/InputBufError)[1]', 'int') AS TdsInputBufError,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsBufInfo/OutputBufError)[1]', 'int') AS TdsOutputBufError,

                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/DisconnectDueToReadError)[1]', 'bit') AS DisconnectDueToReadError,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/ErrorFoundBeforeLogin)[1]', 'bit') AS ErrorFoundBeforeLogin,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NetworkErrorFoundInInputStream)[1]', 'bit') AS NetworkErrorFoundInInputStream,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalDisconnect)[1]', 'bit') AS NormalDisconnect,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/NormalLogout)[1]', 'varchar(max)') AS NormalLogout,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/PhysicalConnectionIsKilled)[1]', 'bit') AS PhysicalConnectionIsKilled,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/RoutingCompleted)[1]', 'bit') AS RoutingCompleted,
                records.record.value('(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]', 'bit') AS SessionIsKilled

                -- Stack/frame
                -- record_data
        FROM    (
                SELECT  CAST(record AS XML) AS record_data
                FROM    sys.dm_os_ring_buffers r
                WHERE   ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
                ) a
        CROSS APPLY 
                record_data.nodes('//Record') AS records (record)
        )
SELECT  rb.Id,
        rb.Type,
        rb.Time,
        NULLIF(rb.ClientConnectionId, '00000000-0000-0000-0000-000000000000') AS ClientConnectionId,
        rb.IsClient,
        rb.LocalHost,
        rb.LocalPort,
        NULLIF(rb.OSError, 0) AS OSError,
        rb.RecordSource,
        rb.RecordTime,
        rb.RecordType,
        rb.RemoteHost,
        rb.RemotePort,
        s.SniConnectionId,
        s.SniConsumerError,
        rb.SniProvider,
        NULLIF(rb.Spid, 0) AS Spid,
        rb.State,
        
        rb.EnqueueTime,
        rb.NetReadsTime,
        rb.NetWritesTime,
        rb.TotalTime,
        
        rb.EnqueueTimeSsl,
        rb.NetReadsTimeSsl,
        rb.NetWritesTimeSsl,
        rb.SecureCallsTimeSsl,
        rb.TotalTimeSsl,
        
        rb.EnqueueTimeSspi,
        rb.NetReadsTimeSspi,
        rb.NetWritesTimeSspi,
        rb.SecureCallsTimeSspi,
        rb.TotalTimeSspi,

        rb.ExecClassifierGov,
        rb.FindLoginGov,
        rb.LogonTriggersGov,
        rb.SessionRecoverGov,
        rb.TotalTimeGov,

        s.TdsInputBufferBytes,
        s.TdsInputBufferError,
        s.TdsOutputBufferError,

        rb.DisconnectDueToReadError,
        rb.ErrorFoundBeforeLogin,
        rb.NetworkErrorFoundInInputStream,
        rb.NormalDisconnect,
        rb.NormalLogout,
        rb.PhysicalConnectionIsKilled,
        rb.RoutingCompleted,
        rb.SessionIsKilled

        -- s.CoalesceError
FROM    RingBuffer rb
OUTER APPLY (
        SELECT  COALESCE(rb.SniConnectionId, rb.SniConnId) AS SniConnectionId,
                NULLIF(COALESCE(rb.SniConsumerError, rb.SniError), 0) AS SniConsumerError,
                COALESCE(rb.TdsInputBufferBytes, rb.TdsInputBufBytes) AS TdsInputBufferBytes,
                NULLIF(COALESCE(rb.TdsInputBufferError, rb.TdsInputBufError), 0) AS TdsInputBufferError,
                NULLIF(COALESCE(rb.TdsOutputBufferError, rb.TdsOutputBufError), 0) AS TdsOutputBufferError,

                CASE
                WHEN    (rb.SniConnectionId IS NOT NULL AND rb.SniConnID IS NOT NULL)
                OR      (rb.SniConsumerError IS NOT NULL AND rb.SniError IS NOT NULL)
                OR      (rb.TdsInputBufferBytes IS NOT NULL AND rb.TdsInputBufBytes IS NOT NULL)
                OR      (rb.TdsInputBufferError IS NOT NULL AND rb.TdsInputBufError IS NOT NULL)
                OR      (rb.TdsOutputBufferError IS NOT NULL AND rb.TdsOutputBufError IS NOT NULL)
                THEN    1
                END     AS CoalesceError
        ) s
ORDER BY
        rb.RecordTime, rb.Id
;