Ukuzenzakalela kweseva ye-SQL e-Jenkins: ukubuyisela umphumela kahle

Futhi eqhubeka nendikimba yokuhlela I-Zero Touch PROD ngaphansi kwe-RDS. Ama-DBA esikhathi esizayo ngeke akwazi ukuxhuma kumaseva e-PROD ngokuqondile, kodwa azokwazi ukusebenzisa Jenkins imisebenzi yesethi elinganiselwe yemisebenzi. I-DBA iqala umsebenzi futhi ngemva kwesikhathi esithile ithola incwadi enombiko wokuphothulwa kwalo msebenzi. Ake sibheke izindlela zokwethula le miphumela kumsebenzisi.

Umbhalo Ongenalutho

Ake siqale ngezincane kakhulu. Indlela yokuqala ilula kangangokuba akukho okungakhulunywa ngakho (umbhali ngemuva kwalokhu usebenzisa imisebenzi yeFreeStyle):

sqlcmd yenza okuthile futhi siyethula kumsebenzisi. Ilungele, isibonelo, imisebenzi eyisipele:

Ungakhohlwa, ngendlela, ukuthi ngaphansi kwesipele/ukubuyisela kwe-RDS asynchronous, ngakho-ke udinga ukuyilinda:

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

Indlela yesibili, CSV

Konke lapha nakho kulula kakhulu:

Nokho, le ndlela isebenza kuphela uma idatha ebuyiswe ku-CSV "ilula". Uma uzama ukubuyisela, isibonelo, uhlu lwemibuzo ejulile ye-TOP N CPU ngale ndlela, i-CSV "izogqwala" ngenxa yokuthi umbhalo wombuzo ungaqukatha noma yiziphi izinhlamvu - okhefana, izingcaphuno, ngisho nokunqamuka komugqa. Ngakho-ke, sidinga okuthile okuyinkimbinkimbi.

Izimpawu ezinhle ku-HTML

Ngizokunikeza amazwibela ekhodi ngokushesha

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

Kodwa-ke, naka umugqa oneSistimu.Ukuphatha.Ukuzenzakalela.PSCustomObject, iwumlingo; uma kunomugqa owodwa ncamashi kugridi, bese kuba nezinkinga ezithile. Isixazululo sithathwe ku-inthanethi ngaphandle kokuqonda okuningi. Ngenxa yalokho, uzothola okukhiphayo kufomethwe into enjengale:

Ukudweba amagrafu

Isexwayiso: ikhodi ye-kinky ngezansi!
Kunombuzo ohlekisayo kuseva ye-SQL ebonisa i-CPU emizuzwini engu-N yokugcina - kuvele ukuthi uComrade Major ukhumbula yonke into! Zama lo mbuzo:

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

Manje, usebenzisa lokhu kufometha (okuguquguqukayo kwe-$ 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>

Singakha indikimba yencwadi:

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

Okuzobukeka kanje:

Yebo, uMonsieur wazi okuningi mayelana nokuhlanekezela! Kuyathakazelisa ukuthi le khodi iqukethe: Powershell (ebhalwe kuyo), SQL, Xquery, HTML. Kuyadabukisa ukuthi asikwazi ukwengeza i-Javascript ku-HTML (njengoba eyokubhala), kodwa ukupholisha ikhodi yePython (engasetshenziswa ku-SQL) kuwumsebenzi wawo wonke umuntu!

Okuphumayo kwephrofayili ye-SQL

Kuyacaca ukuthi ukulandelela ngeke kungene ku-CSV ngenxa yenkambu ye-TextData. Kodwa ukubonisa igridi yokulandela uhlamvu nakho kuyaxaka - kokubili ngenxa yobukhulu nangenxa yokuthi le datha ivame ukusetshenziselwa ukuhlaziywa okwengeziwe. Ngakho-ke, senza lokhu okulandelayo: sibiza nge cela-SqlCmd umbhalo othile, ekujuleni okwenziwa ngawo

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

Okulandelayo, kuqhubeke umngane Kuseva efinyelelwa yi-DBA, kunesizindalwazi se-Traces esinesifanekiso esingenalutho, i-Model plate, elungele ukwamukela wonke amakholomu ashiwo. Sikopisha le modeli etafuleni elisha elinegama elihlukile:

$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 

Futhi manje singabhala umkhondo wethu kukho sisebenzisa Idatha.SqlClient.SqlBulkCopy - Senginikeze isibonelo salokhu ngenhla. Yebo, kungaba kuhle futhi ukuvala ama-constants ku-TextData:

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

Sishintsha izinombolo ezingaphezu kohlamvu olulodwa ngobude ngo-999, futhi sifaka u-'str' esikhundleni seyunithi yezinhlamvu ezinde kunohlamvu olulodwa. Izinombolo ezisuka ku-0 kuye ku-9 zivame ukusetshenziswa njengamafulege, futhi asizithinti, kanye neyunithi yezinhlamvu ezingenalutho nenohlamvu olulodwa - 'Y', 'N', njll. zivame ukutholakala phakathi kwazo.

Ake sengeze umbala othile ezimpilweni zethu (ngokuqondile 18+)

Kumathebula, ngokuvamile ufuna ukugqamisa amaseli adinga ukunakwa. Isibonelo, I-FAILS, izinga eliphezulu lokuhlukana, njll. Vele, lokhu kungenziwa ku-SQL engenalutho, ukukhiqiza i-HTML kusetshenziswa i-PRINT, nokusetha uhlobo lwefayela ku-HTML ku-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>'

Kungani ngibhale ikhodi enjalo?

Kodwa kunesixazululo esihle kakhulu. ConvertTo-HTML akusivumeli ukuthi sifake umbala kumaseli, kodwa singakwenza ngemva kweqiniso. Isibonelo, sifuna ukukhetha amaseli anezinga lokuhlukaniswa elingaphezu kuka-80 nangaphezulu kuka-90. Masingeze izitayela:

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

Embuzweni ngokwawo sizofaka ikholomu ye-dummy ngokushesha ngaphambili ikholomu sifuna ukuyifaka umbala. Ikholomu kufanele ibizwe I-SQLmarkup-okuthile:

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, 

Manje, ngemva kokuthola i-HTML ekhiqizwe i-Powershell, sizosusa ikholomu eyidumi kunhlokweni, futhi emzimbeni wedatha sizodlulisela inani ukusuka kukholomu siye kusitayela. Lokhu kwenziwa ngokushintshanisa okubili nje:

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

Umphumela:


Akuyona into enhle? Nakuba cha, lo mbala ungikhumbuza okuthile


Source: www.habr.com