🥇Automação do SQL server no Jenkins: retornando o resultado lindamente

Novamente continuando o tema do arranjo Zero Touch PROD sob RDS. Os futuros DBAs não poderão se conectar diretamente aos servidores PROD, mas poderão usar Jenkins trabalhos para um conjunto limitado de operações. O DBA lança o job e após algum tempo recebe uma carta informando a conclusão desta operação. Vejamos maneiras de apresentar esses resultados ao usuário.

Plain Text

Vamos começar com o mais trivial. O primeiro método é tão simples que não há realmente nada para falar (o autor daqui em diante usa trabalhos FreeStyle):

sqlcmd faz algo e apresentamos ao usuário. Ideal para, por exemplo, trabalhos de backup:

Não se esqueça, aliás, que no RDS o backup/restauração é assíncrono, então você precisa esperar por isso:

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

Segundo método, CSV

Tudo aqui também é muito simples:

Porém, este método só funciona se os dados retornados no CSV forem "simples". Se você tentar retornar, por exemplo, uma lista de consultas TOP N com uso intensivo de CPU dessa maneira, o CSV “corroerá” devido ao fato de que o texto da consulta pode conter quaisquer caracteres - vírgulas, aspas e até quebras de linha. Portanto, precisamos de algo mais complicado.

Lindos sinais em HTML

Vou te dar um trecho de código imediatamente

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

A propósito, preste atenção na linha com System.Management.Automation.PSCustomObject, é mágico, se houver exatamente uma linha na grade, então surgiram alguns problemas. A solução foi retirada da Internet sem muito entendimento. Como resultado, você obterá uma saída formatada mais ou menos assim:

Desenhando gráficos

Aviso: código excêntrico abaixo!
Há uma consulta engraçada no servidor SQL que exibe a CPU dos últimos N minutos - acontece que o camarada Major se lembra de tudo! Experimente este teste:

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

Agora, usando esta 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 da carta:

$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 ficará assim:

Sim, Monsieur sabe muito sobre perversões! É interessante que este código contenha: Powershell (escrito nele), SQL, Xquery, HTML. É uma pena que não possamos adicionar Javascript ao HTML (já que é para escrita), mas polir o código Python (que pode ser usado em SQL) é dever de todos!

Saída de rastreamento do SQL Profiler

É claro que o rastreamento não caberá no CSV por causa do campo TextData. Mas exibir uma grade de rastreamento em uma carta também é estranho – tanto por causa do tamanho quanto porque esses dados são frequentemente usados para análises posteriores. Portanto, fazemos o seguinte: ligamos via invocar-SqlCmd um certo roteiro, em cujas profundezas isso é feito

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

A seguir, em другом No servidor acessível pelo DBA, existe um banco de dados Traces com um template vazio, a placa Model, pronto para aceitar todas as colunas especificadas. Copiamos este modelo para uma nova tabela com um nome exclusivo:

$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 escrever nosso rastreamento nele usando Dados.SqlClient.SqlBulkCopy - Já dei um exemplo disso acima. Sim, também seria bom mascarar 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 caractere por 999 e substituímos strings com mais de um caractere por 'str'. Números de 0 a 9 são frequentemente usados como sinalizadores e não os tocamos, assim como strings vazias e de um único caractere - 'Y', 'N', etc.

Vamos adicionar um pouco de cor às nossas vidas (estritamente maiores de 18 anos)

Nas tabelas, muitas vezes você deseja destacar células que requerem atenção. Por exemplo, FAILS, alto nível de fragmentação, etc. Claro, isso pode ser feito em SQL simples, gerando HTML usando PRINT e definindo 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>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>'

Por que escrevi esse código?

Mas existe uma solução mais bonita. Converter para HTML não nos permite colorir as células, mas podemos fazer isso depois do fato. Por exemplo, queremos selecionar células com um nível de fragmentação superior a 80 e superior a 90. Vamos adicionar estilos:

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

Na própria consulta adicionaremos uma coluna fictícia imediatamente antes coluna que queremos colorir. A coluna deve ser chamada Marcação SQL-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, tendo recebido o HTML gerado pelo Powershell, retiraremos a coluna fictícia do cabeçalho, e no corpo dos dados transferiremos o valor da coluna para o estilo. Isso é feito com apenas duas substituições:

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

Resultado:

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

Fonte: habr.com

Automação do SQL Server no Jenkins: retornando o resultado lindamente