🥇Automazione del server SQL in Jenkins: restituire il risultato magnificamente

di nuovo continuando il tema dell'arrangiamento Zero TouchPROD sotto RDS. I futuri DBA non saranno in grado di connettersi direttamente ai server PROD, ma potranno utilizzarli Jenkins posti di lavoro per un insieme limitato di operazioni. Il DBA avvia il lavoro e dopo qualche tempo riceve una lettera con un rapporto sul completamento di questa operazione. Diamo un'occhiata ai modi per presentare questi risultati all'utente.

Testo normale

Cominciamo dal più banale. Il primo metodo è così semplice che non c'è davvero nulla di cui parlare (l'autore di seguito utilizza i lavori FreeStyle):

sqlcmd fa qualcosa e lo presentiamo all'utente. Ideale, ad esempio, per i processi di backup:

Non dimenticare, tra l'altro, che in RDS il backup/ripristino è asincrono, quindi è necessario attendere:

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

Secondo metodo, CSV

Anche qui tutto è molto semplice:

Tuttavia questo metodo funziona solo se i dati restituiti nel CSV sono "semplici". Se provi a restituire, ad esempio, un elenco di TOP N query che richiedono un uso intensivo della CPU in questo modo, il CSV si "corroderà" a causa del fatto che il testo della query può contenere qualsiasi carattere: virgole, virgolette e persino interruzioni di riga. Pertanto, abbiamo bisogno di qualcosa di più complicato.

Bellissimi segni in HTML

Ti darò subito uno snippet di codice

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

A proposito, fai attenzione alla linea con System.Management.Automation.PSCustomObject, è magica, se c'è esattamente una linea nella griglia, allora sono sorti dei problemi. La soluzione è stata presa da Internet senza molta comprensione. Di conseguenza, otterrai un output formattato in questo modo:

Disegnare grafici

Attenzione: codice vizioso qui sotto!
C'è una query divertente sul server SQL che mostra la CPU negli ultimi N minuti: si scopre che il compagno maggiore ricorda tutto! Prova questo quiz:

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

Ora, utilizzando questa formattazione (variabile $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>

Possiamo formare il corpo della lettera:

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

Che sarà simile a questo:

Sì, il signore ne sa molto di perversioni! È interessante che questo codice contenga: Powershell (scritto al suo interno), SQL, Xquery, HTML. È un peccato che non possiamo aggiungere Javascript all’HTML (poiché serve per scrivere), ma rifinire il codice Python (che può essere utilizzato in SQL) è un dovere di tutti!

Output della traccia del profiler SQL

È chiaro che la traccia non entrerà nel CSV a causa del campo TextData. Ma anche la visualizzazione di una griglia di traccia in una lettera è strana, sia per le dimensioni sia perché questi dati vengono spesso utilizzati per ulteriori analisi. Pertanto, facciamo quanto segue: chiamiamo via invocare-SqlCmd un certo copione, nelle profondità del quale è fatto

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

Avanti, avanti другом Sul server accessibile dal DBA è presente un database Traces con un template vuoto, la Model plate, pronta ad accettare tutte le colonne specificate. Copiamo questo modello in una nuova tabella con un nome univoco:

$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

E ora possiamo scrivere la nostra traccia al suo interno utilizzando Data.SqlClient.SqlBulkCopy - Ne ho già dato un esempio sopra. Sì, sarebbe anche carino mascherare le costanti in TextData:

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

Sostituiamo i numeri lunghi più di un carattere con 999 e sostituiamo le stringhe più lunghe di un carattere con 'str'. I numeri da 0 a 9 sono spesso usati come flag e non li tocchiamo, così come si trovano spesso stringhe vuote e di un solo carattere: "Y", "N", ecc.

Aggiungiamo un po' di colore alle nostre vite (rigorosamente 18+)

Nelle tabelle è spesso necessario evidenziare le celle che richiedono attenzione. Ad esempio, FAILS, alto livello di frammentazione, ecc. Naturalmente, questo può essere fatto in SQL semplice, generando HTML utilizzando PRINT e impostando il tipo di file su HTML in 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>'

Perché ho scritto questo codice?

Ma esiste una soluzione più bella. Converti in HTML non ci permette di colorare le celle, ma possiamo farlo a posteriori. Ad esempio, vogliamo selezionare celle con un livello di frammentazione superiore a 80 e superiore a 90. Aggiungiamo gli stili:

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

Nella query stessa aggiungeremo una colonna fittizia immediatamente prima colonna che vogliamo colorare. La colonna dovrebbe essere chiamata SQLmarkup-qualcosa:

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,

Ora, ricevuto l'HTML generato da Powershell, rimuoveremo la colonna fittizia dall'intestazione e nel corpo dei dati trasferiremo il valore dalla colonna allo stile. Questo viene fatto con solo due sostituzioni:

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

Il risultato:

Non è elegante? Anche se no, questa colorazione mi ricorda qualcosa

Fonte: habr.com

Automazione del server SQL in Jenkins: restituendo il risultato magnificamente