🥇Automatização do SQL Server no Jenkins: retornando resultados de forma bonita

Novamente continuando o tema de configuração Zero Touch PROD para RDS. Futuros DBAs não terão a capacidade de se conectar diretamente aos servidores PROD, mas poderão utilizar Jenkins jobs para um conjunto limitado de operações. O DBA inicia o job e, após algum tempo, recebe um e-mail com o relatório sobre a execução dessa operação. Vamos considerar maneiras de apresentar esses resultados ao usuário.

Texto Simples

Vamos começar com o mais trivial. O primeiro método é tão simples que não há muito a dizer (o autor usa aqui e adiante FreeStyle jobs):

sqlcmd algo é executado e nós apresentamos isso ao usuário. É ideal para, por exemplo, jobs de backup:

Não esqueçamos, aliás, que para RDS o backup/restauração é assíncrono, então é necessário aguardar:

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ído pelo nome do banco de dados pelo PowerShell
select @stat=lifecycle,@info=taskinfo from @rds where id=@xid
if @stat not in ('ERROR','SUCCESS','CANCELLED') goto again

O segundo método, CSV

Aqui tudo também é muito simples:

No entanto, esse método só funciona se os dados retornados em CSV forem "simples". Se você tentar retornar, por exemplo, uma lista de TOP N consultas que consomem CPU, o CSV "se desfigurará" devido ao fato de que o texto da consulta pode conter quaisquer caracteres - vírgulas, aspas e até quebras de linha. Portanto, precisaremos de algo mais complexo.

Tabelas bonitinhas em HTML

Vou apresentar imediatamente um 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>Minha tabela</h2>"
  $Result | ConvertTo-HTML -Title "Rows" -Head $header -body $body `
    | Out-File "res.log" -Append -Encoding UTF8
  } else {
    "<h3>Sem dados</h3>" | Out-File "res.log" -Append -Encoding UTF8
  }

Aliás, preste atenção na linha com System.Management.Automation.PSCustomObject, ela é mágica; se no grid houver exatamente uma linha, podem ocorrer alguns problemas. A solução foi retirada da internet sem muito entendimento. Como resultado, você obterá uma saída formatada aproximadamente assim:

Desenhando gráficos

Atenção: o código abaixo é exagerado!
Há uma consulta divertida no SQL Server que exibe a CPU dos últimos N minutos - parece que o camarada major se lembra de tudo! Tente essa query:

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

Agora, usando essa formatação (variável $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 o corpo do e-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 = $item.EventTime $sqlcpu = $item.SQLCPU $other = $item.OtherCPU $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 vai parecer assim:

Sim, o senhor sabe sobre peculiaridades! É interessante que esse código contém: Powershell (nele escrito), SQL, Xquery, HTML. É uma pena que não possamos adicionar Javascript ao HTML (já que é para o e-mail), mas aprimorar o código Python (que pode ser usado em SQL) é um dever de todos!

Saída do SQL profiler trace

É evidente que o trace não caberá em CSV devido ao campo TextData. Mas exibir o trace em grid no e-mail também é estranho, tanto pelo tamanho quanto pelo fato de que esses dados geralmente são usados para análise posterior. Portanto, fazemos o seguinte: chamamos através de invoke-SqlCmd um certo script, no qual é feito

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

Em seguida, na um amigo servidor, disponível ao DBA, há um banco de Traces com uma estrutura vazia, uma tabela Model, pronta para receber todas as colunas mencionadas. Copiamos esse modelo para uma nova tabela com um nome ú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

E agora podemos gravar nosso trace nela usando Data.SqlClient.SqlBulkCopy — um exemplo disso já mencionei acima. Sim, também seria bom fazer o masking de constantes em TextData:

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

Substituímos números com mais de um símbolo de comprimento por 999, enquanto strings com mais de um símbolo são substituídas por 'str'. Números de 0 a 9 são frequentemente usados como flags, e não os tocamos, assim como strings vazias e de um símbolo - entre elas frequentemente ocorrem 'Y', 'N', etc.

Vamos adicionar cores à nossa vida (estritamente 18+)

Em tabelas, frequentemente queremos destacar células que requerem atenção. Por exemplo, FAILS, alto nível de fragmentação, etc. Claro, isso também pode ser feito usando SQL puro, gerando HTML com o comando PRINT, e configurando o tipo de arquivo como HTML no Jenkins:

declare @body varchar(max), @chunk varchar(max)
set @body='<font face="Lucida Console" size="3">'
set @body=@body+'<b>Nome do servidor: '+@@servername+'</b><br>'
set @body=@body+'<br><br>'
set @body=@body+'<table><tr><th>Trabalho</th><th>Última Execução</th><th>Duração Média, seg</th><th>Última Execução, seg</th><th>Último 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<>'Sucesso' then '<font color="red">' else '' end+
      LastStatus+
      case when LastStatus<>'Sucesso' 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 que eu escrevi esse código?

Mas há uma solução mais bonita. ConvertTo-HTML não nos permite colorir as células, mas podemos fazer isso posteriormente. Por exemplo, queremos destacar as células com nível de fragmentação superior a 80 e mais de 90. Vamos adicionar estilos:

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

Na própria query, adicionaremos uma coluna fictícia diretamente antes da coluna que queremos colorir. A coluna deve ser chamada de 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,

Agora, obtendo o HTML criado pelo Powershell, removeremos a coluna fictícia do cabeçalho, e no corpo dos dados teremos o valor da coluna no estilo. Isso é feito em apenas duas substituições:

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

Resultado:

Não é verdade, elegante? Embora não, essa coloração me lembra algo

Fonte: habr.com