Automatizimi i SQL server në Jenkins: kthejmë rezultatin bukur

Sërish duke vazhduar temën e organizimit Zero Touch PROD nën RDS. DBA-të e ardhshme nuk do të kenë mundësi të lidhen drejtpërdrejt me serverët PROD, por do të mund të përdorin Jenkins punët për një grup të kufizuar operacionesh. DBA-në ekzekuton punën dhe pas një kohe merr një email me raportin mbi përfundimin e kësaj operacioni. Le të shqyrtojmë mënyrat për të paraqitur këto rezultate për përdoruesin.

Teksti i Thjeshtë

Le të fillojmë me atë më triviale. Mënyra e parë është aq e thjeshtë sa nuk ka shumë për të thënë (autori këtu e përmend dhe vazhdimisht përdor punët FreeStyle):

sqlcmd diçka ekzekuton dhe ne e prezantojmë atë për përdoruesin. Përshtatet gjithashtu shumë mirë, për shembull, për punët e backup-it:

Mos harro, për më tepër, që nën RDS backup/restorimi është asnjar, kështu që duhet ta presim atë:

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} zëvendësohet me emrin e databazës nga powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Mënyra e dytë, CSV

Këtu gjithçka është gjithashtu shumë e thjeshtë:

Megjithatë, kjo mënyrë funksionon vetëm, nëse të dhënat që kthehen në CSV janë 'simple'. Nëse provoni të ktheni, për shembull, një listë TOP N të pyetjeve të intensitetit të CPU, atëherë CSV do të 'ndarë' për shkak se teksti i pyetjes mund të përmbajë çdo simbole — presje, citate dhe madje edhe vijat e reja. Prandaj, na nevojitet diçka më komplekse.

Tavolina të bukura në HTML

Do të sjell menjëherë një fragment kodi

$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>Tabela ime</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Nuk ka të dhëna</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Apropo, kushtojini vëmendje rreshtit me System.Management.Automation.PSCustomObject, është magjik, nëse në grid ka vetëm një rresht, atëherë shfaqeshin disa probleme. Zgjidhja është marrë nga interneti pa e kuptuar shumë. Si rezultat, do të merrni një dalje të formatuar njëlloj kështu:

Vizatojmë grafikë

Kujdes: kodi i çmendur më poshtë!
Ka një kërkesë të interesante në SQL server që tregon CPU në minutat e fundit N — del që mik i dashur gjithçka e ruan! Provoni këtë pyetje:

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

Tani, duke përdorur këtë formatim (variabla $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>

Ne mund të formojmë trupin e emailit:

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

Që do të duket kështu:

Po, zotëri e di se çfarë bëhet me devijimet! Është interesante se ky kod përfshin: Powershell (në të është shkruar), SQL, Xquery, HTML. Është e zhgënjyer që nuk mund të shtojmë Javascript në HTML (sepse është për email), por përjashtimi i kodit Python (i cili mund të përdoret në SQL) është detyrë e çdo njeriu!

Dalja nga SQL profiler trace

Është e qartë se trace "nuk do të kalojë" në CSV për shkak të fushës TextData. Por gjithashtu është e çuditshme që ta tregojmë trace-in në grid në email — dhe për shkak të madhësisë, dhe sepse këto të dhëna shpesh përdoren për analiza të mëtejshme. Prandaj, ne bëjmë si më poshtë: thërrasim nëpërmjet invoke-SqlCmd një skript, në thellësitë e së cilës bëhet

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

Pastaj, në tjetër сервере, e cila është në dispozicion të DBA, ekziston një bazë Traces me një shabllon bosh, një tabelë Model, e gatshme për të pranuar të gjitha kolonat e shënuara. Ne e kopjojmë këtë model në një tabelë të re me një emër unik:

$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 

Dhe tani mund të shkruajmë trace-in tonë me ndihmën e Data.SqlClient.SqlBulkCopy — një shembull i kësaj unë kam dhënë më lart. Po, gjithashtu do të ishte mirë të bëni masking të konstantave në TextData:

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

Ne zëvendësojmë numrat me më shumë se një shenjë gjatësi me 999, ndërsa vargjet më të gjata se një shenjë i zëvendësojmë me 'str'. Numrat nga 0 në 9 përdoren shpesh si flamuj, dhe ne nuk i prekim ato, ashtu si dhe vargjet bosh dhe ato me një shenjë – ndër to shpesh hasen 'Y', 'N', etj.

Shtojmë ngjyra në jetën tonë (rreptësisht 18+)

Në tabela shpesh dëshirohet të theksohen qelizat që kërkojnë vëmendje. Për shembull, DËMET, niveli i lartë i fraksionimit etj. Sigurisht, kjo mund të bëhet edhe me SQL të pastër, duke formuar HTML me ndihmën e PRINT, dhe në Jenkins të vendoset lloji i skedarit HTML:

deklaro @body varchar(max), @chunk varchar(max)
vendos @body='<font face="Lucida Console" size="3">'
vendos @body=@body+'<b>Emri i serverit: '+@@servername+'</b><br>'
vendos @body=@body+'<br><br>'
vendos @body=@body+'<table><tr><th>Job</th><th>Ekzekutimi i fundit</th><th>Kohëzgjatja mesatare, sekonda</th><th>Ekzekutimi i fundit, sekondat</th><th>Statusi i fundit</th></tr>'
print @body

SHKRUANI tab CURSOR PËR ZGJIDHEN '<tr><td>'+emri+'</td><td>'+
  EkzekutimiIDH.lastRun+'</td><td>'+
  konvert(VARCHAR,EAvgDuration)+'</td><td>'+
  konvert(VARCHAR,ELastDuration)+'</td><td>'+
    rast kur StatusiIDH.lastStatus<>'Sukses' atëherë '<font color="red">' përndryshe '' fund+
      StatusiIDH.lastStatus+
      rast kur StatusiIDH.lastStatus<>'Sukses' atëherë '</font>' përndryshe '' fund+
     +'</td><td>'
  nga #j2
HAP tab;
MERR NËNEXT NGA tab në @chunk
NDJETS STATUSI= 0
FILLIM
  print @chunk
  MERR NËNEXT NGA tab në @chunk;
KETU
Mbyll tab;
DEALLOK TAB;
print '</table>'

Pse shkrova një kod të tillë?

Por ka një zgjidhje më të bukur. ConvertTo-HTML nuk na lejon të kolorojmë qelizat, por mund ta bëjmë këtë pas faktit. Për shembull, duam të theksojmë qelizat me nivel fraksionimi më shumë se 80 dhe 90. Shtojmë stile:

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

Në vetë kërkesën do të shtojmë një kolonë fiktive direkt para kolonës që duam të kolorojmë. Kolona duhet të quhet SQLmarkup-diçka:

rast  
  kur ps.avg_fragmentation_in_percent>=90.0 atëherë 'SQLmarkup-red'
  kur ps.avg_fragmentation_in_percent>=80.0 atëherë 'SQLmarkup-yellow'
  përndryshe 'SQLmarkup-default' 
  përfundoni si [SQLmarkup-1], 
ps.avg_fragmentation_in_percent, 

Tani, pasi kemi marrë HTML-në e krijuar nga Powershell, do të heqim kolonën fiktive nga titulli, dhe në trupin e të dhënave do ta transferojmë vlerën nga kolona në stil. Kjo bëhet me dy zëvendësime:

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

Rezultati:


A nuk është e vërtetë, elegante? Megjithatë, ka diçka që më kujton këtë kolorim


Burimi: habr.com