Otomatisasi server Jenkins SQL: mengembalikan hasilnya dengan indah

Melanjutkan tema mengatur Zero Touch PROD di bawah RDS lagi. DBA di masa mendatang tidak akan dapat terhubung ke server PROD secara langsung, tetapi akan dapat menggunakan pekerjaan Jenkins untuk serangkaian operasi terbatas. DBA memulai pekerjaan dan setelah beberapa saat menerima surat berisi laporan tentang penyelesaian operasi ini. Mari kita lihat cara untuk mempresentasikan hasil ini kepada pengguna.

Teks biasa

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



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



Ngomong-ngomong, kami tidak lupa bahwa di bawah RDS backup / restore asynchronous, jadi kita harus menunggu untuk itu:

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 

Cara kedua, CSV

Semuanya di sini juga sangat sederhana:



Namun, metode ini hanya berfungsi jika data yang dikembalikan ke CSV "sederhana". Jika Anda mencoba mengembalikan, misalnya, daftar permintaan intensif TOP N CPU, maka CSV akan "terkorosi" karena fakta bahwa teks kueri dapat berisi karakter apa pun - koma, tanda kutip, dan bahkan pemisah baris. Karena itu, kita membutuhkan sesuatu yang lebih rumit.

Tag HTML yang indah

Saya akan memberi Anda potongan 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 satu baris di grid, maka ada beberapa masalah. Keputusan diambil dari Internet tanpa banyak pemahaman. Hasilnya, Anda mendapatkan hasilnya, ditulis seperti ini:

Gambarlah

Peringatan: kode sesat di bawah ini!

Ada permintaan lucu ke server SQL yang menampilkan CPU dalam N menit terakhir - Ternyata Kamerad Utama mengingat semuanya! Coba yang 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 format ini ($ Fragment variable)

 <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 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 terlihat seperti ini:



Ya, Monsieur tahu banyak tentang penyimpangan! Menariknya, kode ini mengandung: Powershell (tertulis di atasnya), SQL, Xquery, HTML. Sangat disayangkan bahwa kita tidak dapat menambahkan Javascript ke HTML (karena ini untuk menulis), tetapi tugas semua orang adalah untuk menggiling kode Python (yang dapat digunakan dalam SQL)!

Output penelusuran profiler SQL

Jelas bahwa jejak tidak akan "cocok" di CSV karena bidang TextData. Tetapi aneh juga untuk menampilkan kisi-kisi dalam surat - baik karena ukuran dan karena seringnya data ini digunakan untuk analisis lebih lanjut. Oleh karena itu, kami melakukan hal berikut: kami memohon melalui memanggil-SqlCmd skrip tertentu, di dalam usus yang dilakukan

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

Selanjutnya, pada server lain yang dapat diakses oleh DBA, ada basis Jejak dengan kosong kosong, pelat Model, siap untuk 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 menulis jejak kita dengan menggunakan Data.SqlClient.SqlBulkCopy - Saya telah memberikan contoh di atas ini. Ya, akan lebih baik untuk menyembunyikan 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 dengan lebih dari satu karakter dengan panjang 999, dan kami mengganti string yang lebih panjang dari satu karakter dengan 'str'. Angka 0 hingga 9 sering digunakan sebagai bendera, dan kami tidak menyentuhnya, serta string kosong dan karakter tunggal - di antaranya sering ditemukan 'Y', 'N', dll.

Tambahkan warna ke kehidupan kita (ketat 18+)

Di tablet Anda sering ingin menyorot sel yang membutuhkan perhatian. Misalnya, GAGAL, ​​tingkat fragmentasi yang tinggi, dll. Tentu saja, ini juga dapat dilakukan pada bare SQL, membentuk HTML menggunakan PRINT, dan di Jenkins Anda dapat mengatur jenis file HTML:

 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?



Tetapi ada solusi yang lebih indah. ConvertTo-HTML tidak memungkinkan kita untuk mewarnai sel, tetapi kita bisa melakukannya setelah faktanya. Misalnya, kami ingin memilih sel dengan tingkat fragmentasi lebih dari 80 dan lebih dari 90. 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> 

Di qi itu sendiri, kita akan menambahkan kolom dummy langsung ke kolom yang ingin kita beri warna. Kolom harus disebut SQLmarkup- something:

 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 dibuat oleh Powershell, kami akan menghapus kolom dummy dari header dan mentransfer nilai dari kolom ke gaya di badan data. Ini dilakukan hanya dalam dua permutasi:

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

Hasil:



Bukankah itu elegan? Meskipun tidak, sesuatu yang mengingatkan saya pada pewarnaan ini