🥇Automatizacija SQL servera u Jenkinsu: prelijepo vraćanje rezultata

Opet nastavljajući temu aranžmana Zero Touch PROD pod RDS. Budući DBA neće moći direktno da se povežu na PROD servere, ali će moći da ih koriste Jenkins poslovi za ograničen skup operacija. DBA pokreće posao i nakon nekog vremena dobija pismo sa izvještajem o završetku ove operacije. Pogledajmo načine kako da ove rezultate predstavimo korisniku.

Običan tekst

Počnimo s najtrivijalnijim. Prva metoda je toliko jednostavna da se zapravo nema o čemu pričati (autor u nastavku koristi FreeStyle poslove):

sqlcmd radi nešto i mi to predstavljamo korisniku. Idealno za, na primjer, backup poslove:

Ne zaboravite, usput, da je pod RDS backup/restore asinkrono, tako da morate pričekati na to:

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

Druga metoda, CSV

Ovde je takođe sve veoma jednostavno:

Međutim, ova metoda funkcionira samo ako su podaci vraćeni u CSV-u "jednostavni". Ako pokušate da vratite, na primjer, listu TOP N CPU intenzivnih upita na ovaj način, CSV će se "nagrizati" zbog činjenice da tekst upita može sadržavati bilo koje znakove - zareze, navodnike, pa čak i prijelome reda. Stoga nam treba nešto komplikovanije.

Prekrasni znakovi u HTML-u

Odmah ću vam dati isječak koda

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

Usput, obratite pažnju na liniju sa System.Management.Automation.PSCustomObject, to je magično, ako postoji tačno jedna linija u mreži, onda su se pojavili neki problemi. Rješenje je preuzeto sa interneta bez puno razumijevanja. Kao rezultat, dobit ćete izlaz formatiran otprilike ovako:

Crtanje grafova

Upozorenje: kinky kod ispod!
Postoji smiješan upit na SQL serveru koji prikazuje CPU u posljednjih N minuta - ispostavilo se da drug Major pamti sve! Isprobajte ovaj kviz:

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

Sada, koristeći ovo formatiranje ($Fragment varijabla)

<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žemo formirati tijelo pisma:

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

Što će izgledati ovako:

Da, gospodin zna mnogo o perverzijama! Zanimljivo je da ovaj kod sadrži: Powershell (napisan u njemu), SQL, Xquery, HTML. Šteta što ne možemo dodati Javascript u HTML (pošto je za pisanje), ali poliranje Python koda (koji se može koristiti u SQL-u) je svačija dužnost!

Izlaz traga SQL profilera

Jasno je da se trag neće uklopiti u CSV zbog polja TextData. Ali prikazivanje mreže tragova u slovu je također čudno - i zbog veličine i zbog toga što se ovi podaci često koriste za dalju analizu. Stoga radimo sljedeće: zovemo putem invoke-SqlCmd određeni scenario u čijoj dubini se to radi

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

Dalje, dalje prijatelju Na serveru kojem DBA pristupa, postoji baza podataka Traces sa praznim predloškom, Model ploča, spremna da prihvati sve navedene kolone. Ovaj model kopiramo u novu tablicu s jedinstvenim imenom:

$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 sada možemo zapisati svoj trag u njega koristeći Data.SqlClient.SqlBulkCopy - Već sam gore naveo primjer ovoga. Da, također bi bilo lijepo maskirati konstante u TextData:

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

Brojeve dužine više od jednog znaka zamjenjujemo sa 999, a nizove duži od jednog znaka zamjenjujemo sa 'str'. Brojevi od 0 do 9 se često koriste kao zastavice i ne diramo ih, a među njima se često nalaze prazni i jednoznačni nizovi - 'Y', 'N' itd.

Hajde da dodamo malo boje našim životima (strogo 18+)

U tabelama često želite da istaknete ćelije koje zahtevaju pažnju. Na primjer, NEUSPEH, visok nivo fragmentacije, itd. Naravno, ovo se može uraditi u golom SQL-u, generišući HTML koristeći PRINT i postavljajući tip datoteke na HTML u Jenkinsu:

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

Zašto sam napisao takav kod?

Ali postoji ljepše rješenje. Pretvori u HTML ne dozvoljava nam da bojimo ćelije, ali to možemo učiniti naknadno. Na primjer, želimo odabrati ćelije sa nivoom fragmentacije većim od 80 i više od 90. Dodajmo stilove:

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

U sam upit ćemo dodati lažnu kolonu neposredno prije kolonu koju želimo da obojimo. Kolona treba biti pozvana SQLmarkup-nešto:

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,

Sada, nakon što smo primili HTML generiran od strane Powershell-a, uklonit ćemo lažnu kolonu iz zaglavlja, a u tijelu podataka ćemo prenijeti vrijednost iz kolone u stil. Ovo se radi sa samo dvije zamjene:

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

Rezultat:

Zar nije elegantno? Iako ne, ova boja me na nešto podsjeća

izvor: www.habr.com

Automatizacija SQL servera u Jenkinsu: prelijepo vraćanje rezultata