🥇Aunoatanga o te tūmau SQL i Jenkins: te whakahoki ataahua i te hua

Ano te haere tonu i te kaupapa o te whakaritenga Kore Touch PROD i raro i te RDS. Kaore e taea e nga DBA a meake nei te hono atu ki nga tūmau PROD, engari ka taea te whakamahi Jenkins mahi mo te huinga mahi iti. Ka whakarewahia e te DBA he mahi, a, i muri i etahi wa ka tae mai he reta me te ripoata mo te otinga o tenei mahi. Me titiro ki nga huarahi hei whakaatu i enei hua ki te kaiwhakamahi.

Kupu Maama

Me timata ki nga mea iti rawa. Ko te tikanga tuatahi he tino ngawari kaore he mea hei korero (ka whakamahia e te kaituhi nga mahi FreeStyle):

sqlcmd ka mahi i tetahi mea ka tukuna e matou ki te kaiwhakamahi. He pai mo, hei tauira, nga mahi taapiri:

Kaua e wareware, i te ara, kei raro i te RDS te taapiri / whakahoki he koretake, na me tatari koe mo tena:

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

Tikanga tuarua, CSV

He tino ngawari nga mea katoa i konei:

Heoi, ka mahi noa tenei tikanga mena he "ngawari" nga raraunga i whakahokia mai i te CSV. Mena ka ngana koe ki te hoki mai, hei tauira, he rarangi o nga patai kaha a TOP N CPU penei, ko te CSV "ka pirau" na te mea kei roto i te tuhinga patai etahi tohu - piko, korukī, tae noa ki nga wehenga raina. Na reira, ka hiahia tatou ki tetahi mea uaua ake.

Nga tohu ataahua i roto i te HTML

Ka hoatu e ahau he snippet waehere i tenei wa tonu

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

Ma te ara, kia tupato ki te raina me te System.Management.Automation.PSCustomObject, he mea makutu, mena he kotahi te rarangi kei roto i te raarangi, katahi ka puta etahi raru. I tangohia te otinga mai i te Ipurangi me te kore e tino mohio. Ko te mutunga, ka whiwhi koe i te putanga whakahōputu penei:

Te tuhi kauwhata

Whakatupato: waehere kinky kei raro!
He patai rorirori kei runga i te tūmau SQL e whakaatu ana i te PTM mo nga meneti N whakamutunga - ka kitea kei te maumahara a Comrade Major ki nga mea katoa! Whakamātauria tēnei pātaitai:

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

Inaianei, ma te whakamahi i tenei whakahōpututanga ($Taurangi Wahi)

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

Ka taea e tatou te hanga i te tinana o te reta:

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

Ka penei te ahua:

Ae, e mohio nui ana a Monsieur mo nga whanoke! He mea whakamiharo kei roto i tenei waehere: Powershell (kua tuhia ki roto), SQL, Xquery, HTML. He pouri kaore e taea e matou te taapiri i te Javascript ki te HTML (mai i te mea mo te tuhi), engari ko te whakakoi i te waehere Python (ka taea te whakamahi i roto i te SQL) he mahi ma te katoa!

Putanga tohu tohu SQL

E marama ana kaore e uru te tohu ki roto i te CSV na te mara TextData. Engari ko te whakaatu i te matiti tohu i roto i te reta he mea rereke - na te nui o te rahi me te whakamahi i enei raraunga mo etahi atu tātaritanga. No reira, ka mahia e matou nga mea e whai ake nei: ka waea matou ma karanga-SqlCmd he tuhi tuhi, i roto i te hohonutanga o te mahi

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

I muri mai, i runga hoa I runga i te tūmau ka taea e te DBA te uru atu, kei reira he papaa raraunga Traces me te tauira kau, te pereti Tauira, kua rite ki te whakaae ki nga pou katoa kua tohua. Ka kapea e matou tenei tauira ki tetahi ripanga hou me te ingoa ahurei:

$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 inaianei ka taea e taatau te tuhi i o maatau tohu ki roto ma te whakamahi Raraunga.SqlClient.SqlBulkCopy - Kua hoatu e ahau he tauira o runga ake nei. Ae, he pai hoki ki te huna i nga taumau i roto TextData:

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

Ka whakakapihia e matou nga nama neke atu i te kotahi te roa ki te 999, ka whakakapihia e matou nga aho roa atu i te kotahi me te 'str'. Ko nga nama mai i te 0 ki te 9 ka whakamahia hei haki, kaore matou e pa ki a raatau, tae atu ki nga aho kau me nga aho kotahi - 'Y', 'N', me etahi atu ka kitea i roto i a raatau.

Me taapiri he tae ki o tatou oranga (he 18+)

I roto i nga ripanga, he maha nga wa ka hiahia koe ki te whakanui i nga pūtau me aro. Hei tauira, FAILS, taumata teitei o te wehenga, etc. Ae ra, ka taea tenei i roto i te SQL kore, te whakaputa HTML ma te whakamahi i te PRINT, me te tautuhi i te momo konae ki HTML i 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>'

He aha ahau i tuhi ai i taua waehere?

Engari he otinga ataahua ake. Tahuri Ki-HTML e kore e tukua kia tae tatou ki nga ruma, engari ka taea e tatou i muri i te meka. Hei tauira, e hiahia ana matou ki te kowhiri i nga pūtau me te taumata wehewehenga neke atu i te 80 me te neke atu i te 90. Me taapiri momo:

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

I roto i te patai ake ka taapirihia e matou he pou hangarua i mua tonu atu tīwae e hiahia ana matou ki te tae. Me karanga te pou SQLmarkup-tetahi mea:

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,

Inaianei, kua riro mai i a matou te HTML i hangaia e Powershell, ka tangohia e matou te poupou puhoi mai i te pane, a i roto i te tinana o nga raraunga ka whakawhitia e matou te uara mai i te pou ki te ahua. Ka mahia tenei ma te rua noa nga whakakapinga:

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

Hua:

Ehara i te mea huatau? Ahakoa kare, ka maumahara tenei tae ki tetahi mea

Source: will.com

Aunoatanga o te tūmau SQL i Jenkins: te whakahoki ataahua i te hua