🥇SQL server automatizálás Jenkins-ben: a szép eredmény visszahozatala

Újra folytatva a témát a beállításokról Zero Touch PROD RDS alatt. A jövőbeli DBA-k nem férhetnek hozzá a PROD szerverekhez közvetlenül, de használhatják Jenkins a job-okat korlátozott műveletekhez. A DBA elindít egy job-ot, és egy idő után levelet kap a művelet eredményéről. Nézzük meg a felhasználónak történő prezentálás módjait.

Sima szöveg

Kezdjük a legtriviálisabbal. Az első módszer annyira egyszerű, hogy igazából nem is érdemes róla beszélni (a szerző itt és tovább egyaránt FreeStyle job-okat használ):

sqlcmd valami végrehajt, és ezt prezentáljuk a felhasználónak. Ideális például a backup job-okhoz:

Ne felejtsük el, hogy az RDS alatt a backup/restore aszinkron, ezért meg kell várni:

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} helyére az adatbázis neve kerül be powershell által
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

A második módszer, CSV

Itt is minden nagyon egyszerű:

Ez a módszer azonban csak akkor működik, ha a CSV-ben visszaadott adatok „egyszerűek”. Ha megpróbálsz például egy TOP N CPU-intenzív lekérdezés listát visszaadni, a CSV „megbomlik”, mert a lekérdezés szövege bármilyen karaktert tartalmazhat — vesszőket, idézőjeleket és még sortöréseket is. Ezért valami bonyolultabbra lesz szükségünk.

Szép táblázatok HTML-en

Azonnal bemutatok egy kódot

$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>A táblám</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Nincs adat</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Egyébként figyelj oda a System.Management.Automation.PSCustomObject sorra, ez varázslatos, ha a gridben pontosan egy sor van, akkor valami probléma léphet fel. A megoldás az internetről származik, különösebben nem elemezve. Ennek eredményeként körülbelül így fog kinézni a kimenet:

Grafikonok rajzolása

Figyelem: a lenti furcsa kód!
Van egy érdekes lekérdezés az SQL szerveren, amely az utolsó N perc CPU-t adja meg — kiderül, hogy a barátom egyes dolgokat mind megjegyez! Próbáld ki ezt a lekérdezést:

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

Most ilyen formázással (változó $Fragment) használva

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

Meg tudjuk alkotni a levél törzsét:

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

Ami így fog kinézni:

Igen, a mester tudja, mi fán terem a perverzió! Érdekes, hogy ezt a kódot tartalmazza: Powershell (amin íródott), SQL, Xquery, HTML. Kár, hogy HTML-hez nem adhattunk Javascriptet (mivel ez levélhez készült), de a Python kód kifinomítását (melyet SQL-ben lehet használni) mindenki kötelessége!

SQL profiler trace kimenet

Nyilvánvaló, hogy a trace nem kerül CSV-be a TextData mező miatt. De a trace gridben történő megjelenítés is furcsa levélben — a mérete miatt, és mert ezeket az adatokat gyakran további elemzéshez használják. Tehát a következőt tesszük: meghívjuk a invoke-SqlCmd valamilyen szkriptet, aminek mélyén a következő történik

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

Ezután a egy barátommal szerveren, hozzáférhető DBA számára, létezik egy Traces adatbázis üres sablonnal, a Model táblával, amely készen áll az összes megadott oszlop befogadására. Ezt a modellt lemásoljuk egy új táblába egyedi névvel:

$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

És most már bele tudjuk írni a trace-t a Data.SqlClient.SqlBulkCopy — ennek a példája korábban már szerepelt. Igen, jól lenne még a TextData-ban a konstansok maszkolását is elvégezni:

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

A számokat, amelyek több mint egy karakter hosszúak, 999-re cseréljük, és az egy karakter hosszú szövegeket ‘str’-re cseréljük. A 0-tól 9-ig terjedő számokat gyakran használják jelzőkként, ezeket nem érintjük, valamint az üres és egy karakterből álló szövegeket is — ezek között gyakran előfordulnak az ‘Y’, ‘N’ stb.

Színt viszünk az életünkbe (szigorúan 18+)

A táblázatokban gyakran szükség van arra, hogy kiemeljük azokat a cellákat, amelyek figyelmet igényelnek. Például: FAILS, magas töredezés szint stb. Természetesen ezt meg lehet csinálni nyers SQL formázásával HTML-t a PRINT segítségével, és a Jenkinsnél HTML fájltípus hozzáadásával:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Kiszolgáló név: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Feladat</th><th>Utolsó futás</th><th>Átlagos időtartam, másodperc</th><th>Utolsó futás, másodperc</th><th>Utolsó állapot</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<>'Sikerült' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Sikerült' 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>'

Miért írtam ezt a kódot?

De van egy szebb megoldás is. ConvertTo-HTML nem adja lehetővé, hogy színezzük a cellákat, de ezt utólag megtehetjük. Például ki akarjuk emelni azokat a cellákat, amelyek töredezési szintje 80 vagy 90 feletti. Adjunk hozzá stílusokat:

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

A lekérdezésben egy fiktív oszlopot adunk hozzá közvetlenül az oszlop előtt, amelyet ki szeretnénk emelni. Az oszlopnak a következő neve legyen SQLmarkup-valami:

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,

Most, hogy megkaptuk a Powershell által létrehozott HTML-t, eltávolítjuk a fiktív oszlopot a címkéből, és az adatok törzsében áthelyezzük az értéket az oszlopból a stílusba. Ezt mindössze két helyettesítéssel végezzük el:

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

Eredmény:

Igaz, hogy elegáns? Bár nem, van valami, ami emlékeztet erre a színezésre

Forrás: habr.com

SQL server automatizálás Jenkinsben: a megfelelő eredmény visszaadása