🥇Automazione SQL server in Jenkins: riportiamo il risultato in modo elegante

Di nuovo continuando il tema dell'organizzazione Zero Touch PROD sotto RDS. I futuri DBA non avranno la possibilità di connettersi direttamente ai server PROD, ma potranno utilizzare Jenkins job per un insieme limitato di operazioni. Il DBA avvia un job e dopo un po' riceve un'email con un report sull'esecuzione di quest'operazione. Esaminiamo i modi per presentare questi risultati all'utente.

Testo semplice

Iniziamo con il modo più banale. Il primo metodo è così semplice che, in fin dei conti, non c'è molto di cui parlare (l'autore qui e oltre utilizza FreeStyle jobs):

sqlcmd esegue qualcosa, e lo presentiamo all'utente. È perfetto per esempio per i backup jobs:

Non dimentichiamo, per inciso, che sotto RDS il backup/restore è asincrono, quindi bisogna aspettarlo:

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} sostituito con il nome del db da powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Secondo metodo, CSV

Qui tutto è molto semplice:

Tuttavia, questo metodo funziona solo se i dati restituiti in CSV sono "semplici". Se provate a restituire, per esempio, un elenco delle TOP N query intensive in termini di CPU, il CSV "si romperà" perché il testo della query può contenere qualsiasi simbolo: virgole, virgolette e persino ritorni a capo. Quindi ci vorrà qualcosa di più complesso.

Tabelle belle in HTML

Riporterò subito un frammento 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>La mia tabella</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Nessun dato</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

A proposito, fate attenzione alla riga con System.Management.Automation.PSCustomObject, è magica, se nel grid c'è esattamente una riga, si presentano alcuni problemi. La soluzione è stata presa da internet senza approfondire particolarmente. Di conseguenza, otterrete un output formattato in questo modo:

Disegniamo grafici

Attenzione: codice distorto qui sotto!
C'è una strana query sul server SQL che restituisce l'uso della CPU negli ultimi N minuti - si scopre che il comandante si ricorda tutto! Provate questa query:

DICHIARA @ts_now bigint = (SELEZIONA cpu_ticks/(cpu_ticks/ms_ticks) FROM sys.dm_os_sys_info CON WITH (NOLOCK)); SELEZIONA TOP(256) DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) COME [EventTime], SQLProcessUtilization COME [SQLCPU], 100 - SystemIdle - SQLProcessUtilization COME [OtherCPU] DA (SELEZIONA record.value('(.\/Record\/@id)[1]', 'int') COME record_id, record.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/SystemIdle)[1]', 'int') COME [SystemIdle], record.value('(.\/Record\/SchedulerMonitorEvent\/SystemHealth\/ProcessUtilization)[1]', 'int') COME [SQLProcessUtilization], [timestamp] DA (SELEZIONA [timestamp], CONVERT(xml, record) COME [record] DA sys.dm_os_ring_buffers CON WITH (NOLOCK) DOVE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' E record LIKE N'%%' ) COME x ) COME y ORDINARE PER 1 DESC OPZIONE (RECOMPILA);

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

$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 apparirà così:

E sì, monsieur sa cosa significa piacere! È interessante che questo codice contenga: Powershell (in cui è scritto), SQL, Xquery, HTML. Peccato che non possiamo aggiungere Javascript all'HTML (dato che è per la mail), ma rifinire il codice Python (che può essere usato in SQL) è un dovere di ciascuno!

Output della traccia SQL profiler

È chiaro che la traccia ‘non entrerà’ in CSV a causa del campo TextData. Ma mostrare la traccia in griglia nella email è anche strano — sia per la dimensione, sia perché spesso questi dati vengono utilizzati per ulteriori analisi. Pertanto facciamo quanto segue: chiamiamo tramite invoke-SqlCmd uno script, nel quale si esegue

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

In seguito, su un'altra server, disponibile al DBA, esiste un database Traces con un modello vuoto, una tabella Model, pronta a ricevere tutte le colonne indicate. Copiamo questo modello in una nuova tabella con un nome unico:

$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 in essa utilizzando Data.SqlClient.SqlBulkCopy — un esempio di questo l'ho già citato sopra. Inoltre, sarebbe utile mascherare le costanti nel 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 di più di un carattere con 999 e le stringhe più lunghe di un carattere le sostituiamo con 'str'. I numeri da 0 a 9 sono spesso utilizzati come flag e non li tocchiamo, così come le stringhe vuote e monocarattere — tra queste ci sono spesso 'Y', 'N', ecc.

Aggiungiamo colore alla nostra vita (solo 18+)

Nelle tabelle spesso si desidera evidenziare le celle che richiedono attenzione. Ad esempio, FAILS, alto livello di frammentazione, ecc. Certamente, si può fare anche con SQL puro, generando HTML tramite PRINT, e in Jenkins impostando il tipo di file HTML:

dichiara @body varchar(max), @chunk varchar(max)
imposta @body='<font face="Lucida Console" size="3">'
imposta @body=@body+'<b>Nome del server: '+@@servername+'</b><br>'
imposta @body=@body+'<br><br>'
imposta @body=@body+'<table><tr><th>Job</th><th>Ultima esecuzione</th><th>Durata media, sec</th><th>Ultima esecuzione, sec</th><th>Ultimo stato</th></tr>'
print @body

DICHIARA tab CURSOR PER SELECT '<tr><td>'+name+'</td><td>'+
  UltimaEsecuzione+'</td><td>'+
  convert(varchar,DurataMedia)+'</td><td>'+
  convert(varchar,UltimaDurata)+'</td><td>'+
    caso quando UltimoStato<>'Riuscito' allora '<font color="red">' altrimenti '' fine+
      UltimoStato+
      caso quando UltimoStato<>'Riuscito' allora '</font>' altrimenti '' fine+
     +'</td><td>'
  da #j2
APRI tab;
PRENDI IL SUCCESSIVO DA tab in @chunk
MENTRE @@FETCH_STATUS = 0
INIZIA
  print @chunk
  PRENDI IL SUCCESSIVO DA tab in @chunk;
FINE
CHIUDI tab;
DEALLOCATE tab;
print '</table>'

Perché ho scritto questo codice?

Ma c'è una soluzione più elegante. ConvertTo-HTML non ci consente di colorare le celle, ma possiamo farlo ex post. Ad esempio, vogliamo evidenziare le celle con un livello di frammentazione maggiore dell'80 e oltre il 90. Aggiungiamo gli stili:

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

Nella query stessa aggiungeremo una colonna fittizia immediatamente prima della colonna che vogliamo colorare. La colonna deve chiamarsi 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, ottenendo l'HTML creato da Powershell, rimuoveremo la colonna fittizia dall'intestazione, e nel corpo dei dati trasferiremo il valore dalla colonna allo stile. Questo viene fatto con sole due sostituzioni:

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

Risultato:

Non è elegante? Anche se mi ricorda qualcosa questa colorazione.

Fonte: habr.com

Automazione di SQL server in Jenkins: restituiamo il risultato in modo elegante