🥇 SQL servera automatizācija Dženkinsā: skaista rezultāta atgriešana

Atkal turpinot aranžējuma tēmu Zero Touch PROD saskaņā ar RDS. Nākotnes DBA nevarēs tieši izveidot savienojumu ar PROD serveriem, bet varēs izmantot Jenkins darba vietas ierobežotam darbību kopumam. DBA uzsāk darbu un pēc kāda laika saņem vēstuli ar ziņojumu par šīs operācijas pabeigšanu. Apskatīsim veidus, kā parādīt šos rezultātus lietotājam.

Vienkāršs teksts

Sāksim ar visnopietnāko. Pirmā metode ir tik vienkārša, ka īsti nav par ko runāt (turpmāk autors izmanto FreeStyle darbus):

sqlcmd kaut ko dara, un mēs to piedāvājam lietotājam. Ideāli piemērots, piemēram, dublēšanas darbiem:

Starp citu, neaizmirstiet, ka RDS dublēšana/atkopšana ir asinhrona, tāpēc jums tas jāgaida:

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

Otrā metode, CSV

Šeit viss ir arī ļoti vienkāršs:

Tomēr šī metode darbojas tikai tad, ja CSV failā atgrieztie dati ir "vienkārši". Ja šādā veidā mēģināsit atgriezt, piemēram, TOP N CPU intensīvi vaicājumu sarakstu, CSV fails “sarūsēs” tādēļ, ka vaicājuma tekstā var būt jebkuras rakstzīmes - komatus, pēdiņas un pat rindiņu pārtraukumus. Tāpēc mums ir nepieciešams kaut kas sarežģītāks.

Skaistas zīmes HTML

Es jums tūlīt iedošu koda fragmentu

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

Starp citu, pievērsiet uzmanību rindai ar System.Management.Automation.PSCustomObject, tas ir maģiski, ja režģī ir tieši viena rinda, tad radās dažas problēmas. Risinājums tika ņemts no interneta bez lielas izpratnes. Rezultātā izvade tiks formatēta apmēram šādi:

Grafiku zīmēšana

Brīdinājums: zemāk redzamais kods!
SQL serverī ir smieklīgs vaicājums, kas parāda CPU pēdējās N minūtes - izrādās, ka biedrs majors visu atceras! Izmēģiniet šo viktorīnu:

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

Tagad, izmantojot šo formatējumu (mainīgais $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>

Mēs varam izveidot vēstules pamattekstu:

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

Kas izskatīsies šādi:

Jā, Monsieur daudz zina par perversijām! Interesanti, ka šis kods satur: Powershell (tajā rakstīts), SQL, Xquery, HTML. Žēl, ka mēs nevaram pievienot Javascript HTML (jo tas ir paredzēts rakstīšanai), bet Python koda (ko var izmantot SQL) slīpēšana ir katra pienākums!

SQL profilētāja izsekošanas izvade

Ir skaidrs, ka teksta datu lauka dēļ trasēšana neietilps CSV failā. Taču arī izsekošanas režģa attēlošana vēstulē ir dīvaina – gan lieluma dēļ, gan tāpēc, ka šie dati bieži tiek izmantoti turpmākai analīzei. Tāpēc mēs rīkojamies šādi: zvanām pa invoke-SqlCmd noteikts scenārijs, kura dziļumos tas tiek darīts

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

Tālāk, tālāk draugs Serverī, kuram var piekļūt DBA, ir Traces datu bāze ar tukšu veidni, modeļa plāksni, kas ir gatava pieņemt visas norādītās kolonnas. Mēs kopējam šo modeli uz jaunu tabulu ar unikālu nosaukumu:

$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

Un tagad mēs varam ierakstīt savu izsekojumu tajā, izmantojot Data.SqlClient.SqlBulkCopy - Es jau minēju piemēru tam iepriekš. Jā, būtu arī jauki maskēt konstantes programmā TextData:

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

Vairāk nekā vienu rakstzīmi garus skaitļus mēs aizstājam ar 999, bet virknes, kas garākas par vienu rakstzīmi, aizstājam ar str. Kā karodziņi bieži tiek izmantoti skaitļi no 0 līdz 9, un mēs tos neaiztiekam, kā arī starp tiem bieži sastopamas tukšas un vienas rakstzīmes virknes - 'Y', 'N' utt.

Piešķirsim savai dzīvei krāsu (stingri 18+)

Tabulās bieži vēlaties izcelt šūnas, kurām jāpievērš uzmanība. Piemēram, FAILS, augsts sadrumstalotības līmenis utt. Protams, to var izdarīt tukšā SQL, ģenerējot HTML, izmantojot PRINT, un iestatot faila tipu uz HTML programmā 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>'

Kāpēc es uzrakstīju šādu kodu?

Bet ir arī skaistāks risinājums. Konvertēt uz HTML neļauj mums krāsot šūnas, bet mēs varam to izdarīt pēc fakta. Piemēram, mēs vēlamies atlasīt šūnas, kuru sadrumstalotības līmenis ir lielāks par 80 un vairāk nekā 90. Pievienosim stilus:

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

Pašā vaicājumā mēs pievienosim fiktīvu kolonnu tieši pirms tam kolonnu, kuru vēlamies krāsot. Kolonna jāsauc SQL iezīmēšana-kaut kas:

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,

Tagad, saņemot Powershell ģenerēto HTML, mēs noņemsim fiktīvo kolonnu no galvenes, un datu pamattekstā mēs pārsūtīsim vērtību no kolonnas uz stilu. Tas tiek darīts tikai ar diviem aizstāšanas gadījumiem:

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

Rezultāts:

Vai nav eleganti? Lai gan nē, šis krāsojums man kaut ko atgādina

Avots: www.habr.com

SQL servera automatizācija Dženkinsā: skaista rezultāta atgriešana