🥇Jenkinsi SQL-serveri automatiseerimine: tulemuse ilusti tagastamine

Jälle korralduse teema jätkamine Zero Touch PROD RDS-i all. Tulevased DBA-d ei saa PROD-serveritega otse ühendust luua, kuid saavad kasutada Jenkins töökohti piiratud hulga toimingute jaoks. DBA alustab tööd ja saab mõne aja pärast kirja aruandega selle toimingu lõpetamise kohta. Vaatame võimalusi, kuidas neid tulemusi kasutajale esitada.

Plain Text

Alustame kõige triviaalsemast. Esimene meetod on nii lihtne, et tegelikult pole millestki rääkida (autor kasutab edaspidi FreeStyle'i töid):

sqlcmd teeb midagi ja me esitame selle kasutajale. Ideaalne näiteks varundustöödeks:

Muide, ärge unustage, et RDS-i varundamine/taastamine on asünkroonne, seega peate seda ootama:

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

Teine meetod, CSV

Ka siin on kõik väga lihtne:

See meetod töötab aga ainult siis, kui CSV-s tagastatud andmed on "lihtsad". Kui proovite sel viisil tagastada näiteks TOP N protsessorimahukate päringute loendit, siis CSV "korrodeerub" seetõttu, et päringu tekst võib sisaldada mis tahes märke - komasid, jutumärke ja isegi reavahetusi. Seetõttu vajame midagi keerulisemat.

Ilusad märgid HTML-is

Annan teile kohe koodijupi

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

Muide, pöörake tähelepanu reale System.Management.Automation.PSCustomObjectiga, see on maagiline; kui ruudustikus on täpselt üks rida, siis tekkisid probleemid. Lahendus võeti Internetist ilma suurema mõistmiseta. Selle tulemusel vormindatakse väljund umbes järgmiselt:

Graafikute joonistamine

Hoiatus: krussis kood allpool!
SQL-serveris on naljakas päring, mis kuvab viimase N minuti CPU-d – selgub, et seltsimees major mäletab kõike! Proovi seda viktoriini:

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

Nüüd, kasutades seda vormingut (muutuja $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>

Saame moodustada kirja keha:

$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 selline:

Jah, Monsieur teab perverssustest palju! Huvitav on see, et see kood sisaldab: Powershell (sellesse kirjutatud), SQL, Xquery, HTML. Kahju, et me ei saa HTML-ile Javascripti lisada (kuna see on mõeldud kirjutamiseks), kuid Pythoni koodi (mida saab SQL-is kasutada) lihvimine on igaühe kohustus!

SQL-profiili jälgimise väljund

On selge, et jälg ei mahu TextData välja tõttu CSV-sse. Kuid ka jäljeruudustiku kuvamine kirjas on kummaline – nii suuruse tõttu kui ka seetõttu, et neid andmeid kasutatakse sageli edasiseks analüüsiks. Seetõttu teeme järgmist: helistame kaudu invoke-SqlCmd teatud stsenaarium, mille sügavuses seda tehakse

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

Edasi, edasi sõber DBA-le ligipääsetavas serveris on Tracesi andmebaas tühja malliga Model plate, mis on valmis aktsepteerima kõiki määratud veerge. Kopeerime selle mudeli uude unikaalse nimega tabelisse:

$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 oma jälje sellesse kirjutada, kasutades Data.SqlClient.SqlBulkCopy - Olen selle näite juba eespool toonud. Jah, oleks tore ka TextData konstandid maskeerida:

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

Asendame rohkem kui ühe märgi pikkused numbrid 999-ga ja ühest tähemärgist pikemad stringid asendame stringiga. Tihti kasutatakse lippudena numbreid 0-st 9-ni ja neid me ei puuduta, samuti leidub nende hulgas sageli tühje ja ühekohalisi stringe - 'Y', 'N' jne.

Lisame oma ellu värvi (rangelt 18+)

Tabelites soovite sageli esile tõsta tähelepanu nõudvaid lahtreid. Näiteks FAILS, kõrge killustatuse tase jne. Muidugi saab seda teha palja SQL-iga, genereerides HTML-i PRINT-i abil ja määrates Jenkinsis failitüübiks HTML:

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

Miks ma sellise koodi kirjutasin?

Kuid on ilusam lahendus. Teisenda HTML-iks ei lase meil rakke värvida, aga saame seda teha tagantjärele. Näiteks tahame valida lahtreid, mille killustatuse tase on üle 80 ja üle 90. Lisame stiilid:

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

Päringus endas lisame näiva veeru vahetult enne veergu, mida tahame värvida. Kolonni tuleks kutsuda SQL-märgistus-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, olles saanud Powershelli genereeritud HTML-i, eemaldame päisest näiva veeru ja andmete põhiosas kanname väärtuse veerust stiili. Seda tehakse vaid kahe asendusega:

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

Tulemus:

Kas pole elegantne? Kuigi ei, see värvimine meenutab mulle midagi

Allikas: www.habr.com

SQL-serveri automatiseerimine Jenkinsis: tulemuse ilusti tagastamine