🥇Otomasi server SQL di Jenkins: mengembalikan hasilnya dengan indah

Lagi melanjutkan tema penataan PROD Nol Sentuh di bawah RDS. DBA masa depan tidak akan dapat terhubung ke server PROD secara langsung, namun akan dapat digunakan Jenkins pekerjaan untuk serangkaian operasi terbatas. DBA meluncurkan pekerjaan dan setelah beberapa waktu menerima surat yang melaporkan penyelesaian operasi ini. Mari kita lihat cara untuk menyajikan hasil ini kepada pengguna.

Teks Biasa

Mari kita mulai dengan hal yang paling sepele. Cara pertama sangat sederhana sehingga tidak ada yang perlu dibicarakan (selanjutnya penulis menggunakan pekerjaan FreeStyle):

sqlcmd melakukan sesuatu dan kami menyajikannya kepada pengguna. Ideal untuk, misalnya, pekerjaan pencadangan:

Ngomong-ngomong, jangan lupa bahwa pencadangan/pemulihan di bawah RDS tidak sinkron, jadi Anda harus menunggu:

declare @rds table
  (id int, task_type varchar(128), database_name sysname, pct int, duration int, 
   lifecycle varchar(128), taskinfo varchar(max) null, 
   upd datetime, cre datetime,
   s3 varchar(256), ovr int, KMS varchar(256) null)
waitfor delay '00:00:20' 
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}'
select @xid=max(id) from @rds

again:
waitfor delay '00:00:02'
delete from @rds
insert into @rds exec msdb.dbo.rds_task_status @db_name='{db}' 
# {db} substituted with db name by powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Metode kedua, CSV

Semuanya di sini juga sangat sederhana:

Namun, metode ini hanya berfungsi jika data yang dikembalikan dalam CSV "sederhana". Jika Anda mencoba mengembalikan, misalnya, daftar kueri intensif CPU TOP N dengan cara ini, CSV akan "terkorosi" karena teks kueri dapat berisi karakter apa pun - koma, tanda kutip, dan bahkan jeda baris. Oleh karena itu, kita memerlukan sesuatu yang lebih rumit.

Tanda-tanda indah dalam HTML

Saya akan segera memberi Anda cuplikan kode

$Header = @"
<style>
TABLE {border-width: 1px; border-style: solid; border-color: black; border-collapse: collapse;}
TH {border-width: 1px; padding: 3px; border-style: solid; border-color: black; background-color: #6495ED;}
TD {border-width: 1px; padding: 3px; border-style: solid; border-color: black;}
</style>
"@
  
$Result = invoke-Sqlcmd -ConnectionString $jstr -Query "select * from DbInv" `
  | Select-Object -Property * -ExcludeProperty "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result -eq $null) { $cnt = 0; }
elseif ($Result.getType().FullName -eq "System.Management.Automation.PSCustomObject") { $cnt = 1; }
else { $cnt = $Result.Rows.Count; } 
if ($cnt -gt 0) {
  $body = "<h2>My table</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>No data</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Ngomong-ngomong, perhatikan baris dengan System.Management.Automation.PSCustomObject, itu ajaib, jika ada tepat satu baris di grid, maka beberapa masalah akan muncul. Solusinya diambil dari Internet tanpa banyak pemahaman. Hasilnya, Anda akan mendapatkan keluaran dengan format seperti ini:

Menggambar grafik

Peringatan: kode keriting di bawah ini!
Ada query lucu di SQL server yang menampilkan CPU selama N menit terakhir - ternyata Kamerad Mayor mengingat semuanya! Coba kuis ini:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks) 
  FROM sys.dm_os_sys_info WITH (NOLOCK)); 
SELECT TOP(256) 
  DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [EventTime],
  SQLProcessUtilization AS [SQLCPU], 
  100 - SystemIdle - SQLProcessUtilization AS [OtherCPU]
FROM (SELECT record.value('(./Record/@id)[1]', 'int') AS record_id, 
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') 
    AS [SystemIdle], 
  record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') 
    AS [SQLProcessUtilization], [timestamp] 
  FROM (SELECT [timestamp], CONVERT(xml, record) AS [record] 
  FROM sys.dm_os_ring_buffers WITH (NOLOCK)
  WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
    AND record LIKE N'%<SystemHealth>%') AS x) AS y 
ORDER BY 1 DESC OPTION (RECOMPILE);

Sekarang, menggunakan pemformatan ini ($variabel Fragmen)

<table style="width: 100%"><tbody><tr style="background-color: white; height: 2pt;">
  <td style="width: SQLCPU%; background-color: green;"></td>
  <td style="width: OtherCPU%; background-color: blue;"></td>
  <td style="width: REST%; background-color: #C0C0C0;"></td></tr></tbody>
</table>

Kita dapat membentuk badan surat:

$Result = invoke-Sqlcmd -ConnectionString $connstr -Query $Query `
  | Select-Object -Property * -ExcludeProperty `
  "ItemArray", "RowError", "RowState", "Table", "HasErrors"
if ($Result.HasRows) {
  foreach($item in $Result) 
    { 
    $time = $itemEventTime 
    $sqlcpu = $item.SQLCPU
    $other = $itemOtherCPU
    $rest = 100 - $sqlcpu - $other
    $f = $fragment -replace "SQLCPU", $sqlcpu
    $f = $f -replace "OtherCPU", $other
    $f = $f -replace "REST", $rest
    $f | Out-File "res.log" -Append -Encoding UTF8
    }

Yang akan terlihat seperti ini:

Ya, Tuan tahu banyak tentang penyimpangan! Menariknya, kode ini berisi: Powershell (tertulis di dalamnya), SQL, Xquery, HTML. Sayangnya kami tidak dapat menambahkan Javascript ke HTML (karena ini untuk menulis), tetapi memoles kode Python (yang dapat digunakan dalam SQL) adalah tugas semua orang!

Keluaran jejak profiler SQL

Jelas bahwa jejaknya tidak akan masuk ke dalam CSV karena bidang TextData. Namun menampilkan kisi-kisi jejak dalam sebuah huruf juga aneh - baik karena ukurannya maupun karena data ini sering digunakan untuk analisis lebih lanjut. Oleh karena itu, kami melakukan hal berikut: kami menelepon melalui panggil-SqlCmd naskah tertentu, yang di dalamnya hal itu dilakukan

select 
  SPID,EventClass,TextData,
  Duration,Reads,Writes,CPU,
  StartTime,EndTime,DatabaseName,HostName,
  ApplicationName,LoginName
   from ::fn_trace_gettable ( @filename , default )

Selanjutnya teman Di server yang dapat diakses oleh DBA, terdapat database Jejak dengan templat kosong, pelat Model, siap menerima semua kolom yang ditentukan. Kami menyalin model ini ke tabel baru dengan nama unik:

$dt = Get-Date -format "yyyyMMdd"
$tm = Get-Date -format "hhmmss"
$tableName = $srv + "_" + $dt + "_" + $tm
$copytab = "select * into " + $tableName + " from Model"
invoke-SqlCmd -ConnectionString $tstr -Query $copytab

Dan sekarang kita dapat menuliskan jejak kita ke dalamnya menggunakan Data.SqlClient.SqlBulkCopy - Saya sudah memberikan contohnya di atas. Ya, alangkah baiknya juga untuk menutupi konstanta di TextData:

# mask data
foreach ($Row in $Result)
{ 
  $v = $Row["TextData"]
  $v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
  $Row["TextData"] = $v
}

Kami mengganti angka yang panjangnya lebih dari satu karakter dengan 999, dan kami mengganti string yang panjangnya lebih dari satu karakter dengan 'str'. Angka dari 0 hingga 9 sering digunakan sebagai bendera, dan kita tidak menyentuhnya, begitu juga dengan string kosong dan berkarakter tunggal - 'Y', 'N', dll.

Mari tambahkan warna pada hidup kita (khusus 18+)

Dalam tabel, Anda sering kali ingin menyorot sel yang memerlukan perhatian. Misalnya GAGAL, fragmentasi tingkat tinggi, dll. Tentu saja, ini dapat dilakukan dalam SQL sederhana, membuat HTML menggunakan PRINT, dan mengatur jenis file ke HTML di Jenkins:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Server name: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Last Run</th><th>Avg Duration, sec</th><th>Last Run, Sec</th><th>Last Status</th></tr>'
print @body

DECLARE tab CURSOR FOR SELECT '<tr><td>'+name+'</td><td>'+
  LastRun+'</td><td>'+
  convert(varchar,AvgDuration)+'</td><td>'+
  convert(varchar,LastDuration)+'</td><td>'+
    case when LastStatus<>'Succeeded' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Succeeded' then '</font>' else '' end+
     +'</td><td>'
  from #j2
OPEN tab;  
FETCH NEXT FROM tab into @chunk
WHILE @@FETCH_STATUS = 0  
BEGIN
  print @chunk
  FETCH NEXT FROM tab into @chunk;  
END  
CLOSE tab;  
DEALLOCATE tab;
print '</table>'

Mengapa saya menulis kode seperti itu?

Namun ada solusi yang lebih indah. Konversi Ke-HTML tidak membiarkan kita mewarnai sel, tapi kita bisa melakukannya setelah kejadian itu. Misalnya, kita ingin memilih sel dengan tingkat fragmentasi lebih dari 80 dan lebih dari 90. Mari tambahkan gaya:

<style>
.SQLmarkup-red { color: red; background-color: yellow; }
.SQLmarkup-yellow { color: black; background-color: #FFFFE0; }
.SQLmarkup-default { color: black; background-color: white; }
</style>

Dalam query itu sendiri kita akan menambahkan kolom dummy segera sebelumnya kolom yang ingin kita warnai. Kolom harus diberi nama markup SQL-sesuatu:

case  
  when ps.avg_fragmentation_in_percent>=90.0 then 'SQLmarkup-red'
  when ps.avg_fragmentation_in_percent>=80.0 then 'SQLmarkup-yellow'
  else 'SQLmarkup-default' 
  end as [SQLmarkup-1], 
ps.avg_fragmentation_in_percent,

Sekarang, setelah menerima HTML yang dihasilkan oleh Powershell, kami akan menghapus kolom dummy dari header, dan di badan data kami akan mentransfer nilai dari kolom ke gaya. Hal ini dilakukan hanya dengan dua substitusi:

$html = $html `
  -replace "<th>SQLmarkup[^<]*</th>", "" `
  -replace "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'

Hasilnya:

Elegan bukan? Meski tidak, pewarnaan ini mengingatkanku pada sesuatu

Sumber: www.habr.com

Otomatisasi server SQL di Jenkins: mengembalikan hasilnya dengan indah