Jenkins 中 SQL Server 的自动化：漂亮地返回结果

再次 继续安排的主题 零接触产品 在 RDS 下。 未来的 DBA 将无法直接连接到 PROD 服务器，但可以使用 詹金斯 一组有限操作的作业。 DBA 启动作业，并在一段时间后收到一封包含此操作完成报告的信函。 让我们看看如何向用户呈现这些结果。

纯文本

让我们从最琐碎的事情开始。 第一种方法简单得实在没什么可讲的（笔者下文使用的是FreeStyle jobs）：

命令行 做某事并将其呈现给用户。 例如，非常适合备份作业：

顺便别忘了，RDS下的备份/恢复是异步的，所以你需要等待：

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

第二种方法，CSV

这里的一切也很简单：

但是，此方法仅在 CSV 中返回的数据“简单”时才有效。 例如，如果您尝试以这种方式返回前 N 个 CPU 密集型查询的列表，则 CSV 将“腐蚀”，因为查询文本可以包含任何字符 - 逗号、引号，甚至换行符。 因此，我们需要一些更复杂的东西。

HTML 中的漂亮标志

我马上给你一个代码片段

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

顺便说一句，注意 System.Management.Automation.PSCustomObject 的那一行，它很神奇；如果网格中恰好只有一行，那么就会出现一些问题。 解决方案是从网上抄来的，没有太多理解。 结果，您将得到如下格式的输出：

绘制图表

警告：下面有奇怪的代码！
SQL 服务器上有一个有趣的查询，显示了最后 N 分钟的 CPU - 原来少校同志记得一切！ 试试这个测验：

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

现在，使用这种格式（$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>

我们可以形成信件的正文：

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

看起来像这样：

是的，先生对变态了解很多！ 有趣的是，这段代码包含：Powershell（用它编写）、SQL、Xquery、HTML。 遗憾的是，我们不能将 Javascript 添加到 HTML 中（因为它是用于编写的），但是打磨 Python 代码（可以在 SQL 中使用）是每个人的职责！

SQL 探查器跟踪输出

很明显，由于 TextData 字段，跟踪不适合 CSV。 但在字母中显示跟踪网格也很奇怪 - 既因为大小，也因为这些数据经常用于进一步分析。 因此，我们执行以下操作：我们通过以下方式调用 调用-SqlCmd 某个脚本，在其深处完成

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

接下来，关于 其他 在 DBA 可访问的服务器上，有一个带有空模板（模型板）的 Traces 数据库，准备接受所有指定的列。 我们将此模型复制到具有唯一名称的新表中：

$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 

现在我们可以使用以下命令将跟踪写入其中 数据.SqlClient.SqlBulkCopy - 我已经在上面给出了一个例子。 是的，在 TextData 中屏蔽常量也很好：

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

我们用 999 替换长度超过 0 个字符的数字，用“str”替换长度超过 9 个字符的字符串。 从 XNUMX 到 XNUMX 的数字经常被用作标志，我们不会碰它们，而且空字符串和单字符字符串 - 'Y'，'N' 等也经常出现在其中。

让我们为我们的生活增添一些色彩（仅限18岁以上）

在表格中，您经常想要突出显示需要注意的单元格。 例如，失败、高度碎片化等。 当然，这可以在裸 SQL 中完成，使用 PRINT 生成 HTML，并在 Jenkins 中将文件类型设置为 HTML：

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

我为什么要写这样的代码？

但还有一个更漂亮的解决方案。 转换为 HTML 不允许我们给细胞着色，但我们可以在事后这样做。 例如，我们要选择碎片级别超过80和超过90的单元格。我们来添加样式：

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

在查询本身中，我们将添加一个虚拟列 就在之前 我们想要着色的列。 该列应该称为 SQL标记-某物：

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, 

现在，收到 Powershell 生成的 HTML 后，我们将从标题中删除虚拟列，并在数据正文中将值从列传输到样式。 只需进行两次替换即可完成此操作：

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

结果：


是不是很优雅呢？ 虽然没有，但这个颜色让我想起了一些东西


来源： habr.com