🥇Automatisering af SQL-server i Jenkins: præsenterer resultatet smukt

Igen fortsætte emnet om indretning Zero Touch PROD til RDS. Fremtidige DBA'er vil ikke have mulighed for at oprette forbindelse til PROD-servere direkte, men vil kunne bruge Jenkins jobs til et begrænset sæt operationer. DBA'en starter en job, og efter et stykke tid modtager han en e-mail med rapporten om udførelsen af denne operation. Lad os se på måder at præsentere disse resultater for brugeren.

Plain Text

Lad os starte med det mest banale. Den første metode er så simpel, at der egentlig ikke er meget at sige (forfatteren bruger her og fremover FreeStyle jobs):

sqlcmd noget udfører, og vi præsenterer det for brugeren. Det er ideelt til for eksempel backup jobs:

Lad os ikke glemme, at backup/restore under RDS er asynkron, så vi skal vente på det:

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} erstattes med db-navn af powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Den anden metode, CSV

Her er også alt meget simpelt:

Men denne metode fungerer kun, hvis de data, der returneres i CSV, er "simple". Hvis du forsøger at returnere en liste over TOP N CPU-intensive forespørgsler på denne måde, så vil CSV 'løbe løbsk', fordi tekstforspørgslen kan indeholde alle slags tegn - kommaer, citationstegn og endda linjeskift. Derfor får vi brug for noget mere kompliceret.

Smukke tabeller i HTML

Lad mig straks præsentere et kodeuddrag

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

Forresten, læg mærke til linjen med System.Management.Automation.PSCustomObject, den er magisk, hvis der er præcis én række i gridet, så opstod der nogle problemer. Løsningen blev fundet på internettet uden nærmere eftertænksomhed. Som resultat får du output, der er formateret sådan:

Tegner grafer

Bemærk: den perverse kode nedenfor!
Der er en sjov forespørgsel på SQL-serveren, som viser CPU'en de seneste N minutter - det viser sig, at majoren husker alt! Prøv denne forespørgsel:

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

Nu, ved hjælp af denne formatering (variablen $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>

Kan vi danne brødteksten til e-mailen:

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

Som ser således ud:

Ja, monsieur kender virkelig til afvigelser! Det er interessant, at denne kode indeholder: Powershell (som den er skrevet i), SQL, Xquery, HTML. Det er en skam, at vi ikke kan tilføje Javascript til HTML (da dette er til en e-mail), men at perfektionere Python-koden (som kan bruges i SQL) er en pligt!

Output fra SQL profiler trace

Det er klart, at trace ikke vil kunne gå ind i CSV på grund af feltet TextData. Men at vise trace i grid i e-mailen er også mærkeligt — både på grund af størrelsen og fordi disse data ofte bruges til yderligere analyse. Derfor gør vi følgende: vi kaldet gennem invoke-SqlCmd et eller andet script, i dybet af hvilket der udføres

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

Dernæst, på en anden serveren, tilgængelig for DBA, eksisterer der en database Traces med en tom skabelon, tabel Model, klar til at modtage alle de angivne kolonner. Vi kopierer denne model til en ny tabel med et unikt navn:

$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

Og nu kan vi skrive vores trace ind i den ved hjælp af Data.SqlClient.SqlBulkCopy — et eksempel på dette har jeg allerede givet ovenfor. Ja, det ville også være godt at lave masking af konstanter i TextData:

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

Vi erstatter tal, der er længere end ét ciffer, med 999, og strenge, der er længere end ét tegn, erstatter vi med 'str'. Tal fra 0 til 9 bruges ofte som flag, og vi rører ikke ved dem, ligesom vi ikke ændrer tomme og enstrenget tekst – blandt dem ses ofte 'Y', 'N' osv.

Lad os tilføje farver til vores liv (kun 18+)

I tabeller ønsker vi ofte at fremhæve celler, der kræver opmærksomhed. For eksempel, FAILS, høj fragmenteringsgrad osv. Selvfølgelig kan dette gøres med ren SQL ved at generere HTML ved hjælp af PRINT, og i Jenkins sætte filtypen til HTML:

deklarér @body varchar(max), @chunk varchar(max)
indstil @body='<font face="Lucida Console" size="3">'
indstil @body=@body+'<b>Servernavn: '+@@servername+'</b><br>'
indstil @body=@body+'<br><br>'
indstil @body=@body+'<table><tr><th>Job</th><th>Sidste kørsel</th><th>Gennemsnitlig varighed, sek</th><th>Sidste kørsel, sek</th><th>Sidste status</th></tr>'
print @body

DEKLARÉ tab CURSOR FOR VÆLG '<tr><td>'+name+'</td><td>'+
  LastRun+'</td><td>'+
  convert(varchar,Gennemsnitsvarighed)+'</td><td>'+
  convert(varchar,SidsteVarighed)+'</td><td>'+
    case when SidsteStatus<>'Udført' så '<font color="red">' ellers '' slut+
      SidsteStatus+
      case when SidsteStatus<>'Udført' så '</font>' ellers '' slut+
     +'</td><td>'
  fra #j2
ÅBN tab;  
HENT NÆSTE FRA tab ind i @chunk
HVILKE @@FETCH_STATUS = 0  
BEGIN
  print @chunk
  HENT NÆSTE FRA tab ind i @chunk;  
SLUT  
LUK tab;  
FRIGIV tab;
print '</table>'

Hvorfor skrev jeg sådan en kode?

Men der er en smukkere løsning. ConvertTo-HTML giver os ikke mulighed for at farve celler, men vi kan gøre det efterfølgende. For eksempel, vi ønsker at fremhæve celler med en fragmenteringsgrad over 80 og 90. Vi tilføjer stilarter:

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

I selve forespørgslen tilføjer vi en fiktiv kolonne direkte før den kolonne, vi ønsker at farve. Kolonnen skal hedde SQLmarkup-noget:

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,

Nu, når vi har HTML genereret af Powershell, fjerner vi den fiktive kolonne fra overskriften, og i datakroppen flytter vi værdien fra kolonnen til stilen. Dette gøres på blot to substitutioner:

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

Resultat:

Er det ikke elegant? Selvom, vent lidt, noget ved den farvning minder mig om

Kilde: habr.com

Automatisering af SQL server i Jenkins: returnerer resultatet flot