🥇اتوماسیون سرور SQL در جنکینز: بازگرداندن نتیجه به زیبایی

دوباره ادامه موضوع چیدمان Zero Touch PROD تحت RDS. DBA های آینده نمی توانند مستقیماً به سرورهای PROD متصل شوند، اما می توانند استفاده کنند جنکینز مشاغل برای مجموعه محدودی از عملیات. DBA کار را راه اندازی می کند و پس از مدتی نامه ای با گزارش تکمیل این عملیات دریافت می کند. بیایید راه هایی را برای ارائه این نتایج به کاربر بررسی کنیم.

متن ساده

بیایید با بی اهمیت ترین شروع کنیم. روش اول آنقدر ساده است که واقعاً چیزی برای صحبت در مورد آن وجود ندارد (نویسنده از این پس از FreeStyle Jobs استفاده می کند):

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 "ساده" باشد. برای مثال، اگر سعی کنید فهرستی از پرس و جوهای فشرده CPU TOP N را به این روش برگردانید، 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 وجود دارد که CPU را برای N دقیقه آخر نمایش می دهد - معلوم می شود که رفیق سرگرد همه چیز را به خاطر می آورد! این مسابقه را امتحان کنید:

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 اضافه کنیم (چون برای نوشتن است)، اما صیقل دادن کد پایتون (که می تواند در SQL استفاده شود) وظیفه همه است!

خروجی ردیابی پروفایلر SQL

واضح است که ردیابی به دلیل فیلد TextData در CSV قرار نمی گیرد. اما نمایش یک شبکه ردیابی در یک حرف نیز عجیب است - هم به دلیل اندازه و هم به این دلیل که این داده ها اغلب برای تجزیه و تحلیل بیشتر استفاده می شوند. بنابراین، ما موارد زیر را انجام می دهیم: از طریق تماس می گیریم invoke-SqlCmd فیلمنامه خاصی که در عمق آن انجام شده است

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

بعد، در دوست در سرور قابل دسترسی توسط DBA، یک پایگاه داده Traces با یک الگوی خالی، صفحه Model، وجود دارد که آماده پذیرش تمام ستون های مشخص شده است. ما این مدل را در یک جدول جدید با یک نام منحصر به فرد کپی می کنیم:

$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 خالی، تولید HTML با استفاده از PRINT، و تنظیم نوع فایل به HTML در 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>'

چرا همچین کدی نوشتم؟

اما راه حل زیباتری وجود دارد. 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>

در خود پرس و جو یک ستون ساختگی اضافه می کنیم بلافاصله قبل از ستونی که می خواهیم رنگ کنیم. ستون باید فراخوانی شود SQLmarkup-چیزی:

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,

اکنون با دریافت HTML تولید شده توسط Powershell، ستون ساختگی را از هدر حذف می کنیم و در بدنه داده ها مقدار را از ستون به استایل منتقل می کنیم. این فقط با دو تعویض انجام می شود:

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

یافته ها:

شیک نیست؟ اگرچه نه، این رنگ آمیزی مرا یاد چیزی می اندازد

منبع: www.habr.com

اتوماسیون سرور SQL در جنکینز: بازگرداندن نتیجه به زیبایی