Uendeshaji wa seva ya SQL huko Jenkins: kurudisha matokeo kwa uzuri

Tena kuendelea na mada ya mpangilio Zero Touch PROD chini ya RDS. DBA za siku zijazo hazitaweza kuunganisha kwenye seva za PROD moja kwa moja, lakini zitaweza kutumia Jenkins kazi kwa seti ndogo ya shughuli. DBA inazindua kazi na baada ya muda hupokea barua na ripoti ya kukamilika kwa operesheni hii. Hebu tuangalie njia za kuwasilisha matokeo haya kwa mtumiaji.

Maandishi wazi

Wacha tuanze na ndogo zaidi. Njia ya kwanza ni rahisi sana hivi kwamba hakuna kitu cha kuzungumza juu (mwandishi hapa anatumia kazi za FreeStyle):

sqlcmd hufanya kitu na tunawasilisha kwa mtumiaji. Inafaa kwa, kwa mfano, kazi za chelezo:

Usisahau, kwa njia, kwamba chini ya chelezo/rejesho ya RDS ni sawa, kwa hivyo unahitaji kuingoja:

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

Njia ya pili, CSV

Kila kitu hapa pia ni rahisi sana:

Walakini, njia hii inafanya kazi tu ikiwa data iliyorejeshwa katika CSV ni "rahisi". Ukijaribu kurudisha, kwa mfano, orodha ya maswali mazito ya TOP N CPU kwa njia hii, CSV "itaharibika" kwa sababu ya ukweli kwamba maandishi ya hoja yanaweza kuwa na herufi zozote - koma, nukuu, na hata mapumziko ya mstari. Kwa hiyo, tunahitaji kitu ngumu zaidi.

Ishara nzuri katika HTML

Nitakupa kijisehemu cha msimbo mara moja

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

Kwa njia, makini na mstari na System.Management.Automation.PSCustomObject, ni ya kichawi; ikiwa kuna mstari mmoja katika gridi ya taifa, basi matatizo fulani yalitokea. Suluhisho lilichukuliwa kutoka kwa Mtandao bila uelewa mwingi. Kama matokeo, utapata muundo wa pato kama hii:

Kuchora grafu

Onyo: msimbo wa kinky hapa chini!
Kuna swali la kuchekesha kwenye seva ya SQL inayoonyesha CPU kwa dakika N mwisho - ikawa kwamba Comrade Meja anakumbuka kila kitu! Jaribu swali hili:

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

Sasa, kwa kutumia umbizo hili ($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>

Tunaweza kuunda mwili wa barua:

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

Ambayo itaonekana kama hii:

Ndiyo, Monsieur anajua mengi kuhusu upotovu! Inafurahisha kwamba nambari hii ina: Powershell (iliyoandikwa ndani yake), SQL, Xquery, HTML. Inasikitisha kwamba hatuwezi kuongeza Javascript kwenye HTML (kwa kuwa ni ya kuandika), lakini kung'arisha msimbo wa Python (unaoweza kutumika katika SQL) ni wajibu wa kila mtu!

Matokeo ya kufuatilia wasifu wa SQL

Ni wazi kuwa ufuatiliaji hautaingia kwenye CSV kwa sababu ya uga wa TextData. Lakini kuonyesha gridi ya ufuatiliaji katika barua pia ni ajabu - kwa sababu ya ukubwa na kwa sababu data hii mara nyingi hutumiwa kwa uchambuzi zaidi. Kwa hiyo, tunafanya yafuatayo: tunaita kupitia omba-SqlCmd script fulani, kwa kina ambayo inafanywa

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

Ifuatayo, endelea rafiki Kwenye seva inayofikiwa na DBA, kuna hifadhidata ya Ufuatiliaji iliyo na kiolezo tupu, sahani ya Mfano, tayari kukubali safu wima zote zilizobainishwa. Tunakili muundo huu kwenye jedwali jipya lenye jina la kipekee:

$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 

Na sasa tunaweza kuandika ufuatiliaji wetu ndani yake kwa kutumia Data.SqlClient.SqlBulkCopy - Tayari nimetoa mfano wa hii hapo juu. Ndio, itakuwa nzuri pia kufunga viboreshaji katika TextData:

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

Tunabadilisha nambari zenye urefu wa herufi zaidi ya moja na 999, na tunabadilisha mifuatano mirefu zaidi ya herufi moja na 'str'. Nambari kutoka 0 hadi 9 hutumiwa mara nyingi kama bendera, na hatuzigusi, na vile vile nyuzi tupu na za herufi moja - 'Y', 'N', n.k. mara nyingi hupatikana miongoni mwazo.

Hebu tuongeze rangi katika maisha yetu (18+ kabisa)

Katika majedwali, mara nyingi ungependa kuangazia visanduku vinavyohitaji kuzingatiwa. Kwa mfano, FAILS, kiwango cha juu cha kugawanyika, nk. Kwa kweli, hii inaweza kufanywa kwa SQL tupu, ikitoa HTML kwa kutumia PRINT, na kuweka aina ya faili kwa HTML huko 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>'

Kwa nini niliandika nambari kama hiyo?

Lakini kuna suluhisho nzuri zaidi. ConvertTo-HTML hairuhusu sisi rangi seli, lakini tunaweza kufanya hivyo baada ya ukweli. Kwa mfano, tunataka kuchagua visanduku vilivyo na kiwango cha kugawanyika cha zaidi ya 80 na zaidi ya 90. Hebu tuongeze mitindo:

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

Katika swala yenyewe tutaongeza safu ya dummy mara moja kabla safu tunataka kupaka rangi. Safu inapaswa kuitwa SQLmarkup-kitu:

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, 

Sasa, baada ya kupokea HTML iliyotolewa na Powershell, tutaondoa safu ya dummy kutoka kwa kichwa, na katika mwili wa data tutahamisha thamani kutoka kwa safu hadi kwa mtindo. Hii inafanywa na mbadala mbili tu:

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

Matokeo:


Je, si ya kifahari? Ingawa hapana, rangi hii inanikumbusha kitu


Chanzo: mapenzi.com