🥇Automate SQL server in Jenkins: return the result beautifully

Again continuing the theme of arrangement Zero Touch PROD under RDS. Future DBAs will not be able to connect to PROD servers directly, but will be able to use Jenkins jobs for a limited set of operations. DBA starts the job and after some time receives a letter with a report on the completion of this operation. Let's look at ways to present these results to the user.

Plain text

Let's start with the most trivial. The first method is so simple that there is nothing to talk about (hereinafter, the author uses FreeStyle jobs):

sqlcmd does something, and we present it to the user. Ideal for e.g. backup jobs:

Do not forget, by the way, that under RDS backup / restore is asynchronous, so you should wait for it:

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

The second way, CSV

Here everything is also very simple:

However, this method only works if the data returned in CSV is "simple". If you try to return, for example, a list of TOP N CPU intensive queries in this way, then the CSV will “correct” due to the fact that the query text can contain any characters - commas, quotes, and even newlines. Therefore, we need something more complicated.

Beautiful HTML Signs

I'll give you a code snippet

$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
  }

By the way, pay attention to the line with System.Management.Automation.PSCustomObject, it is magical, if there is exactly one line in the grid, then there were some problems. The solution is taken from the Internet without much understanding. As a result, you will get an output formatted something like this:

Draw charts

Attention: perverted code below!
There is a funny query on the SQL server that displays the CPU for the last N minutes - it turns out that Comrade Major remembers everything! Try this queer:

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

Now, using this formatting ($Fragment variable)

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

We can form the body of the letter:

$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
    }

Which will look like this:

So yes, Monsieur knows a lot about perversions! It is interesting that this code contains: Powershell (it is written on it), SQL, Xquery, HTML. It's a pity that we can't add Javascript to HTML (since it's for writing), but polishing Python code (which can be used in SQL) is everyone's duty!

SQL profiler trace output

It is clear that the trace will not fit into CSV because of the TextData field. But displaying a trace as a grid in an email is also strange - both because of the size and because this data is often used for further analysis. Therefore, we do the following: we call via invoke-SqlCmd some script, in the bowels of which is done

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

Further on other On a server accessible by the DBA, there is a Traces database with an empty blank, a Model label, ready to accept all the specified columns. We copy this model into a new table with a unique name:

$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

And now we can write our trace into it using Data.SqlClient.SqlBulkCopy I already gave an example of this above. Yes, it would be nice to do masking of constants in TextData:

# mask data
foreach ($Row in $Result)
{ 
  $v = $Row["TextData"]
  $v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
  $Row["TextData"] = $v
}

We replace numbers with more than one character length by 999, and we replace strings longer than one character with 'str'. Numbers from 0 to 9 are often used as flags, and we do not touch them, as well as empty and single-character strings - 'Y', 'N', etc. are often found among them.

Let's add color to our life (strictly 18+)

In tablets, you often want to highlight cells that require attention. For example, FAILS, high level of fragmentation, etc. Of course, this can also be done in raw SQL, generating HTML using PRINT, and setting the HTML file type in 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>'

Why did I write such code?

But there is a more beautiful solution. ConvertTo HTML prevents us from coloring the cells, but we can do it after the fact. For example, we want to select cells with a fragmentation level of more than 80 and more than 90. Let's add styles:

<style>
.SQLmarkup-red { color: red; background-color: yellow; }
.SQLmarkup-yellow { color: black; background-color: #FFFFE0; }
.SQLmarkup-default { color: black; background-color: white; }
</style>

In the Queri itself, we will add a dummy column right up to the column we want to colorize. The column should be named SQLmarkup-something:

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,

Now, having received the HTML generated by Powershell, we will remove the dummy column from the header, and in the body of the data we will transfer the value from the column to the style. This is done with just two substitutions:

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

Result:

Isn't it elegant? Although no, this coloring reminds me of something

Source: habr.com

Automating SQL server in Jenkins: returning the result beautifully