🥇Автоматизація SQL server у Jenkins: повертаємо результат красиво ProHoster

знову продовжуючи тему облаштування Zero Touch PROD під RDS. Майбутні DBA не будуть мати можливість підключитися до PROD серверів безпосередньо, але зможуть використовувати Дженкінс 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} 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

Другий спосіб, 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>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
  }

До речі, зверніть увагу на рядок із System.Management.Automation.PSCustomObject, вона магічна, якщо в гриді рівно один рядок, то виникали якісь проблеми. Рішення взято з інтернету особливо не розбираючись. В результаті ви отримаєте висновок, оформлений приблизно так:

Малюємо графіки

Увага: збочений код нижче!
Є кумедний запит на SQL сервер, який виводить CPU за останні N хвилин - виявляється, товариш майор все запам'ятовує! Спробуйте цю квер:

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

Тепер, використовуючи таке форматування (змінна $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 profiler trace

Зрозуміло, що трейс «не залізе» у CSV через поле TextData. Але виводити трейс гридом у листі теж дивно — і через розмір, і тому, що ці дані часто використовуються для подальшого аналізу. Тому ми робимо таке: викликаємо через invoke-SqlCmd якийсь скрипт, у надрах якого робиться

select 
  SPID,EventClass,TextData,
  Duration,Reads,Writes,CPU,
  StartTime,EndTime,DatabaseName,HostName,
  ApplicationName,LoginName
   from ::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 + " from Model"
invoke-SqlCmd -ConnectionString $tstr -Query $copytab

І тепер ми можемо до неї записати наш трейс за допомогою Data.SqlClient.SqlBulkCopy - Приклад цього я вже наводив вище. Так, ще непогано б зробити masking констант у 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:

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

Для чого я написав такий код?

Але є красивіше рішення. ConvertTo-HTML не дає нам розфарбувати клітини, але ми можемо зробити постфактум. Наприклад, ми хочемо виділити клітини з рівнем фрагментації понад 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>

У самій квері ми додамо фіктивну колонку безпосередньо до колонки, яку хочемо розфарбувати. Колонка має називатися 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 `
  -replace "<th>SQLmarkup[^<]*</th>", "" `
  -replace "<td>SQLmarkup-(.+?)</td><td>",'<td class="SQLmarkup-$1">'

Результат:

Чи не так, елегантно? Хоча ні, щось це забарвлення мені нагадує

Джерело: habr.com

Автоматизація SQL server у Jenkins: повертаємо результат красиво