🥇Автоматизация на SQL сървър в Jenkins: връщаме резултата красиво

Снова Продължавайки темата за оборудването Zero Touch PROD под RDS. Бъдещите DBA няма да могат да се свържат директно с PROD сървърите, но ще могат да използват Jenkins jobs за ограничен набор от операции. DBA стартира job и след известно време получава имейл с отчет за изпълнението на тази операция. Нека разгледаме начини за представяне на тези резултати на потребителя.

Plain Text

Да започнем с най-тривиалното. Първият начин е толкова прост, че всъщност няма за какво да се говори (авторът тук и по-нататък използва FreeStyle jobs):

sqlcmd нещо се изпълнява и ние го представяме на потребителя. Идеално е за, например, backup jobs:

Не забравяйте, между другото, че под RDS backup/restore е асинхронен, така че трябва да изчакате:

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} заменено с името на db от powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Вторият начин, CSV

Тук всичко също е много просто:

Въпреки това, този метод работи само ако данните, върнати в CSV, са "прости". Ако опитате по този начин да върнете, например, списък с TOP N CPU intensive queries, CSV ще "се развали" поради факта, че текстът на заявката може да съдържа всякакви символи — запетаи, кавички и дори нови редове. Затова ще ни е необходим нещо по-сложно.

Красива таблица на HTML

Ще дам един фрагмент от код

$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>Моята таблица</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Няма данни</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Между другото, обърнете внимание на реда с System.Management.Automation.PSCustomObject, той е магически, ако в гридa има точно един ред, възникват проблеми. Решението е взето от интернет, особено без да се задълбочаваме. В резултат ще получите изход, оформен по следния начин:

Рисуваме графики

Внимание: изкривен код по-долу!
Има интересен запитване на SQL сървър, който извежда CPU за последните N минути — оказва се, че другарят майор всичко помни! Опитайте тази заявка:

ДЕКЛАРИРАЙ @ts_now bigint = (ИЗБЕРИ cpu_ticks/(cpu_ticks/ms_ticks) 
  ОТ sys.dm_os_sys_info С (NOLOCK)); 
ИЗБЕРИ TOP(256) 
  DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) КАТО [EventTime],
  SQLProcessUtilization КАТО [SQLCPU], 
  100 - SystemIdle - SQLProcessUtilization КАТО [OtherCPU]
ОТ (ИЗБЕРИ record.value('(. /Record /@id)[1]', 'int') КАТО record_id, 
  record.value('(. /Record /SchedulerMonitorEvent /SystemHealth /SystemIdle)[1]', 'int') 
    КАТО [SystemIdle], 
  record.value('(. /Record /SchedulerMonitorEvent /SystemHealth /ProcessUtilization)[1]', 'int') 
    КАТО [SQLProcessUtilization], [timestamp] 
  ОТ (ИЗБЕРИ [timestamp], CONVERT(xml, record) КАТО [record] 
  ОТ sys.dm_os_ring_buffers С (NOLOCK)
  КЪДЕ ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
    И record LIKE N'%%') КАТО x) КАТО y 
НАРЕДИ ПО 1 DESC ВАРИАНТ (ПРЕИЗЧИСЛИ);

Сега, използвайки такова форматиране (променлива $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>

Можем да съставим тялото на писмото:

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

Което ще изглежда така:

Да, господинът разбира от изкривявания! Интересно е, че този код съдържа: Powershell (на който е написан), SQL, Xquery, HTML. Жалко, че към HTML не можем да добавим Javascript (тъй като това е за писмото), но да обработим кода на Python (който може да се използва в SQL) е дълг на всеки!

Изход на SQL профайлера

Ясно е, че трасето „не ще попадне“ в CSV заради полето TextData. Но да извеждаме трасето в таблица в писмото също е странно — заради размера и защото тези данни често се използват за по-нататъшен анализ. Затова правим следното: извикваме чрез invoke-SqlCmd някакъв скрипт, в недрата на който се извършва

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

След това, на другом сървър, достъпен за DBA, съществува база Traces с празен шаблон, табличка Model, готова да приеме всички посочени колони. Копираме този модел в нова таблица с уникално име:

$dt = Get-Date -format "yyyyMMdd"
$tm = Get-Date -format "hhmmss"
$tableName = $srv + "_" + $dt + "_" + $tm
$copytab = "select * into " + $tableName + " от Model"
invoke-SqlCmd -ConnectionString $tstr -Query $copytab

И сега можем да запишем нашето трасе с помощта на Data.SqlClient.SqlBulkCopy — пример за това вече посочих по-горе. Да, все пак е добре да направим маскиране на константите в TextData:

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

Ние заменяме числа с повече от един символ с 999, а низовете с повече от един символ заменяме с 'str'. Числата от 0 до 9 често се използват като флагове и не ги засягаме, както и празните и едносимволните низове — между тях често се срещат 'Y', 'N' и т.н.

Да добавим цветове в живота си (строго 18+)

В таблиците често искаме да подчертаем клетки, които изискват внимание. Например, FAILS, високото ниво на фрагментация и т.н. Разбира се, това може да се направи и с чист SQL, генерирайки HTML с помощта на PRINT, а в Jenkins задайте типа файл HTML:

декларация @body varchar(max), @chunk varchar(max)
задание @body='<font face="Lucida Console" size="3">'
задание @body=@body+'<b>Име на сървъра: '+@@servername+'</b><br>'
задание @body=@body+'<br><br>'
задание @body=@body+'<table><tr><th>Job</th><th>Последно изпълнение</th><th>Средна продължителност, сек</th><th>Последно изпълнение, сек</th><th>Последен статус</th></tr>'
печат @body

ДЕКЛАРИРАЙ курсор tab за ИЗБИРАНЕ '<tr><td>'+името+'</td><td>'+
  ПоследноИзпълнение+'</td><td>'+
  конвертиране(varchar,СреднаПродължителност)+'</td><td>'+
  конвертиране(varchar,ПоследнаПродължителност)+'</td><td>'+
    случай когато ПоследенСтатус<>'Успех' тогава '<font color="red">' иначе '' край+
      ПоследенСтатус+
      случай когато ПоследенСтатус<>'Успех' тогава '</font>' иначе '' край+
     +'</td><td>'
  от #j2
ОТВОРИ таб;  
ВЗЕМИ следващ от таб в @chunk
ДОКАТО @@FETCH_STATUS = 0  
НАЧАЛО
  печат @chunk
  ВЗЕМИ следващ от таб в @chunk;  
КРАЙ
ЗАВРЪЩАЙ таб;
печат '</table>'

Защо написах такъв код?

Но има по-красиво решение. ConvertTo-HTML не ни позволява да оцветим клетките, но можем да го направим постфактум. Например, искаме да подчертаем клетките с ниво на фрагментация над 80 и над 90. Добавяме стиловете:

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

В самата заявка ще добавим фиктивна колона директно преди колоната, която искаме да оцветим. Колоната трябва да се нарича SQLmarkup-нещо:

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, генерирано от Powershell, ще премахнем фиктивната колона от заглавието, а в тялото на данните ще преместим стойността от колоната в стила. Това става само с две замени:

$html = $html `
  -замени "<th>SQLmarkup[^<]*</th>", "" `
  -замени "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'

Резултат:

Не е ли елегантно? Въпреки че нещо тази окраска ми напомня

Източник: habr.com

Автоматизация на SQL сървър в Jenkins: връщане на резултата красиво