SE の雑記

WITH CTE AS (  
       SELECT CAST(event_data AS XML)  AS [target_data_XML]  
   FROM sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null)  
)  
SELECT target_data_XML.value('(/event/@timestamp)[1]', 'DateTime2') AS Timestamp,  
target_data_XML.query('/event/data[@name=''xml_report'']/value/deadlock') AS deadlock_xml,  
target_data_XML.query('/event/data[@name=''database_name'']/value').value('(/value)[1]', 'nvarchar(100)') AS db_name  
FROM CTE  

Clear-Host

$conString = "server=xxxxxx.database.windows.net;user=xxxxx;password=xxxxxx"

$con = New-Object System.Data.SqlClient.SqlConnection($conString)
$con.Open()
$sb = New-Object System.Text.StringBuilder
97..122 | %{
    $first = [char]$_
    97..122 | %{
        $second = [char]$_
        $sql = ("select top 10 '{0}{1}' AS type, timestamp_utc, object_name, file_name, cast(event_data as xml) as event_data from  sys.fn_xe_telemetry_blob_target_read_file('{0}{1}', null, null, null) order by timestamp_utc desc" -f $first,$second)
        $cmd = New-Object System.Data.SqlClient.SqlCommand($sql, $con)
        $cmd.CommandTimeout = 0
        $reader = $cmd.ExecuteReader()
        $dt = New-Object System.Data.DataTable
        $dt.Load($reader)
        if($dt.Rows.Count -ne 0){
            Write-Host $sql
        }
        $reader.Close()
    }
}
$con.Dispose()

select top 10 'dl' AS type, timestamp_utc, object_name, file_name, cast(event_data as xml) as event_data from  sys.fn_xe_telemetry_blob_target_read_file('dl', null, null, null) order by timestamp_utc desc
select top 10 'el' AS type, timestamp_utc, object_name, file_name, cast(event_data as xml) as event_data from  sys.fn_xe_telemetry_blob_target_read_file('el', null, null, null) order by timestamp_utc desc
select top 10 'rs' AS type, timestamp_utc, object_name, file_name, cast(event_data as xml) as event_data from  sys.fn_xe_telemetry_blob_target_read_file('rs', null, null, null) order by timestamp_utc desc
select top 10 'ru' AS type, timestamp_utc, object_name, file_name, cast(event_data as xml) as event_data from  sys.fn_xe_telemetry_blob_target_read_file('ru', null, null, null) order by timestamp_utc desc