Αυτοματοποίηση του διακομιστή SQL στο Jenkins: επιστρέφοντας όμορφα το αποτέλεσμα

Και πάλι συνεχίζοντας το θέμα της διευθέτησης Zero Touch PROD υπό RDS. Τα μελλοντικά DBA δεν θα μπορούν να συνδέονται απευθείας με διακομιστές PROD, αλλά θα μπορούν να τα χρησιμοποιούν Jenkins θέσεις εργασίας για περιορισμένο σύνολο λειτουργιών. Η 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 είναι "απλά". Εάν προσπαθήσετε να επιστρέψετε, για παράδειγμα, μια λίστα με ερωτήματα εντατικής έντασης CPU N 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, είναι μαγικό· αν υπάρχει ακριβώς μία γραμμή στο πλέγμα, τότε προέκυψαν κάποια προβλήματα. Η λύση ελήφθη από το Διαδίκτυο χωρίς ιδιαίτερη κατανόηση. Ως αποτέλεσμα, θα λάβετε μορφή εξόδου κάπως έτσι:

Σχεδιάζοντας γραφήματα

Προειδοποίηση: kinky κωδικός παρακάτω!
Υπάρχει ένα αστείο ερώτημα στον διακομιστή 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
    }

Το οποίο θα μοιάζει με αυτό:

Ναι, ο Monsieur ξέρει πολλά για τις διαστροφές! Είναι ενδιαφέρον ότι αυτός ο κώδικας περιέχει: 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 με ένα κενό πρότυπο, την πινακίδα μοντέλου, έτοιμη να δεχθεί όλες τις καθορισμένες στήλες. Αντιγράφουμε αυτό το μοντέλο σε έναν νέο πίνακα με μοναδικό όνομα:

$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