🥇 Automatizimi i serverit SQL në Jenkins: kthimi i bukur i rezultatit

Përsëri duke vazhduar temën e rregullimit Zero Touch PROD nën RDS. DBA-të e ardhshme nuk do të jenë në gjendje të lidhen drejtpërdrejt me serverët PROD, por do të jenë në gjendje të përdorin Jenkins punë për një grup të kufizuar operacionesh. DBA nis punën dhe pas disa kohësh merr një letër me një raport për përfundimin e këtij operacioni. Le të shohim mënyrat për t'i paraqitur këto rezultate përdoruesit.

Tekst i thjeshtë

Le të fillojmë me më të parëndësishmet. Metoda e parë është aq e thjeshtë sa nuk ka asgjë për të folur (autori në vijim përdor punët FreeStyle):

sqlcmd bën diçka dhe ne e prezantojmë atë tek përdoruesi. Ideale për, për shembull, punë rezervë:

Mos harroni, meqë ra fjala, se rezervimi/rivendosja e RDS është asinkron, kështu që duhet ta prisni:

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

Metoda e dytë, CSV

Gjithçka këtu është gjithashtu shumë e thjeshtë:

Megjithatë, kjo metodë funksionon vetëm nëse të dhënat e kthyera në CSV janë "të thjeshta". Nëse përpiqeni të ktheni, për shembull, një listë të pyetjeve intensive të CPU-së TOP N në këtë mënyrë, CSV do të "gërryhet" për shkak të faktit se teksti i pyetjes mund të përmbajë çdo karakter - presje, thonjëza dhe madje edhe ndërprerje rreshtash. Prandaj, na duhet diçka më e ndërlikuar.

Shenja të bukura në HTML

Unë do t'ju jap menjëherë një copë 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>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
  }

Nga rruga, kushtojini vëmendje linjës me System.Management.Automation.PSCustomObject, është magjike; nëse ka saktësisht një rresht në rrjet, atëherë u shfaqën disa probleme. Zgjidhja është marrë nga interneti pa shumë kuptim. Si rezultat, ju do të merrni formatimin e daljes diçka si kjo:

Vizatimi i grafikëve

Kujdes: kodi i çuditshëm më poshtë!
Ekziston një pyetje qesharake në serverin SQL që shfaq CPU-në për N minutat e fundit - rezulton se shoku Major mban mend gjithçka! Provoni këtë kuiz:

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

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

Mund të formojmë trupin e letrës:

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

E cila do të duket kështu:

Po, zotëri di shumë për perversionet! Është interesante që ky kod përmban: Powershell (i shkruar në të), SQL, Xquery, HTML. Është për të ardhur keq që nuk mund të shtojmë Javascript në HTML (pasi është për shkrim), por lustrimi i kodit Python (i cili mund të përdoret në SQL) është detyrë e të gjithëve!

Prodhimi i gjurmës së profilit SQL

Është e qartë se gjurma nuk do të futet në CSV për shkak të fushës TextData. Por shfaqja e një rrjeti gjurmësh në një letër është gjithashtu e çuditshme - si për shkak të madhësisë ashtu edhe për shkak se këto të dhëna shpesh përdoren për analiza të mëtejshme. Prandaj, ne bëjmë sa më poshtë: ne telefonojmë nëpërmjet thirr-SqlCmd një skenar i caktuar, në thellësi të të cilit është bërë

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

Më tej, në shoku Në serverin e aksesueshëm nga DBA, ekziston një bazë të dhënash Traces me një shabllon bosh, pllakën Model, e gatshme për të pranuar të gjitha kolonat e specifikuara. 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 ne mund të shkruajmë gjurmën tonë në të duke përdorur Data.SqlClient.SqlBulkCopy - Unë kam dhënë tashmë një shembull për këtë më lart. Po, do të ishte gjithashtu mirë të maskoheshin konstantet 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 më të gjatë se një karakter me 999 dhe vargjet më të gjata se një karakter i zëvendësojmë me 'str'. Numrat nga 0 deri në 9 përdoren shpesh si flamuj, dhe ne nuk i prekim, si dhe vargjet boshe dhe me një karakter - 'Y', 'N', etj., shpesh gjenden midis tyre.

Le t'i shtojmë pak ngjyra jetës sonë (rreptësisht 18+)

Në tabela, shpesh dëshironi të nënvizoni qelizat që kërkojnë vëmendje. Për shembull, FAILS, niveli i lartë i fragmentimit, etj. Sigurisht, kjo mund të bëhet në SQL të zhveshur, duke gjeneruar HTML duke përdorur PRINT dhe duke vendosur llojin e skedarit në HTML në Jenkins:

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

Pse shkrova një kod të tillë?

Por ka një zgjidhje më të bukur. ConvertTo-HTML nuk na lejon t'i ngjyrosim qelizat, por mund ta bëjmë pas faktit. Për shembull, ne duam të zgjedhim qeliza me një nivel fragmentimi prej më shumë se 80 dhe më shumë se 90. Le të shtojmë stilet:

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

Në vetë pyetjen ne do të shtojmë një kolonë bedel menjëherë përpara kolona që duam të ngjyrosim. Kolona duhet të thirret SQLmarkup-diçka:

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,

Tani, pasi kemi marrë HTML-në e gjeneruar nga Powershell, ne do të heqim kolonën dummy nga kreu, dhe në trupin e të dhënave do të transferojmë vlerën nga kolona në stil. Kjo bëhet me vetëm dy zëvendësime:

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

Rezultati:

A nuk është elegante? Edhe pse jo, kjo ngjyrosje më kujton diçka

Burimi: www.habr.com

Automatizimi i serverit SQL në Jenkins: kthimi i bukur i rezultatit