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

Отново продължаване на темата за подредбата Zero Touch PROD под RDS. Бъдещите DBA няма да могат да се свързват директно към PROD сървърите, но ще могат да използват Дженкинс работни места за ограничен набор от операции. DBA стартира задание и след известно време получава писмо с отчет за завършването на тази операция. Нека да разгледаме начините за представяне на тези резултати на потребителя.

Обикновен текст

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

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

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

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 по този начин, 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 сървъра, която показва процесора за последните 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. Жалко е, че не можем да добавим Javascript към HTML (тъй като е за писане), но полирането на кода на Python (който може да се използва в SQL) е задължение на всеки!

Изход за проследяване на SQL Profiler

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

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

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

$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 - Вече дадох пример за това по-горе. Да, също би било хубаво да маскирате константи в 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 и задаване на файлов тип на HTML в 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>'

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

Но има по-красиво решение. Преобразуване в 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">'

Резултати:

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

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

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