אוטומציה של שרת SQL בג'נקינס: החזרת התוצאה יפה

שוב המשך נושא הסידור Zero Touch PROD תחת RDS. DBAs עתידיים לא יוכלו להתחבר לשרתי 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, זה קסום, אם יש בדיוק שורה אחת ברשת אז נוצרו כמה בעיות. הפתרון נלקח מהאינטרנט בלי הרבה הבנה. כתוצאה מכך, תקבל פלט בפורמט כזה:

ציור גרפים

אזהרה: קוד קינקי למטה!
יש שאילתה מצחיקה בשרת 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);

כעת, באמצעות העיצוב הזה (משתנה $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 profiler

ברור שהעקיבה לא תתאים ל-CSV בגלל השדה TextData. אבל הצגת רשת עקבות במכתב היא גם מוזרה - הן בגלל הגודל והן בגלל שהנתונים האלה משמשים לעתים קרובות לניתוח נוסף. לכן, אנו עושים את הפעולות הבאות: אנו מתקשרים דרך 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 plate, מוכן לקבל את כל העמודות שצוינו. אנו מעתיקים מודל זה לטבלה חדשה עם שם ייחודי:

$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 בג'נקינס:

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>

בשאילתה עצמה נוסיף עמודת דמה מיד לפני עמודה שאנו רוצים לצבוע. יש לקרוא לעמודה 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