Jenkins SQLサーバー自動化：結果を美しく返す

RDSの下でZero Touch PRODを配置するというテーマを再び続けます 。 将来の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
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2番目の方法、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の行に注意してください。グリッドに1行しかない場合は魔法です。いくつかの問題がありました。 この決定は、あまり理解することなくインターネットから行われました。 その結果、次のように記述された出力が得られます。

グラフィックを描く

警告：以下の異常なコード！



最後のN分間にCPUを表示するSQLサーバーへの面白いクエリがあります-Comrade Majorはすべてを記憶していることがわかりました！ これを試してください：

 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プロファイラートレース出力

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ベースがあります。 このモデルを一意の名前で新しいテーブルにコピーします。

 $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の複数の文字で数字を置き換え、1文字より長い文字列を「str」で置き換えます。 0から9までの数字はフラグとして使用されることが多く、空の文字や1文字の文字列に加えて、それらには触れません。

私たちの生活に色を追加します（厳密に18+）

タブレットでは、注意が必要な細胞を強調したいことがよくあります。 たとえば、FAILS、高レベルのフラグメンテーションなど。 もちろん、これは、PRINTを使用してHTMLを形成するベアSQLで実行でき、Jenkinsで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>'
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

なぜこのようなコードを書いたのですか？







しかし、より美しい解決策があります。 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>
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

qi自体で、ダミーの列を色付けする列に直接追加します。 列は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,
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

Powershellで作成されたHTMLを受け取ったら、ヘッダーからダミー列を削除し、列の値をデータ本体のスタイルに転送します。 これは、たった2つの順列で行われます。

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

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結果：







エレガントじゃないですか？ いや、この着色は私に思い出させる何か