🥇Jenkins'te SQL sunucusunun otomasyonu: sonucun güzel bir şekilde döndürülmesi

tekrar düzenleme temasına devam etmek Sıfır Dokunuş PROD RDS kapsamında. Gelecekteki DBA'lar PROD sunucularına doğrudan bağlanamayacak ancak Jenkins sınırlı sayıda operasyon için işler. DBA işi başlatır ve bir süre sonra bu operasyonun tamamlandığını bildiren bir rapor içeren bir mektup alır. Bu sonuçları kullanıcıya sunmanın yollarına bakalım.

Düz Metin

En önemsiz olanla başlayalım. İlk yöntem o kadar basittir ki aslında hakkında konuşulacak hiçbir şey yoktur (bundan sonra yazar FreeStyle işlerini kullanır):

Sqlcmd bir şey yapar ve onu kullanıcıya sunarız. Örneğin yedekleme işleri için idealdir:

Bu arada, RDS altında yedekleme/geri yüklemenin eşzamansız olduğunu, bu nedenle beklemeniz gerektiğini unutmayın:

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

İkinci yöntem, CSV

Buradaki her şey de çok basit:

Ancak bu yöntem yalnızca CSV'de döndürülen verilerin "basit" olması durumunda işe yarar. Örneğin, TOP N CPU yoğun sorguların bir listesini bu şekilde döndürmeye çalışırsanız, sorgu metninin herhangi bir karakter (virgül, tırnak işareti ve hatta satır sonları) içerebilmesi nedeniyle CSV "paslanır". Bu nedenle daha karmaşık bir şeye ihtiyacımız var.

HTML'deki güzel işaretler

Size hemen bir kod pasajı vereceğim

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

Bu arada System.Management.Automation.PSCustomObject satırına dikkat edin, sihirlidir, gridde tam olarak tek satır varsa bazı sorunlar ortaya çıkmıştır. Çözüm internetten pek anlaşılmadan alındı. Sonuç olarak, şunun gibi biçimlendirilmiş bir çıktı alacaksınız:

Grafik çizme

Uyarı: garip kod aşağıda!
SQL sunucusunda son N dakikadaki CPU'yu görüntüleyen komik bir sorgu var - Yoldaş Binbaşı'nın her şeyi hatırladığı ortaya çıktı! Bu testi deneyin:

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);

Şimdi bu biçimlendirmeyi kullanarak ($Fragment değişkeni)

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

Mektubun gövdesini şöyle oluşturabiliriz:

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

Hangisi şuna benzeyecek:

Evet Mösyö sapıklıklar hakkında çok şey biliyor! Bu kodun şunları içermesi ilginçtir: Powershell (içinde yazılı), SQL, Xquery, HTML. HTML'ye Javascript'i ekleyemiyor olmamız üzücü (çünkü bu yazmak için), ancak Python kodunu (SQL'de kullanılabilir) geliştirmek herkesin görevidir!

SQL profil oluşturucu izleme çıkışı

TextData alanı nedeniyle izlemenin CSV'ye sığmayacağı açıktır. Ancak bir mektupta iz ızgarasının görüntülenmesi de hem boyuttan hem de bu verilerin sıklıkla daha ileri analizler için kullanılmasından dolayı gariptir. Bu nedenle aşağıdakileri yapıyoruz: aracılığıyla çağırıyoruz çağırmak-SqlCmd Derinliklerinde yapıldığı belirli bir senaryo

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

Sıradaki другом DBA tarafından erişilebilen sunucuda, belirtilen tüm sütunları kabul etmeye hazır, boş bir şablon olan Model plakasına sahip bir Traces veritabanı bulunmaktadır. Bu modeli benzersiz bir adla yeni bir tabloya kopyalıyoruz:

$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

Ve şimdi izimizi şunu kullanarak yazabiliriz: Data.SqlClient.SqlBulkCopy - Bunun örneğini zaten yukarıda vermiştim. Evet, TextData'da sabitleri maskelemek de güzel olurdu:

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

Bir karakterden uzun sayıları 999 ile, bir karakterden uzun dizeleri ise 'str' ile değiştiririz. Bayrak olarak sıklıkla 0'dan 9'a kadar olan sayılar kullanılır ve bunlara dokunmayız, ayrıca boş ve tek karakterli dizeler - 'Y', 'N' vb. de sıklıkla bunların arasında bulunur.

Hayatımıza biraz renk katalım (kesinlikle 18+)

Tablolarda sıklıkla dikkat edilmesi gereken hücreleri vurgulamak istersiniz. Örneğin, BAŞARISIZLIKLAR, yüksek düzeyde parçalanma vb. Elbette bu, PRINT kullanarak HTML oluşturarak ve Jenkins'te dosya türünü HTML olarak ayarlayarak çıplak SQL'de yapılabilir:

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>'

Neden böyle bir kod yazdım?

Ama daha güzel bir çözüm var. HTML'ye Dönüştür hücreleri renklendirmemize izin vermiyor ama bunu daha sonra yapabiliriz. Örneğin parçalanma düzeyi 80'in üzerinde ve 90'ın üzerinde olan hücreleri seçmek istiyoruz. Stiller ekleyelim:

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

Sorgunun kendisine sahte bir sütun ekleyeceğiz hemen önce renklendirmek istediğimiz sütun. Sütun çağrılmalıdır SQLmarkup-bir şey:

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,

Şimdi, Powershell tarafından oluşturulan HTML'yi aldıktan sonra, kukla sütunu başlıktan kaldıracağız ve veri gövdesinde değeri sütundan stile aktaracağız. Bu sadece iki değişiklikle yapılır:

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

Sonuç:

Zarif değil mi? Hayır olmasına rağmen bu renklendirme bana bir şeyi hatırlatıyor

Kaynak: habr.com

Jenkins'te SQL sunucusunun otomasyonu: sonucun güzel bir şekilde döndürülmesi