Jenkins의 SQL 서버 자동화: 결과를 아름답게 반환

다시 배열의 주제를 계속 제로터치프로드 RDS 하에서. 미래의 DBA는 PROD 서버에 직접 연결할 수는 없지만 다음을 사용할 수 있습니다. 젠킨스 제한된 작업 세트에 대한 작업입니다. DBA는 작업을 시작하고 잠시 후 이 작업 완료에 대한 보고서가 포함된 편지를 받습니다. 이러한 결과를 사용자에게 표시하는 방법을 살펴보겠습니다.

일반 텍스트

가장 사소한 것부터 시작해 보겠습니다. 첫 번째 방법은 너무 간단해서 별로 이야기할 것이 없습니다(저자는 이하 FreeStyle 작업을 사용합니다).

SQLcmd 뭔가를 하면 우리는 그것을 사용자에게 제시합니다. 예를 들어 백업 작업에 적합합니다.

그런데 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로 반환된 데이터가 "단순"인 경우에만 작동합니다. 예를 들어 이러한 방식으로 TOP 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가 있는 줄에 주의하세요. 그리드에 정확히 한 줄만 있으면 몇 가지 문제가 발생합니다. 해결책은 별다른 이해 없이 인터넷에서 가져온 것입니다. 결과적으로 다음과 같은 형식의 출력을 얻게 됩니다.

그래프 그리기

경고: 아래에 이상한 코드가 있습니다!
마지막 N분 동안의 CPU를 표시하는 SQL 서버에 대한 재미있는 쿼리가 있습니다. 알고 보니 전공 동지가 모든 것을 기억하고 있는 것으로 나타났습니다! 이 퀴즈를 시도해 보세요:

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이 포함되어 있다는 점이 흥미롭습니다. HTML에 Javascript를 추가할 수 없다는 점은 아쉽지만(SQL에서 사용할 수 있는) Python 코드를 다듬는 것은 모두의 의무입니다!

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 

이제 다음을 사용하여 추적을 작성할 수 있습니다. Data.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로 바꾸고, 한 문자보다 긴 문자열을 'str'로 바꿉니다. 0부터 9까지의 숫자는 종종 플래그로 사용되며 우리는 이를 건드리지 않으며 빈 단일 문자 문자열인 'Y', 'N' 등이 종종 발견됩니다.

우리 삶에 색을 더해보자(엄격히 18세 이상)

표에서 주의가 필요한 셀을 강조 표시하려는 경우가 많습니다. 예를 들어 FAILS, 높은 수준의 조각화 등이 있습니다. 물론 이 작업은 베어 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>'

내가 왜 이런 코드를 작성했는가?

하지만 더 아름다운 해결책이 있습니다. ConvertTo-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