🥇Аутоматизација СКЛ сервера у Џенкинсу: прелепо враћање резултата

Поново настављајући тему уређења Зеро Тоуцх ПРОД под РДС. Будући ДБА неће моћи да се повежу директно на ПРОД сервере, али ће моћи да користе јенкинс послови за ограничен скуп операција. ДБА покреће посао и након неког времена добија писмо са извештајем о завршетку ове операције. Хајде да погледамо начине да ове резултате представимо кориснику.

Обичан текст

Почнимо са најтривијалнијим. Први метод је толико једноставан да нема о чему да се прича (аутор у даљем тексту користи ФрееСтиле послове):

склцмд уради нешто и ми то представљамо кориснику. Идеално за, на пример, резервне послове:

Не заборавите, узгред, да је под РДС бацкуп/ресторе асинхроно, тако да морате да сачекате на то:

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

Други метод, ЦСВ

Овде је све такође врло једноставно:

Међутим, овај метод функционише само ако су подаци враћени у ЦСВ-у „једноставни“. Ако покушате да вратите, на пример, листу ТОП Н ЦПУ интензивних упита на овај начин, ЦСВ ће „нагризати“ због чињенице да текст упита може да садржи било које знакове - зарезе, наводнике, па чак и преломе редова. Дакле, треба нам нешто компликованије.

Прелепи знакови у ХТМЛ-у

Одмах ћу вам дати исечак кода

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

Узгред, обратите пажњу на линију са Систем.Манагемент.Аутоматион.ПСЦустомОбјецт, то је магично, ако постоји тачно једна линија у мрежи, онда су настали неки проблеми. Решење је преузето са интернета без много разумевања. Као резултат, добићете излаз форматиран отприлике овако:

Цртање графова

Упозорење: кинки код испод!
Постоји смешан упит на СКЛ серверу који приказује ЦПУ у последњих Н минута - испоставило се да друг Мајор све памти! Пробајте овај квиз:

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

Сада, користећи ово форматирање (променљива $Фрагмент)

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

Можемо формирати тело писма:

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

Што ће изгледати овако:

Да, господин зна много о перверзијама! Занимљиво је да овај код садржи: Поверсхелл (написан у њему), СКЛ, Кскуери, ХТМЛ. Штета што не можемо да додамо Јавасцрипт у ХТМЛ (пошто је за писање), али полирање Питхон кода (који се може користити у СКЛ-у) је свачија дужност!

Излаз трага СКЛ профилера

Јасно је да се траг неће уклопити у ЦСВ због поља ТектДата. Али приказивање мреже трагова у писму је такође чудно – и због величине и због тога што се ови подаци често користе за даљу анализу. Стога радимо следеће: позивамо преко инвоке-СклЦмд одређени сценарио, у чијој дубини се то ради

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

Следеће, даље пријатељу На серверу којем ДБА приступа, постоји база података Трацес са празним шаблоном, модел плоча, спремна да прихвати све наведене колоне. Копирамо овај модел у нову табелу са јединственим именом:

$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

И сада можемо да упишемо свој траг у њега користећи Дата.СклЦлиент.СклБулкЦопи - Већ сам горе навео пример овога. Да, такође би било лепо маскирати константе у ТектДата:

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

Бројеве дужине више од једног знака замењујемо са 999, а низове дужи од једног знака замењујемо са 'стр'. Често се као заставице користе бројеви од 0 до 9, а не дирамо их, као и празне и једнозначне низове - 'И', 'Н' итд.

Хајде да додамо мало боје нашим животима (строго 18+)

У табелама често желите да истакнете ћелије које захтевају пажњу. На пример, НЕУСПЕХ, висок ниво фрагментације итд. Наравно, ово се може урадити у голом СКЛ-у, генеришући ХТМЛ користећи ПРИНТ и подешавајући тип датотеке на ХТМЛ у Џенкинсу:

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

Зашто сам написао такав код?

Али постоји лепше решење. ЦонвертТо-ХТМЛ не дозвољава нам да бојимо ћелије, али то можемо учинити накнадно. На пример, желимо да изаберемо ћелије са нивоом фрагментације већим од 80 и више од 90. Хајде да додамо стилове:

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

У самом упиту додаћемо лажну колону непосредно пре колону коју желимо да обојимо. Колона треба да се зове СКЛмаркуп-нешто:

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,

Сада, након што смо примили ХТМЛ који генерише Поверсхелл, уклонићемо лажну колону из заглавља, а у телу података ћемо пренети вредност из колоне у стил. Ово се ради са само две замене:

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

Резултат:

Зар није елегантно? Иако не, ова боја ме подсећа на нешто

Извор: ввв.хабр.цом

Аутоматизација СКЛ сервера у Џенкинсу: прелепо враћање резултата