Automatisearring fan SQL-tsjinner yn Jenkins: it resultaat prachtich weromjaan

Wer trochgean mei it tema fan regeling Zero Touch PROD ûnder RDS. Takomstige DBA's sille net direkt kinne ferbine mei PROD-tsjinners, mar kinne gebrûk meitsje Jenkins banen foar in beheinde set fan operaasjes. De DBA lansearret baan en kriget nei ferrin fan tiid in brief mei in rapport oer it foltôgjen fan dizze operaasje. Litte wy nei manieren sjen om dizze resultaten oan 'e brûker te presintearjen.

Platte tekst

Litte wy begjinne mei de meast triviale. De earste metoade is sa ienfâldich dat d'r echt neat is om oer te praten (de auteur brûkt hjirnei FreeStyle-banen):

sqlcmd docht wat en wy presintearje it oan de brûker. Ideaal foar bygelyks backuptaken:

Ferjit trouwens net dat reservekopy / weromsette ûnder RDS asynchrone is, dus jo moatte derop wachtsje:

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

Twadde metoade, CSV

Alles hjir is ek hiel ienfâldich:

Dizze metoade wurket lykwols allinich as de gegevens weromjûn yn 'e CSV "ienfâldich" binne. As jo ​​besykje om bygelyks in list fan TOP N CPU-yntinsive query's op dizze manier werom te jaan, sil de CSV "korrodearje" fanwege it feit dat de query-tekst alle tekens kin befetsje - komma's, quotes, en sels line breaks. Dêrom hawwe wy wat yngewikkelder nedich.

Moaie tekens yn HTML

Ik jou dy daliks in koadefragment

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

Trouwens, betelje omtinken oan de line mei System.Management.Automation.PSCustomObject, it is magysk as der krekt ien line yn it raster is, dan ûntstiene wat problemen. De oplossing is sûnder folle begryp fan it ynternet helle. As gefolch krije jo útfier opmakke sa'n ding:

Grafiken tekenje

Warskôging: kinky koade hjirûnder!
D'r is in grappige fraach op 'e SQL-tsjinner dy't de CPU foar de lêste N minuten toant - it docht bliken dat kameraad Major alles ûnthâldt! Besykje dizze kwis:

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

No, brûk dizze opmaak ($Fragment fariabele)

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

Wy kinne it lichem fan 'e brief foarmje:

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

Hokker sil der sa útsjen:

Ja, Monsieur wit in protte fan perversaasjes! It is nijsgjirrich dat dizze koade befettet: Powershell (skreaun yn it), SQL, Xquery, HTML. It is spitich dat wy Javascript net kinne tafoegje oan HTML (om't it is foar skriuwen), mar it polearjen fan Python-koade (dat kin brûkt wurde yn SQL) is elkenien syn plicht!

SQL profiler trace útfier

It is dúdlik dat it spoar net past yn 'e CSV fanwegen it fjild TextData. Mar it werjaan fan in spoarraster yn in brief is ek nuver - sawol troch de grutte as om't dizze gegevens faak brûkt wurde foar fierdere analyze. Dêrom dogge wy it folgjende: wy belje fia invoke-SqlCmd in beskaat skrift, yn 'e djipten dêr't it dien wurdt

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

Folgjende, op freon Op de tsjinner dy't tagonklik is troch de DBA, is in Traces-database mei in lege sjabloan, de Modelplaat, klear om alle oantsjutte kolommen te akseptearjen. Wy kopiearje dit model nei in nije tabel mei in unike namme:

$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 no kinne wy ​​ús spoar deryn skriuwe mei help fan Data.SqlClient.SqlBulkCopy - Dêr haw ik hjirboppe al in foarbyld fan jûn. Ja, it soe ek moai wêze om konstanten te maskerjen yn TextData:

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

Wy ferfange nûmers mear as ien karakter lang mei 999, en wy ferfange snaren langer as ien karakter mei 'str'. Sifers fan 0 oant 9 wurde faak brûkt as flaggen, en wy berikke se net, lykas lege en ien-karakter snaren - 'Y', 'N', ensfh wurde faak fûn ûnder harren.

Litte wy wat kleur tafoegje oan ús libben (strikt 18+)

Yn tabellen wolle jo faak sellen markearje dy't oandacht nedich binne. Bygelyks, FAILS, heech nivo fan fragmintaasje, ensfh. Fansels kin dit dien wurde yn bleate SQL, HTML generearje mei PRINT, en it triemtype ynstelle op HTML yn 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>'

Wêrom haw ik sa'n koade skreaun?

Mar der is in moaier oplossing. Konvertearje nei HTML lit ús de sellen net kleurje, mar wy kinne it nei it feit dwaan. Wy wolle bygelyks sellen selektearje mei in fragmintaasjenivo fan mear as 80 en mear as 90. Litte wy stilen tafoegje:

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

Yn 'e query sels sille wy in dummykolom tafoegje fuortendaliks foar kolom wolle wy kleurje. De kolom moat neamd wurde SQLmarkup-eat:

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, 

No, nei it ûntfangen fan de HTML generearre troch Powershell, sille wy de dummy-kolom fan 'e koptekst fuortsmite, en yn it lichem fan' e gegevens sille wy de wearde fan 'e kolom nei de styl oerdrage. Dit wurdt dien mei mar twa ferfangings:

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

Resultaat:


Is it net elegant? Hoewol nee, dizze kleuring docht my wol wat tinken


Boarne: www.habr.com