🥇Automatisation du serveur SQL dans Jenkins : renvoyer le résultat magnifiquement

Encore poursuivant le thème de l'arrangement Zéro Touch PROD sous RDS. Les futurs DBA ne pourront pas se connecter directement aux serveurs PROD, mais pourront utiliser Jenkins emplois pour un ensemble limité d’opérations. Le DBA lance le travail et reçoit après un certain temps une lettre avec un rapport sur l'achèvement de cette opération. Voyons comment présenter ces résultats à l'utilisateur.

Plain Text

Commençons par le plus trivial. La première méthode est si simple qu’il n’y a vraiment rien à dire (l’auteur utilise ci-après les jobs FreeStyle) :

sqlcmd fait quelque chose et nous le présentons à l'utilisateur. Idéal pour, par exemple, les tâches de sauvegarde :

N'oubliez pas d'ailleurs que sous RDS la sauvegarde/restauration est asynchrone, il faut donc 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} 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

Deuxième méthode, CSV

Tout ici est également très simple :

Cependant, cette méthode ne fonctionne que si les données renvoyées dans le CSV sont « simples ». Si vous essayez de renvoyer, par exemple, une liste de requêtes TOP N gourmandes en CPU de cette manière, le CSV se « corrodera » en raison du fait que le texte de la requête peut contenir n'importe quel caractère - virgules, guillemets et même des sauts de ligne. Par conséquent, nous avons besoin de quelque chose de plus compliqué.

De beaux signes en HTML

Je vais vous donner un extrait de code tout de suite

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

À propos, faites attention à la ligne avec System.Management.Automation.PSCustomObject, c'est magique : s'il y a exactement une ligne dans la grille, alors certains problèmes surviennent. La solution a été extraite d’Internet sans grande compréhension. En conséquence, vous obtiendrez une sortie formatée comme ceci :

Dessiner des graphiques

Attention : code pervers ci-dessous !
Il y a une requête amusante sur le serveur SQL qui affiche le CPU pour les N dernières minutes - il s'avère que le camarade major se souvient de tout ! Essayez ce 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);

Maintenant, en utilisant ce formatage ($Fragment variable)

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

On peut former le corps de la lettre :

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

Ce qui ressemblera à ceci :

Oui, Monsieur s'y connaît en perversions ! Il est intéressant que ce code contienne : Powershell (écrit dedans), SQL, Xquery, HTML. C'est dommage qu'on ne puisse pas ajouter du Javascript au HTML (puisque c'est pour l'écriture), mais peaufiner le code Python (qui peut être utilisé en SQL) est le devoir de chacun !

Sortie de trace du profileur SQL

Il est clair que la trace ne rentrera pas dans le CSV à cause du champ TextData. Mais afficher une grille de traces dans une lettre est également étrange - à la fois en raison de sa taille et parce que ces données sont souvent utilisées pour une analyse plus approfondie. Par conséquent, nous procédons comme suit : nous appelons via invoquer-SqlCmd un certain scénario, au fond duquel il est fait

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

Ensuite, sur autre Sur le serveur accessible par le DBA, il existe une base de données Traces avec un modèle vide, le Model plate, prêt à accepter toutes les colonnes spécifié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 écrire notre trace en utilisant Data.SqlClient.SqlBulkCopy - J'en ai déjà donné un exemple ci-dessus. Oui, ce serait aussi bien de masquer les 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 par 999 et les chaînes de plus d’un caractère par « str ». Les nombres de 0 à 9 sont souvent utilisés comme drapeaux, et nous n'y touchons pas, ainsi que des chaînes vides et à un seul caractère - « Y », « N », etc.

Ajoutons de la couleur à nos vies (strictement 18+)

Dans les tableaux, vous souhaitez souvent mettre en évidence les cellules qui nécessitent votre attention. Par exemple, ÉCHECS, niveau élevé de fragmentation, etc. Bien sûr, cela peut être fait en SQL nu, en générant du HTML à l'aide de PRINT et en définissant le type de fichier sur HTML dans 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>'

Pourquoi ai-je écrit un tel code ?

Mais il existe une solution plus belle. Convertir en HTML ne nous permet pas de colorer les cellules, mais nous pouvons le faire après coup. Par exemple, nous souhaitons sélectionner des cellules avec un niveau de fragmentation supérieur à 80 et supérieur à 90. Ajoutons des styles :

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

Dans la requête elle-même, nous ajouterons une colonne factice immédiatement avant colonne que nous voulons colorer. La colonne devrait s'appeler Balisage SQL-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 reçu le HTML généré par Powershell, nous supprimerons la colonne factice de l'en-tête et, dans le corps des données, nous transférerons la valeur de la colonne vers le style. Cela se fait avec seulement deux substitutions :

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

Résultat:

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

Source: habr.com

Automatisation du serveur SQL dans Jenkins : renvoyer le résultat magnifiquement