Automatisering av SQL-server i Jenkins: returnerar resultatet vackert

igen fortsätter på temat arrangemang Zero Touch PROD under RDS. Framtida DBA:er kommer inte att kunna ansluta till PROD-servrar direkt, men kommer att kunna använda Jenkins jobb för en begränsad uppsättning verksamheter. DBA startar jobbet och får efter en tid ett brev med en rapport om slutförandet av denna operation. Låt oss titta på sätt att presentera dessa resultat för användaren.

Plain Text

Låt oss börja med det mest triviala. Den första metoden är så enkel att det egentligen inte finns något att prata om (författaren använder hädanefter FreeStyle-jobb):

sqlcmd gör något och vi presenterar det för användaren. Idealisk för till exempel backupjobb:

Glöm förresten inte att säkerhetskopiering/återställning under RDS är asynkron, så du måste vänta 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} 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

Andra metoden, CSV

Allt här är också väldigt enkelt:

Den här metoden fungerar dock bara om data som returneras i CSV:en är "enkla". Om du försöker returnera, till exempel, en lista över TOP N CPU-intensiva frågor på detta sätt, kommer CSV:en att "korrodera" på grund av att frågetexten kan innehålla vilka tecken som helst - kommatecken, citattecken och till och med radbrytningar. Därför behöver vi något mer komplicerat.

Vackra skyltar i HTML

Jag ger dig ett kodavsnitt direkt

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

Förresten, var uppmärksam på raden med System.Management.Automation.PSCustomObject, den är magisk; om det finns exakt en rad i rutnätet så uppstod några problem. Lösningen hämtades från Internet utan större förståelse. Som ett resultat kommer du att få utdata formaterad ungefär så här:

Rita grafer

Varning: kinky kod nedan!
Det finns en rolig fråga på SQL-servern som visar CPU de sista N minuterna - det visar sig att kamrat Major kommer ihåg allt! Testa detta frågesport:

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

Använd nu denna formatering ($Fragment-variabel)

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

Vi kan bilda brevets kropp:

$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 kommer att se ut så här:

Ja, Monsieur vet mycket om perversioner! Det är intressant att den här koden innehåller: Powershell (skriven i den), SQL, Xquery, HTML. Det är synd att vi inte kan lägga till Javascript i HTML (eftersom det är för att skriva), men att polera Python-kod (som kan användas i SQL) är allas skyldighet!

SQL profiler spåra utdata

Det är tydligt att spåret inte kommer att passa in i CSV-filen på grund av TextData-fältet. Men att visa ett spårrutnät i en bokstav är också konstigt – både på grund av storleken och för att dessa data ofta används för vidare analys. Därför gör vi följande: vi ringer via invoke-SqlCmd ett visst manus, i vars djup det är gjort

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

Nästa, på andra server, tillgänglig för databasadministratören, finns en Traces-databas med en tom mall, en modelltabell, redo att acceptera alla angivna kolumner. Vi kopierar denna modell till en ny tabell med ett unikt namn:

$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 

Och nu kan vi skriva in vårt spår i den med hjälp av Data.SqlClient.SqlBulkCopy – Jag har redan gett ett exempel på detta ovan. Ja, det skulle också vara trevligt att maskera 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 ersätter siffror som är längre än ett tecken med 999, och vi ersätter strängar som är längre än ett tecken med 'str'. Siffror från 0 till 9 används ofta som flaggor, och vi rör dem inte, liksom tomma och enkaraktärssträngar - 'Y', 'N', etc. finns ofta bland dem.

Låt oss lägga lite färg på våra liv (strängt 18+)

I tabeller vill man ofta markera celler som kräver uppmärksamhet. Till exempel FAILS, hög grad av fragmentering, etc. Naturligtvis kan detta göras i ren SQL, generera HTML med PRINT och ställa in filtypen till HTML i 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>'

Varför skrev jag en sådan kod?

Men det finns en vackrare lösning. Konvertera till HTML låter oss inte färga cellerna, men vi kan göra det i efterhand. Till exempel vill vi välja celler med en fragmenteringsnivå på mer än 80 och mer än 90. Låt oss lägga till stilar:

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

I själva frågan lägger vi till en dummy-kolumn omedelbart innan kolumn vi vill färglägga. Kolumnen ska kallas SQLmarkup-något:

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, efter att ha tagit emot HTML-koden som genererats av Powershell, tar vi bort dummykolumnen från rubriken, och i datakroppen kommer vi att överföra värdet från kolumnen till stilen. Detta görs med bara två byten:

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

Resultat:


Är det inte elegant? Fast nej, den här färgen påminner mig om något


Källa: will.com