🥇Automasi pelayan SQL dalam Jenkins: mengembalikan hasil dengan cantik

Sekali lagi meneruskan tema susunan PROD Sentuhan Sifar di bawah RDS. DBA masa hadapan tidak akan dapat menyambung ke pelayan PROD secara langsung, tetapi akan dapat digunakan Jenkins pekerjaan untuk set operasi yang terhad. DBA melancarkan tugas dan selepas beberapa lama menerima surat dengan laporan mengenai penyempurnaan operasi ini. Mari lihat cara untuk membentangkan hasil ini kepada pengguna.

Teks Biasa

Mari kita mulakan dengan yang paling remeh. Kaedah pertama adalah sangat mudah sehingga tiada apa-apa untuk dibincangkan (selepas ini pengarang menggunakan kerja FreeStyle):

sqlcmd melakukan sesuatu dan kami membentangkannya kepada pengguna. Ideal untuk, sebagai contoh, kerja sandaran:

Jangan lupa, dengan cara itu, di bawah sandaran/pemulihan RDS adalah tak segerak, jadi anda perlu menunggunya:

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

Kaedah kedua, CSV

Segala-galanya di sini juga sangat mudah:

Walau bagaimanapun, kaedah ini hanya berfungsi jika data yang dikembalikan dalam CSV adalah "mudah". Jika anda cuba mengembalikan, sebagai contoh, senarai pertanyaan intensif CPU TOP N dengan cara ini, CSV akan "terhakis" disebabkan fakta bahawa teks pertanyaan boleh mengandungi sebarang aksara - koma, petikan dan juga pemisah baris. Oleh itu, kita memerlukan sesuatu yang lebih rumit.

Tanda-tanda cantik dalam HTML

Saya akan memberikan anda coretan kod dengan segera

$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
  }

Dengan cara ini, perhatikan baris dengan System.Management.Automation.PSCustomObject, ia adalah ajaib, jika terdapat tepat satu baris dalam grid, maka beberapa masalah timbul. Penyelesaiannya diambil dari Internet tanpa banyak pemahaman. Akibatnya, anda akan mendapat output yang diformatkan seperti ini:

Melukis graf

Amaran: kod keriting di bawah!
Terdapat pertanyaan lucu pada pelayan SQL yang memaparkan CPU untuk N minit terakhir - ternyata Comrade Major mengingati segala-galanya! Cuba kuiz 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 ($Pembolehubah 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 boleh membentuk badan surat itu:

$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 kelihatan seperti ini:

Ya, Monsieur tahu banyak tentang penyelewengan! Menariknya kod ini mengandungi: Powershell (ditulis di dalamnya), SQL, Xquery, HTML. Sayang sekali kami tidak boleh menambah Javascript pada HTML (kerana ia untuk menulis), tetapi menggilap kod Python (yang boleh digunakan dalam SQL) adalah kewajipan semua orang!

Keluaran surih profil SQL

Adalah jelas bahawa surih tidak akan dimuatkan ke dalam CSV kerana medan TextData. Tetapi memaparkan grid jejak dalam surat juga adalah pelik - kedua-duanya kerana saiz dan kerana data ini sering digunakan untuk analisis lanjut. Oleh itu, kami melakukan perkara berikut: kami menghubungi melalui invoke-SqlCmd skrip tertentu, sedalam mana ia dilakukan

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

Seterusnya, pada kawan Pada pelayan yang boleh diakses oleh DBA, terdapat pangkalan data Jejak dengan templat kosong, plat Model, sedia menerima semua lajur yang ditentukan. Kami menyalin model ini ke jadual baharu 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 boleh menulis jejak kita ke dalamnya menggunakan Data.SqlClient.SqlBulkCopy - Saya telah memberikan contoh di atas. Ya, ia juga bagus untuk menutup pemalar dalam 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 menggantikan nombor lebih daripada satu aksara dengan 999, dan kami menggantikan rentetan yang lebih panjang daripada satu aksara dengan 'str'. Nombor dari 0 hingga 9 sering digunakan sebagai bendera, dan kami tidak menyentuhnya, serta rentetan kosong dan satu aksara - 'Y', 'N', dsb. sering dijumpai di antaranya.

Mari tambahkan sedikit warna pada kehidupan kita (secara ketat 18+)

Dalam jadual, anda sering ingin menyerlahkan sel yang memerlukan perhatian. Contohnya, GAGAL, tahap pemecahan yang tinggi, dsb. Sudah tentu, ini boleh dilakukan dalam SQL kosong, menjana HTML menggunakan PRINT, dan menetapkan jenis fail kepada HTML dalam 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 kod sedemikian?

Tetapi ada penyelesaian yang lebih indah. ConvertTo-HTML tidak membenarkan kita mewarnai sel, tetapi kita boleh melakukannya selepas fakta. Sebagai contoh, kami ingin memilih sel dengan tahap pemecahan lebih daripada 80 dan lebih daripada 90. Mari tambah 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 pertanyaan itu sendiri kami akan menambah lajur tiruan serta-merta sebelum ini lajur yang kita ingin warnakan. Lajur harus dipanggil SQLmarkup-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 dijana oleh Powershell, kami akan mengalih keluar lajur dummy dari pengepala, dan dalam badan data kami akan memindahkan nilai dari lajur ke gaya. Ini dilakukan dengan hanya dua penggantian:

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

Keputusan:

Ia tidak elegan? Walaupun tidak, pewarna ini mengingatkan saya pada sesuatu

Sumber: www.habr.com

Automasi pelayan SQL dalam Jenkins: mengembalikan hasilnya dengan cantik