🥇 Awtomeiddio gweinydd SQL yn Jenkins: dychwelyd y canlyniad yn hyfryd

Unwaith eto gan barhau â'r thema o drefniant Zero Touch PROD dan RDS. Ni fydd DBAs yn y dyfodol yn gallu cysylltu â gweinyddwyr PROD yn uniongyrchol, ond byddant yn gallu defnyddio Jenkins swyddi ar gyfer set gyfyngedig o weithrediadau. Mae'r DBA yn lansio swydd ac ar ôl peth amser yn derbyn llythyr gydag adroddiad ar gwblhau'r llawdriniaeth hon. Gadewch i ni edrych ar ffyrdd o gyflwyno'r canlyniadau hyn i'r defnyddiwr.

Testun plaen

Gadewch i ni ddechrau gyda'r mwyaf dibwys. Mae'r dull cyntaf mor syml fel nad oes unrhyw beth i siarad amdano mewn gwirionedd (mae'r awdur o hyn ymlaen yn defnyddio swyddi FreeStyle):

sqlcmd yn gwneud rhywbeth ac rydym yn ei gyflwyno i'r defnyddiwr. Yn ddelfrydol ar gyfer, er enghraifft, swyddi wrth gefn:

Peidiwch ag anghofio, gyda llaw, bod copi wrth gefn / adfer RDS yn anghydamserol, felly mae angen i chi aros amdano:

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

Ail ddull, CSV

Mae popeth yma hefyd yn syml iawn:

Fodd bynnag, mae'r dull hwn ond yn gweithio os yw'r data a ddychwelwyd yn y CSV yn "syml". Os ceisiwch ddychwelyd, er enghraifft, restr o ymholiadau dwys TOP N CPU yn y modd hwn, bydd y CSV yn “cyrydu” oherwydd y gall testun yr ymholiad gynnwys unrhyw nodau - atalnodau, dyfyniadau, a hyd yn oed toriadau llinell. Felly, mae angen rhywbeth mwy cymhleth arnom.

Arwyddion hardd mewn HTML

Byddaf yn rhoi pyt cod i chi ar unwaith

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

Gyda llaw, rhowch sylw i'r llinell â System.Management.Automation.PSCustomObject, mae'n hudolus; os oes union un llinell yn y grid, yna cododd rhai problemau. Cymerwyd yr ateb o'r Rhyngrwyd heb lawer o ddealltwriaeth. O ganlyniad, byddwch yn cael fformat allbwn rhywbeth fel hyn:

Lluniadu graffiau

Rhybudd: cod kinky isod!
Mae yna ymholiad doniol ar y gweinydd SQL sy'n arddangos y CPU am y munudau N olaf - mae'n troi allan bod Comrade Major yn cofio popeth! Rhowch gynnig ar y cwis hwn:

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

Nawr, gan ddefnyddio'r fformatio hwn (newidyn $ 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>

Gallwn ffurfio corff y llythyr:

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

Pa un fydd yn edrych fel hyn:

Ydy, mae Monsieur yn gwybod llawer am wyrdroadau! Mae'n ddiddorol bod y cod hwn yn cynnwys: Powershell (ysgrifenedig ynddo), SQL, Xquery, HTML. Mae’n drueni na allwn ychwanegu Javascript at HTML (gan ei fod ar gyfer ysgrifennu), ond mae caboli cod Python (y gellir ei ddefnyddio yn SQL) yn ddyletswydd ar bawb!

Proffil olrhain allbwn SQL

Mae'n amlwg na fydd yr olrhain yn ffitio i'r CSV oherwydd y maes TextData. Ond mae dangos grid olrhain mewn llythyren hefyd yn rhyfedd - oherwydd maint ac oherwydd bod y data hwn yn aml yn cael ei ddefnyddio ar gyfer dadansoddiad pellach. Felly, rydym yn gwneud y canlynol: rydym yn galw via galw-SqlCmd ysgrythyr benodol, yn nyfnder yr hon y gwneir

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

Nesaf, ymlaen ffrind Ar y gweinydd sy'n hygyrch gan y DBA, mae cronfa ddata Traces gyda thempled gwag, y plât Model, yn barod i dderbyn yr holl golofnau penodedig. Rydym yn copïo'r model hwn i dabl newydd gydag enw unigryw:

$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

Ac yn awr gallwn ysgrifennu ein hôl i mewn iddo gan ddefnyddio Data.SqlClient.SqlBulkCopy — Yr wyf eisioes wedi rhoddi engraifft o hyn uchod. Byddai, byddai hefyd yn braf cuddio cysonion yn TextData:

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

Rydyn ni'n disodli rhifau mwy nag un nod o hyd gyda 999, ac rydyn ni'n disodli llinynnau hirach nag un nod gyda 'str'. Defnyddir rhifau o 0 i 9 yn aml fel baneri, ac nid ydym yn eu cyffwrdd, yn ogystal â llinynnau gwag ac un cymeriad - 'Y', 'N', ac ati yn aml yn eu plith.

Gadewch i ni ychwanegu rhywfaint o liw at ein bywydau (18+ yn llym)

Mewn tablau, rydych chi'n aml eisiau tynnu sylw at gelloedd sydd angen sylw. Er enghraifft, METHU, lefel uchel o ddarnio, ac ati. Wrth gwrs, gellir gwneud hyn mewn SQL noeth, gan gynhyrchu HTML gan ddefnyddio PRINT, a gosod y math o ffeil i HTML yn 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>'

Pam wnes i ysgrifennu cod o'r fath?

Ond mae yna ateb mwy prydferth. TrosiI-HTML ddim yn gadael i ni liwio'r celloedd, ond fe allwn ni wneud hynny ar ôl y ffaith. Er enghraifft, rydym am ddewis celloedd gyda lefel darnio o fwy na 80 a mwy na 90. Gadewch i ni ychwanegu arddulliau:

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

Yn yr ymholiad ei hun byddwn yn ychwanegu colofn ffug yn union o'r blaen colofn rydyn ni am ei lliwio. Dylid galw'r golofn SQLmarkup-rhywbeth:

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,

Nawr, ar ôl derbyn yr HTML a gynhyrchir gan Powershell, byddwn yn tynnu'r golofn ffug o'r pennawd, ac yng nghorff y data byddwn yn trosglwyddo'r gwerth o'r golofn i'r arddull. Gwneir hyn gyda dim ond dau eilydd:

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

Canlyniad:

Onid yw'n gain? Er na, mae'r lliwio hwn yn fy atgoffa o rywbeth

Ffynhonnell: hab.com

Awtomeiddio gweinydd SQL yn Jenkins: dychwelyd y canlyniad yn hyfryd