🥇SQL serveri automatiseerimine Jenkinsis: toome tulemuse esitlemise kaunilt

Jälle jätkates ruumide korraldamise teemat Zero Touch PROD RDS-i all. Tulevased DBA-d ei saa otse PROD serveritega ühendust luua, kuid nad saavad kasutada Jenkinsile töid piiratud hulga toimingute jaoks. DBA käivitab töö ja mõne aja pärast saab ta aruande selle toimingu täitmise kohta. Vaatame, kuidas neid tulemusi kasutajale esitleda.

Tavaline tekst

Alustame kõige trivialsemast. Esimene viis on nii lihtne, et sellest polegi tegelikult rääkida (autor kasutab siin ja edaspidi FreeStyle töid):

sqlcmd midagi täidab ja me esitame selle kasutajale. Sobib ideaalselt näiteks varukoopiate jaoks:

Ärge unustage, et RDS-i puhul on varukoopia/taastamine asünkroonne, seega oleks mõistlik oodata:

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} asendab Powershelli abil andmebaasi nime
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Teine viis, CSV

Siin on kõik samuti väga lihtne:

Kuid see viis toimib ainult siis, kui CSV-s tagastatavad andmed on "lihtsad". Kui proovite sel viisil tagastada näiteks TOP N CPU ressurssi tarbivate päringute nimekirja, siis CSV „katkeb”, kuna päringu tekst võib sisaldada mis tahes sümboleid — komasid, jutumärke ja isegi reavahetusi. Seetõttu vajame midagi keerukamat.

Ilusad tabelid HTML-is

Tuletan kohe koodilõigu

$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>Minu tabel</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Andmeid ei ole</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Muide, pöörake tähelepanu reale System.Management.Automation.PSCustomObject, see on maagiline, kui ruudus on täpselt üks rida, siis tekkisid mingid probleemid. Lahendus on internetist, eriti ei süvenenud. Tulemusena saate väljundi, mis on kujundatud umbes nii:

Joonistame diagramme

Tähelepanu: allolev kood on perversne!
On olemas naljakas päring SQL-serverile, mis tagastab CPU viimase N minuti jooksul — selgub, et major mäletab kõike! Proovige seda päringut:

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

Nüüd, kasutades sellist vormindamist (muutujaga $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>

Me saame koostada e-kirja sisu:

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

Mis näeb välja nii:

Jah, härra teab, kuidas kummarduda! Huvi pärast, see kood sisaldab: Powershell (milles see on kirjutatud), SQL, Xquery, HTML. Kahju, et HTML-le ei saa lisada Javascripti (kuna see on e-kirja jaoks), kuid täiustada Pythonit (mida saab kasutada SQL-is) on igaühe kohus!

SQL profiler trace väljund

On selge, et trace ei pääse CSV-sse TextData väljaande tõttu. Kuid trace'i väljastamine gridina e-kirjas on samuti veider — ning suuruse ja selle tõttu, et neid andmeid kasutatakse sageli edasiseks analüüsiks. Seetõttu teeme järgmist: kutsume välja invoke-SqlCmd mingi skripti, mille sisemuses tehakse

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

Edasi, DBA-l on olemas Traces andmebaas, millel on tühi mall, tabel Model, mis on valmis vastu võtma kõiki märgitud veerge. Me kopeerime selle malli uude tabelisse, millel on ainulaadne nimi: teises serveris$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

Ja nüüd saame sinna kirjutada meie trace'i abil

Data.SqlClient.SqlBulkCopy — selle näite olen juba eespool toonud. Jah, oleks samuti tore teha TextData konstandte maskeerimine: — näide sellest on juba eespool toodud. Jah, oleks veel hea teha masking konstantide osas TextData:

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

Me asendame enam kui ühe tähemärgi pikkused numbrid väärtusega 999, ja enam kui ühe tähe pikkused stringid asendame 'str' väärtusega. Numbrid vahemikus 0 kuni 9 kasutatakse sageli lipukestena, seega me neid ei muuda, samuti mitte tühje ja ühesümboolseid stringe - nende seas on sageli 'Y', 'N' jne.

Lisame meie ellu värve (ainult 18+)

Tabelites tahaks sageli esile tõsta lahtrid, mis vajavad tähelepanu. Näiteks FAILS, kõrge fragmenteerimise tase jne. Loomulikult on seda võimalik teha ka puhta SQL abil, genereerides HTML-d käsu PRINT abil, ja Jenkinsis seadistades faili tüübiks HTML:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Serveri nimi: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Töö</th><th>Viimane Käivitamine</th><th>Keskmine Kestus, sec</th><th>Viimane Käitamine, Sec</th><th>Viimane Oleku</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>'

Miks ma sellist koodi kirjutasin?

Aga on ka ilusam lahendus. ConvertTo-HTML ei võimalda meil lahtrite värviliseks tegemist, kuid saame seda teha hiljem. Näiteks, me tahame esile tõsta lahtrid, mille fragmenteerimise tase on üle 80 ja 90. Lisame stiilid:

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

Küürisse lisame vale veeru otse enne veergu, mida tahame värvida. Veerg peab nimetama SQLmarkup-midagi:

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,

Nüüd, kui meil on Powershelli loodud HTML, eemaldame pealkirjast vale veeru ja kanname andmete kehas väärtuse veerust stiiliks. Seda tehakse kahe asendamisega:

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

Tulemus:

Pole ju elegantne? Kuigi midagi see värvimine mulle meenutab

Allikas: habr.com

SQL serveri automatiseerimine Jenkinsis: tagastame tulemuse kaunilt