🥇Automatisation SQL server dans Jenkins : présentons le résultat de manière élégante

Encore en poursuivant le sujet de l'aménagement Zero Touch PROD sous RDS. Les futurs DBA ne pourront pas se connecter directement aux serveurs PROD, mais pourront utiliser Jenkins des jobs pour un ensemble limité d'opérations. Le DBA déclenche un job et après un certain temps, reçoit un e-mail avec un rapport sur l'exécution de cette opération. Examinons les façons de présenter ces résultats à l'utilisateur.

Texte brut

Commençons par le plus trivial. La première méthode est si simple qu'il n'y a pas vraiment besoin d'en parler (l'auteur utilise ici et dans ce qui suit des FreeStyle jobs) :

sqlcmd quelque chose s'exécute, et nous le présentons à l'utilisateur. Idéal pour, par exemple, des jobs de sauvegarde :

N'oublions pas, d'ailleurs, que sous RDS la sauvegarde/restauration est asynchrone, donc il faudrait l'attendre :

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} substitué par le nom de db par powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

Deuxième méthode, CSV

Ici, tout est également très simple :

Cependant, cette méthode ne fonctionne que si les données retournées en CSV sont « simples ». Si vous essayez de retourner, par exemple, une liste des requêtes les plus gourmandes en CPU de cette manière, le CSV « se déformera » car le texte de la requête peut contenir n'importe quel caractère — virgules, guillemets, et même des sauts de ligne. Nous aurons donc besoin de quelque chose de plus complexe.

Jolies tables en HTML

Je vais immédiatement donner un extrait de code

$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>Ma table</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Pas de données</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

À propos, faites attention à la ligne avec System.Management.Automation.PSCustomObject, elle est magique, s'il y a exactement une ligne dans la grille, des problèmes survenaient. La solution a été trouvée sur Internet sans trop se pencher dessus. En conséquence, vous obtiendrez une sortie formée à peu près comme ceci :

Dessiner des graphiques

Attention : code tordu ci-dessous !
Il existe une requête amusante sur SQL server qui affiche l'utilisation du CPU au cours des dernières N minutes — il s'avère que le camarade major retient tout ! Essayez cette requête :

DÉCLARE @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'%%') AS x) AS y ORDER BY 1 DESC OPTION (RECOMPILE);

Maintenant, en utilisant ce formatage (variable $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>

Nous pouvons former le corps du mail :

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

Qui ressemblera à ceci :

Eh bien, monsieur sait de quoi il parle en matière de déviances ! Il est intéressant de noter que ce code contient : Powershell (sur lequel il a été écrit), SQL, Xquery, HTML. Dommage que nous ne puissions pas ajouter Javascript à l'HTML (puisque c'est pour un mail), mais perfectionner le code Python (qui peut être utilisé dans SQL) est le devoir de chacun !

Sortie du traceur de SQL profiler

Il est évident que le trace « ne pourra pas » entrer dans un CSV à cause du champ TextData. Mais afficher le trace en grille dans le mail est également étrange — à cause de la taille, et parce que ces données sont souvent utilisées pour une analyse ultérieure. Donc, nous faisons ce qui suit : nous appelons via invoke-SqlCmd un certain script, dans les entrailles duquel se fait

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

Ensuite, sur un autre le serveur, accessible au DBA, il existe une base Traces avec une maquette vide, une table Model, prête à accepter toutes les colonnes indiquées. Nous copions ce modèle dans une nouvelle table avec un nom unique :

$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

Et maintenant, nous pouvons y enregistrer notre trace en utilisant Data.SqlClient.SqlBulkCopy — un exemple de cela a déjà été donné ci-dessus. Oui, il serait également bon de faire un masquage des constantes dans TextData :

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

Nous remplaçons les nombres de plus d'un caractère de long par 999, et les chaînes de plus d'un caractère par ‘str’. Les nombres de 0 à 9 sont souvent utilisés comme indicateurs, et nous ne les touchons pas, tout comme les chaînes vides et à un caractère — parmi eux, on trouve souvent ‘Y’, ‘N’, etc.

Ajoutons de la couleur à notre vie (réservé aux 18 ans et plus)

Dans les tableaux, on souhaite souvent mettre en évidence les cellules qui nécessitent de l'attention. Par exemple, FAILS, un niveau de fragmentation élevé, etc. Bien sûr, cela peut aussi être fait avec du SQL brut, en générant du HTML avec PRINT, et dans Jenkins, nous définissons le type de fichier sur HTML :

déclare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Nom du serveur: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Dernière exécution</th><th>Durée moyenne, sec</th><th>Dernière exécution, sec</th><th>Dernier statut</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<>'Succès' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Succès' 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>'

Pourquoi ai-je écrit ce code ?

Mais il existe une solution plus élégante. ConvertTo-HTML ne nous permet pas de colorer les cellules, mais nous pouvons le faire après coup. Par exemple, nous voulons mettre en évidence les cellules avec un niveau de fragmentation supérieur à 80 et 90. Ajoutons des styles :

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

Dans la requête elle-même, nous ajouterons une colonne fictive juste avant la colonne que nous souhaitons colorer. La colonne doit être nommée SQLmarkup-quelque chose :

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,

Maintenant, après avoir obtenu le HTML généré par PowerShell, nous supprimerons la colonne fictive de l'en-tête, et dans le corps des données, nous transférerons la valeur de la colonne au style. Cela se fait avec seulement deux substitutions :

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

Résultat :

N'est-ce pas, c'est élégant ? Bien que, non, cette coloration me rappelle quelque chose

Source : habr.com

Automatisation de SQL server dans Jenkins : rendons le résultat esthétique