🥇Аўтаматызацыя SQL server у Jenkins: вяртаем вынік прыгожа

зноў працягваючы тэму ўладкавання Zero Touch PROD пад RDS. Будучыя DBA не будуць мець магчымасці падлучыцца да PROD сервераў напрамую, але змогуць выкарыстоўваць Джэнкінс jobs для абмежаванага набору аперацый. DBA запускае job і праз некаторы час атрымлівае ліст са справаздачай аб выкананні гэтай аперацыі. Давайце разгледзім спосабы прэзентацыі гэтых вынікаў карыстачу.

Звычайны тэкст

Пачнём з самага трывіяльнага. Першы спосаб настолькі просты, што і казаць, увогуле тое, няма пра што (аўтар тут і далей выкарыстоўвае 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: вяртаем вынік хораша