🥇Automatyzacja SQL server w Jenkins: zwracamy wyniki w atrakcyjny sposób

Znowu kontynuując temat urządzania Zero Touch PROD pod RDS. Przyszli DBA nie będą mieli możliwości łączenia się z serwerami PROD bezpośrednio, ale będą mogli korzystać z Jenkins zadań do ograniczonego zestawu operacji. DBA uruchamia zadanie i po pewnym czasie otrzymuje e-mail z raportem dotyczącym wykonania tej operacji. Zobaczmy sposoby prezentacji tych wyników użytkownikowi.

Tekst prosty

Zacznijmy od najbardziej trywialnego. Pierwszy sposób jest na tyle prosty, że właściwie nie ma o czym mówić (autor tutaj i dalej wykorzystuje zadania FreeStyle):

sqlcmd coś wykonuje, a my prezentujemy to użytkownikowi. Idealnie nadaje się na przykład do zadań kopii zapasowej:

Nie zapominajmy, że pod RDS kopia zapasowa/przywracanie jest asynchroniczne, dlatego warto ją poczekać:

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} substytutowane z nazwą bazy danych przez powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Drugi sposób, CSV

Tutaj również wszystko jest bardzo proste:

Jednak ten sposób działa tylko wtedy, gdy dane zwracane w CSV są "proste". Jeśli spróbujesz w ten sposób zwrócić na przykład listę TOP N zapytań wymagających dużej mocy CPU, to CSV "się rozpadnie", ponieważ treść zapytania może zawierać różne znaki — przecinki, cudzysłowy, a nawet znaki nowej linii. Dlatego potrzebujemy czegoś bardziej skomplikowanego.

Ładne tabelki w HTML

Podaję od razu fragment kodu

$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>Moja tabela</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Brak danych</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Możesz zauważyć wiersz z System.Management.Automation.PSCustomObject, jest on magiczny, jeśli w siatce jest dokładnie jeden wiersz, mogą pojawić się jakieś problemy. Rozwiązanie wzięte z internetu, specjalnie nie zgłębiając. W rezultacie uzyskasz wynik w formacie mniej więcej takim:

Rysujemy wykresy

Uwaga: poniżej dziwaczny kod!
Jest ciekłe zapytanie na serwer SQL, które wyświetla zużycie CPU za ostatnie N minut — okazuje się, że towarzysz major wszystko zapamiętuje! Spróbuj tego zapytania:

ZADDECLARE @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'%%') AS x) AS y ORDER BY 1 DESC OPTION (RECOMPILE);

Teraz, używając takiego formatowania (zmienna $Fragment)

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

Możemy uformułować treść wiadomości:

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

Które będzie wyglądać tak:

Tak, monsieur zna się na wyzwaniach! Ciekawe, że ten kod zawiera: Powershell (w którym jest napisany), SQL, Xquery, HTML. Szkoda, że do HTML nie możemy dodać Javascript (ponieważ to dla wiadomości), ale dopracowanie kodu Python (który można wykorzystać w SQL) to obowiązek każdego!

Wyjście SQL profiler trace

Jasne, że trace "nie wejdzie" do CSV z powodu pola TextData. Ale wyświetlanie trace w siatce w wiadomości też jest dziwne — zarówno z powodu rozmiaru, jak i dlatego, że często te dane są używane do dalszej analizy. Dlatego robimy następujące: wywołujemy przez invoke-SqlCmd pewien skrypt, w głębi którego wykonuje się

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

Następnie, w przestrzeni nazw z serwerze, dostępnym DBA, istnieje baza Traces z pustym szablonem, tabelą Model, gotową na przyjęcie wszystkich wskazanych kolumn. Kopiujemy ten model do nowej tabeli z unikalną nazwą:

$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

I teraz możemy zapisać w niej nasz trace za pomocą Data.SqlClient.SqlBulkCopy — przykład tego już podawałem wcześniej. Tak, dobrze byłoby również zrobić maskowanie stałych w TextData:

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

Zamieniamy liczby większe niż jeden znak na 999, a ciągi dłuższe niż jeden znak zamieniamy na ‘str’. Liczby od 0 do 9 często są używane jako flagi, więc ich nie zmieniamy, podobnie jak puste i jednoliterowe ciągi — często pojawiają się wśród nich ‘Y’, ‘N’ itp.

Dodajmy koloru do naszego życia (szczególnie 18+)

W tabelach często chcemy wyróżnić komórki wymagające uwagi. Na przykład, FAILS, wysoki poziom fragmentacji itp. Oczywiście, można to zrobić także na ‘czystym’ SQL, generując HTML za pomocą PRINT, a w Jenkinsie ustawić typ pliku na HTML:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Nazwa serwera: ' + @@servername + '</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Zadanie</th><th>Ostatnie uruchomienie</th><th>Średni czas trwania, sek</th><th>Ostatnie uruchomienie, sek</th><th>Ostatni 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<>'Zakończono' then '<font color="red">' else '' end +
      LastStatus +
      case when LastStatus<>'Zakończono' 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>'

Dlaczego napisałem taki kod?

Ale jest ładniejsze rozwiązanie. ConvertTo-HTML nie pozwala nam pokolorować komórek, ale możemy to zrobić retroaktywnie. Na przykład, chcemy wyróżnić komórki z poziomem fragmentacji większym niż 80 i 90. Dodamy style:

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

Do samej kwerendy dodamy fikcyjną kolumnę tuż przed kolumną, którą chcemy pokolorować. Kolumna powinna nazywać się SQLmarkup-coś:

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,

Teraz, mając HTML wygenerowany przez PowerShell, usuniemy fikcyjną kolumnę z nagłówka, a w treści danych przeniesiemy wartość z kolumny do stylu. Robimy to w dwóch prostych podmianach:

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

Wynik:

Czyż nie jest to eleganckie? Choć nie, coś mi ta kolorystyka przypomina

Źródło: habr.com

Automatyzacja SQL server w Jenkins: zwracamy wyniki w przystępny sposób