🥇Automatización de SQL Server en Jenkins: presentamos los resultados de forma atractiva

De nuevo continuando con el tema de la organización Zero Touch PROD bajo RDS. Los futuros DBA no podrán conectarse directamente a los servidores PROD, pero podrán utilizar Jenkins jobs para un conjunto limitado de operaciones. El DBA inicia un job y, después de un tiempo, recibe un correo con un informe sobre la ejecución de esta operación. Vamos a revisar las formas de presentar estos resultados al usuario.

Texto plano

Empezaremos con lo más trivial. La primera forma es tan simple que, en realidad, no hay mucho de qué hablar (el autor aquí y en adelante utiliza jobs de FreeStyle):

sqlcmd ejecuta algo, y se lo presentamos al usuario. Ideal para, por ejemplo, jobs de respaldo:

No olvidemos, por cierto, que bajo RDS el backup/restauración es asíncrono, así que debemos esperar:

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} sustituido con el nombre de la base de datos por powershell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

La segunda forma, CSV

Aquí también es muy simple:

Sin embargo, este método solo funciona si los datos devueltos en CSV son 'simples'. Si intentas devolver, por ejemplo, una lista de las N consultas que más usan CPU, el CSV 'se romperá' porque el texto de la consulta puede contener cualquier carácter: comas, comillas e incluso saltos de línea. Por lo tanto, necesitaremos algo más complicado.

Tablas bonitas en HTML

Voy a proporcionar un fragmento de código

$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>Mi tabla</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>No hay datos</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Por cierto, presta atención a la línea con System.Management.Automation.PSCustomObject, es mágica, si en la cuadrícula hay exactamente una fila, pueden surgir algunos problemas. La solución fue tomada de Internet sin profundizar mucho. Como resultado, obtendrás una salida formateada de esta manera:

Dibujamos gráficos

Atención: ¡el código retorcido a continuación!
Hay una consulta divertida en SQL Server que muestra el uso de CPU en los últimos N minutos: ¡resulta que el compañero mayor lo registra todo! Prueba esta consulta:

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

Ahora, usando tal formato (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>

Podemos formar el cuerpo del correo:

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

Que se verá así:

Sí, monsieur sabe sobre perversidades! Curiosamente, este código contiene: Powershell (en el que está escrito), SQL, Xquery, HTML. Lástima que no podemos añadir Javascript a HTML (ya que es para el correo), pero perfeccionar el código Python (que se puede usar en SQL) es deber de todos!

Salida del rastreo del perfilador SQL

Está claro que el rastreo "no entrará" en CSV debido al campo TextData. Pero también es extraño mostrar el rastreo en una cuadrícula en el correo — tanto por el tamaño como porque a menudo estos datos se utilizan para un análisis posterior. Por lo tanto, hacemos lo siguiente: invocamos a través de invoke-SqlCmd un script que, en su interior, realiza un

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

A continuación, en otra servidor, disponible para el DBA, existe una base de datos Traces con un modelo vacío, una tabla Model, lista para recibir todas las columnas especificadas. Copiamos este modelo en una nueva tabla con un nombre único:

$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

Y ahora podemos escribir nuestro rastreo en ella usando Data.SqlClient.SqlBulkCopy — un ejemplo de esto ya lo he proporcionado arriba. Sí, también sería bueno hacer un enmascaramiento de constantes en TextData:

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

Sustituimos números de más de un carácter de longitud por 999, y las cadenas más largas de un carácter por 'str'. Los números del 0 al 9 se utilizan frecuentemente como banderas, y no los tocamos, al igual que las cadenas vacías y de un solo carácter, que a menudo incluyen 'Y', 'N', etc.

Añadamos color a nuestra vida (solo 18+)

A menudo, queremos resaltar celdas en las tablas que requieren atención. Por ejemplo, FALLAS, alto nivel de fragmentación, etc. Por supuesto, esto también se puede hacer con SQL puro, generando HTML mediante PRINT, y en Jenkins configurando el tipo de archivo como HTML:

declara @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Nombre del servidor: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Job</th><th>Última ejecución</th><th>Duración promedio, seg</th><th>Última ejecución, seg</th><th>Último estado</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>'

¿Por qué escribí tal código?

Pero hay una solución más estética. ConvertTo-HTML no nos permite colorear las celdas, pero podemos hacerlo posteriormente. Por ejemplo, queremos resaltar celdas con un nivel de fragmentación superior a 80 y a 90. Añadamos estilos:

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

En la propia consulta, añadiremos una columna ficticia justo antes de la columna que queremos colorear. La columna debe llamarse SQLmarkup-algo:

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,

Ahora, al tener el HTML creado por PowerShell, eliminaremos la columna ficticia del encabezado y trasladaremos el valor de la columna al estilo en el cuerpo de datos. Esto se hace con solo dos sustituciones:

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

Resultado:

¿No es elegante? Aunque, ahora que lo pienso, esta coloración me recuerda a

Fuente: habr.com

Automatización de SQL Server en Jenkins: mostrando resultados de forma atractiva