🥇Automatisering van SQL-server in Jenkins: het resultaat prachtig weergeven

opnieuw het thema arrangement voortzetten Zero Touch PROD onder RDS. Toekomstige DBA's zullen niet rechtstreeks verbinding kunnen maken met PROD-servers, maar kunnen deze wel gebruiken Jenkins banen voor een beperkt aantal activiteiten. De DBA lanceert de klus en ontvangt na enige tijd een brief met een rapport over de voltooiing van deze operatie. Laten we kijken naar manieren om deze resultaten aan de gebruiker te presenteren.

Plain Text

Laten we beginnen met het meest triviale. De eerste methode is zo eenvoudig dat er eigenlijk niets is om over te praten (de auteur gebruikt hierna FreeStyle-jobs):

sqlcmd doet iets en wij presenteren het aan de gebruiker. Ideaal voor bijvoorbeeld back-upklussen:

Vergeet trouwens niet dat back-up/herstel onder RDS asynchroon is, dus je moet erop wachten:

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

Tweede methode, CSV

Alles is hier ook heel eenvoudig:

Deze methode werkt echter alleen als de gegevens die in het CSV worden geretourneerd "eenvoudig" zijn. Als u op deze manier bijvoorbeeld een lijst met TOP N CPU-intensieve zoekopdrachten probeert terug te geven, zal de CSV "corroderen" vanwege het feit dat de zoekopdrachttekst alle tekens kan bevatten: komma's, aanhalingstekens en zelfs regeleinden. Daarom hebben we iets ingewikkelders nodig.

Mooie borden in HTML

Ik geef je meteen een codefragment

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

Let trouwens op de regel met System.Management.Automation.PSCustomObject, het is magisch: als er precies één regel in het raster staat, zijn er enkele problemen ontstaan. De oplossing werd zonder veel begrip van internet gehaald. Als gevolg hiervan krijgt u een uitvoer die ongeveer zo is geformatteerd:

Grafieken tekenen

Waarschuwing: kinky code hieronder!
Er is een grappige vraag op de SQL-server die de CPU van de afgelopen N minuten weergeeft - het blijkt dat kameraad-majoor alles onthoudt! Probeer deze quiz:

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

Gebruik nu deze opmaak ($Fragment-variabele)

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

We kunnen de hoofdtekst van de brief vormen:

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

Wat er als volgt uit zal zien:

Ja, Monsieur weet veel over perversies! Het is interessant dat deze code het volgende bevat: Powershell (erin geschreven), SQL, Xquery, HTML. Het is jammer dat we geen Javascript aan HTML kunnen toevoegen (omdat het om te schrijven is), maar het oppoetsen van Python-code (die in SQL kan worden gebruikt) is de plicht van iedereen!

Traceringsuitvoer van SQL-profiler

Het is duidelijk dat de trace niet in het CSV past vanwege het TextData-veld. Maar het weergeven van een traceerraster in een brief is ook vreemd – zowel vanwege de grootte als omdat deze gegevens vaak worden gebruikt voor verdere analyse. Daarom doen wij het volgende: wij bellen via aanroepen-SqlCmd een bepaald script, in de diepte waarvan het wordt gedaan

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

Volgende vriend Op de server die toegankelijk is voor de DBA, bevindt zich een Traces-database met een leeg sjabloon, het Modelplaatje, klaar om alle opgegeven kolommen te accepteren. We kopiëren dit model naar een nieuwe tabel met een unieke naam:

$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

En nu kunnen we ons spoor erin schrijven met behulp van Data.SqlClient.SqlBulkCopy - Ik heb hierboven al een voorbeeld gegeven. Ja, het zou ook leuk zijn om constanten in TextData te maskeren:

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

We vervangen getallen die langer zijn dan één teken door 999, en strings die langer zijn dan één teken vervangen we door 'str'. Getallen van 0 tot 9 worden vaak gebruikt als vlaggen, en we raken ze niet aan, evenals lege reeksen van één teken - 'Y', 'N', enz. Worden vaak onder hen aangetroffen.

Laten we wat kleur toevoegen aan ons leven (uitsluitend 18+)

In tabellen wilt u vaak cellen markeren die aandacht vereisen. Bijvoorbeeld FAILS, hoge mate van fragmentatie, enz. Dit kan natuurlijk worden gedaan in kale SQL, HTML genereren met PRINT en het bestandstype instellen op HTML in 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>'

Waarom heb ik zulke code geschreven?

Maar er is een mooiere oplossing. Converteren naar HTML laat ons de cellen niet kleuren, maar we kunnen het wel achteraf doen. We willen bijvoorbeeld cellen selecteren met een fragmentatieniveau van meer dan 80 en meer dan 90. Laten we stijlen toevoegen:

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

In de query zelf voegen we een dummykolom toe onmiddellijk ervoor kolom die we willen kleuren. De kolom moet worden aangeroepen SQL-opmaak-iets:

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 we de door Powershell gegenereerde HTML hebben ontvangen, zullen we de dummy-kolom uit de kop verwijderen en in de hoofdtekst van de gegevens zullen we de waarde van de kolom naar de stijl overbrengen. Dit gebeurt met slechts twee vervangingen:

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

Resultaat:

Is het niet elegant? Hoewel nee, deze kleuring doet me ergens aan denken

Bron: www.habr.com

Automatisering van SQL-server in Jenkins: het resultaat prachtig retourneren