Automation ng SQL server sa Jenkins: maganda ang pagbabalik ng resulta

Muli pagpapatuloy ng tema ng pagsasaayos Zero Touch PROD sa ilalim ng RDS. Ang mga hinaharap na DBA ay hindi direktang makakonekta sa mga PROD server, ngunit magagamit Jenkins mga trabaho para sa isang limitadong hanay ng mga operasyon. Ang DBA ay naglulunsad ng trabaho at pagkaraan ng ilang oras ay nakatanggap ng sulat na may ulat sa pagkumpleto ng operasyong ito. Tingnan natin ang mga paraan upang ipakita ang mga resultang ito sa user.

Plain na Teksto

Magsimula tayo sa pinakawalang halaga. Ang unang paraan ay napakasimple na wala talagang dapat pag-usapan (ang may-akda pagkatapos nito ay gumagamit ng mga trabahong FreeStyle):

sqlcmd gumagawa ng isang bagay at ipinakita namin ito sa gumagamit. Tamang-tama para sa, halimbawa, mga backup na trabaho:

Huwag kalimutan, sa pamamagitan ng paraan, na sa ilalim ng RDS backup/restore ay asynchronous, kaya kailangan mong hintayin ito:

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

Pangalawang paraan, CSV

Ang lahat dito ay napakasimple din:

Gayunpaman, gumagana lang ang paraang ito kung ang data na ibinalik sa CSV ay "simple". Kung susubukan mong ibalik, halimbawa, ang isang listahan ng mga TOP N CPU intensive query sa ganitong paraan, ang CSV ay "mawawasak" dahil sa katotohanan na ang query text ay maaaring maglaman ng anumang mga character - mga kuwit, quote, at kahit na mga line break. Samakatuwid, kailangan namin ng isang bagay na mas kumplikado.

Magagandang mga sign sa HTML

Bibigyan kita kaagad ng code snippet

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

Sa pamamagitan ng paraan, bigyang-pansin ang linya na may System.Management.Automation.PSCustomObject, ito ay mahiwagang, kung mayroong eksaktong isang linya sa grid, pagkatapos ay lumitaw ang ilang mga problema. Ang solusyon ay kinuha mula sa Internet nang walang gaanong pag-unawa. Bilang isang resulta, makakakuha ka ng output na na-format tulad nito:

Pagguhit ng mga graph

Babala: kinky code sa ibaba!
Mayroong nakakatawang query sa SQL server na nagpapakita ng CPU sa huling N minuto - lumalabas na naaalala ni Comrade Major ang lahat! Subukan ang pagsusulit na ito:

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

Ngayon, gamit ang pag-format na ito ($Fragment variable)

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

Maaari nating buuin ang katawan ng liham:

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

Alin ang magiging ganito:

Oo, maraming alam si Monsieur tungkol sa mga perversions! Kapansin-pansin na ang code na ito ay naglalaman ng: Powershell (nakasulat dito), SQL, Xquery, HTML. Nakakalungkot na hindi namin maidagdag ang Javascript sa HTML (dahil ito ay para sa pagsusulat), ngunit ang polishing Python code (na maaaring gamitin sa SQL) ay tungkulin ng lahat!

SQL profiler trace output

Malinaw na hindi magkakasya ang bakas sa CSV dahil sa field ng TextData. Ngunit ang pagpapakita ng isang trace grid sa isang liham ay kakaiba din - dahil sa laki at dahil madalas na ginagamit ang data na ito para sa karagdagang pagsusuri. Samakatuwid, ginagawa namin ang sumusunod: tumatawag kami sa pamamagitan ng invoke-SqlCmd isang tiyak na script, sa lalim kung saan ito ginawa

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

Susunod sa другом Sa server na maa-access ng DBA, mayroong isang Traces database na may walang laman na template, ang Model plate, na handang tanggapin ang lahat ng tinukoy na column. Kinopya namin ang modelong ito sa isang bagong talahanayan na may natatanging pangalan:

$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 

At ngayon ay maaari nating isulat ang ating bakas dito gamit Data.SqlClient.SqlBulkCopy - Nagbigay na ako ng halimbawa nito sa itaas. Oo, maganda rin na i-mask ang mga constant sa TextData:

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

Pinapalitan namin ang mga numerong higit sa isang character ang haba ng 999, at pinapalitan namin ng 'str' ang mga string na mas mahaba kaysa sa isang character. Ang mga numero mula 0 hanggang 9 ay kadalasang ginagamit bilang mga flag, at hindi namin sila hinawakan, pati na rin ang mga walang laman at solong character na string - 'Y', 'N', atbp. ay madalas na matatagpuan sa kanila.

Lagyan natin ng kulay ang ating buhay (mahigpit na 18+)

Sa mga talahanayan, madalas mong gustong i-highlight ang mga cell na nangangailangan ng pansin. Halimbawa, FAILS, mataas na antas ng fragmentation, atbp. Siyempre, maaari itong gawin sa hubad na SQL, pagbuo ng HTML gamit ang PRINT, at pagtatakda ng uri ng file sa HTML sa 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>'

Bakit ako sumulat ng ganoong code?

Ngunit mayroong isang mas magandang solusyon. ConvertTo-HTML ay hindi nagpapahintulot sa amin na kulayan ang mga cell, ngunit magagawa namin ito pagkatapos ng katotohanan. Halimbawa, gusto naming pumili ng mga cell na may antas ng fragmentation na higit sa 80 at higit sa 90. Magdagdag tayo ng mga istilo:

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

Sa query mismo ay magdaragdag kami ng isang dummy column kaagad kanina column na gusto naming kulayan. Dapat tawagan ang column SQLmarkup-isang bagay:

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, 

Ngayon, nang matanggap ang HTML na nabuo ng Powershell, aalisin namin ang dummy na column mula sa header, at sa katawan ng data ay ililipat namin ang halaga mula sa column patungo sa istilo. Ginagawa ito sa dalawang pagpapalit lamang:

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

Resulta:


Hindi ba elegante? Bagama't hindi, ang kulay na ito ay nagpapaalala sa akin ng isang bagay


Pinagmulan: www.habr.com