🥇أتمتة خادم SQL في جنكينز: إرجاع النتيجة بشكل جميل

ثانية مواصلة موضوع الترتيب منتج زيرو تاتش تحت RDS. لن يتمكن مسؤولو قواعد البيانات المستقبليون من الاتصال بخوادم 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 بهذه الطريقة، فسوف "يتآكل" ملف 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 دقيقة - اتضح أن الرفيق الرائد يتذكر كل شيء! جرب هذا الاختبار:

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

الآن، باستخدام هذا التنسيق (متغير الجزء ($)

<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

من الواضح أن التتبع لن يتناسب مع ملف CSV بسبب حقل TextData. لكن عرض شبكة التتبع في رسالة أمر غريب أيضًا - سواء بسبب الحجم أو لأن هذه البيانات تُستخدم غالبًا لمزيد من التحليل. لذلك نقوم بما يلي: نتصل عبر استدعاء-SqlCmd نص معين، في أعماقه يتم ذلك

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

بجانب او بجوار другом على الخادم الذي يمكن الوصول إليه بواسطة DBA، توجد قاعدة بيانات تتبع مع قالب فارغ، لوحة النموذج، جاهزة لقبول جميع الأعمدة المحددة. ننسخ هذا النموذج إلى جدول جديد باسم فريد:

$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+)

في الجداول، غالبًا ما تريد تمييز الخلايا التي تتطلب الاهتمام. على سبيل المثال، فشل، مستوى عال من التجزئة، وما إلى ذلك. بالطبع، يمكن القيام بذلك في 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>'

لماذا كتبت مثل هذا الرمز؟

لكن هناك حل أجمل . تحويل إلى 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,

الآن، بعد أن تلقينا HTML الذي تم إنشاؤه بواسطة Powershell، سنقوم بإزالة العمود الوهمي من الرأس، وفي نص البيانات سنقوم بنقل القيمة من العمود إلى النمط. يتم ذلك من خلال استبدالين فقط:

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

النتيجة:

أليست أنيقة؟ رغم أن لا، هذا التلوين يذكرني بشيء ما

المصدر: www.habr.com

أتمتة خادم SQL في جنكينز: إرجاع النتيجة بشكل جميل