ื ืืืืืื . ืฆืืงืื ืคึฟื DBAs ืืืขื ื ืืฉื ืงืขื ืขื ืฆื ืคืึทืจืืื ืื ืฆื PROD ืกืขืจืืืขืจืก ืืืืึทื, ืึธืืขืจ ืืื ืงืขื ืขื ื ืืฆื ืืืฉืขื ืงืื ืก ืืืฉืึธืืก ืคึฟืึทืจ ืึท ืืืืืืขื ืืึทื ื ืคืื ืึทืคึผืขืจืืืฉืึทื ื. ืื ืืืึท ืืึธื ืืฉืื ืึทืจืืขื ืืื ื ืึธื ืขืืืขืืข ืืึธื ืืึทืงืืืขื ืึท ืืจืืื ืืื ืึท ืืึทืจืืื ืืืืฃ ืื ืงืึทืืคึผืืืฉืึทื ืคืื ืืขื ืึธืคึผืขืจืึทืฆืืข. ืืื ืก ืงืืง ืืื ืืืขืื ืฆื ืคืึธืจืฉืืขืื ืื ืจืขืืืืืึทืื ืฆื ืืขืจ ืืึทื ืืฆืขืจ.
ืืืืืืข ืืขืงืกื
ืืื ืก ืึธื ืืืืื ืืื ืื ืืขืจืกื ื ืืฉืืืง. ืืขืจ ืขืจืฉืืขืจ ืืืคึฟื ืืื ืึทืืื ืคึผืฉืื ืึทื ืขืก ืก 'ืืึทืงืข ืืึธืจื ืืฉื ืฆื ืจืขืื ืืืขืื (ืืขืจ ืืืืจ ื ืืฆื ืคืจืขืขืกืืืืข ืืืฉืึธืืก ืืืืืืขืจ):
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
ืึทืืฅ ืืึธ ืืื ืืืื ืืืืขืจ ืคึผืฉืื:
ืึธืืขืจ, ืืขื ืืืคึฟื ืึทืจืืขื ืืืืื ืืืื ืื ืืึทืื ืืืืืขืงืขืจื ืืื ืื ืงืกืื ืืื "ืคึผืฉืื". ืืืื ืืืจ ืคึผืจืืืืจื ืฆื ืฆืืจืืงืงืืืขื, ืคึฟืึทืจ ืืืึทืฉืคึผืื, ืึท ืจืฉืืื ืคืื 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. ืขืก ืืื ืึท ืฉืึธื ืึทื ืืืจ ืงืขื ืขื ื ืืฉื ืืืืื ืืืฉืึทืืืึทืกืงืจืืคึผื ืฆื HTML (ืืืืึทื ืขืก ืืื ืคึฟืึทืจ ืฉืจืืืื), ืึธืืขืจ ืคึผืึทืืืฉืื ื ืคึผืืืืึธื ืงืึธื (ืืืึธืก ืงืขื ืขื ืืืื ืืขืืืืื ื ืืื ืกืงื) ืืื ืึทืืขืืขื ืก ืคืืืื!
SQL ืคึผืจืึธืคืืืขืจ ืฉืคึผืืจ ืจืขืืืืืึทื
ืขืก ืืื ืงืืึธืจ ืึทื ืื ืฉืคึผืืจ ืืืขื ื ืืฉื ืคึผืึทืกืืง ืืื ืื ืงืกืื ืืืืึทื ืคืื ืื ืืขืงืกืืืึทืืึท ืคืขืื. ืืืขืจ ืืืืึทืื ืืืง ืึท ืฉืคึผืืจ ืืจืื ืืื ืึท ืืจืืื ืืื ืืืื ืืึธืื ืข - ืืืืืข ืืืืึทื ืคืื ืื ืืจืืืก ืืื ืืืืึทื ืื ืืึทืื ืืขื ืขื ืึธืคื ืืขื ืืฆื ืคึฟืึทืจ ืืืืึทืืขืจ ืึทื ืึทืืืกืืก. ืืขืจืืืขืจ, ืืืจ ืืึธื ืื ืคืืืืขื ืืข: ืืืจ ืจืืคื ืืืจื ืื ืืืึธืืง-SqlCmd ืึท ืืขืืืืกืขืจ ืฉืจืืคื, ืืื ืืืขืืื ืขืก ืืืขืจื ืืขืืึธื ืืื ืืืคืขื ืืฉื
select
SPID,EventClass,TextData,
Duration,Reads,Writes,CPU,
StartTime,EndTime,DatabaseName,HostName,
ApplicationName,LoginName
from ::fn_trace_gettable ( @filename , default ) ืืืืึทืืขืจ, ืืืืฃ ืคืจืืึทื ื ืกืขืจืืืืจืขืจ, ืฆืืืจืืืืขื ืคืืจ ืื ืื-ืื-ืขื, ืืื ืื ื ืืจืขืืก ืืืื ืืืืข ืืื ื ืืืืืืื ืืขืืคืืื, ื ืืืืขื ืืืืขืืข, ืืจืืื ืฆื ืื ื ืขืืขื ืืืข ืกืคืขืฆืืคืืฆืืจืืข ืงืืืื'ืก. ืืืจ ืงืืคืืจื ืืขื ืืืืขื ืฆื ื ื ืืืขืจ ืืืืขืืข ืืื ื ืืื ืืง ื ืืืขื:
$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 - ืืื ืืื ืฉืืื ืืืืื ืืขืืขืื ื ืืืืฉืคืื ืืขืจืคืื. ืืึธ, ืขืก ืืืึธืื ืืืื ืืืื ืคืืึทื ืฆื ืืึทืกืงืข ืงืึทื ืกืืึทื ืฅ ืืื ืืขืงืกืืืึทืืึท:
# mask data
foreach ($Row in $Result)
{
$v = $Row["TextData"]
$v = $v -replace "'([^']{2,})'", "'str'" -replace "[0-9][0-9]+", '999'
$Row["TextData"] = $v
}
ืืืจ ืคืึทืจืืืึทืื ื ืืืขืจื ืืขืจ ืืื ืืืื ืืึทืจืึทืงืืขืจ ืืึทื ื ืืื 999, ืืื ืืืจ ืคืึทืจืืืึทืื ืกืืจืื ืืก ืืขืจ ืืื ืืืื ืืึทืจืึทืงืืขืจ ืืื 'ืกืืจ'. ื ืืืขืจื ืคืื 0 ืืื 9 ืืขื ืขื ืึธืคื ืืขื ืืฆื ืืื ืคืืึทืืก, ืืื ืืืจ ืึธื ืจืืจื ืืื ื ืืฉื, ืืื ืืืื ืืืืืืง ืืื ืืืื-ืืึทืจืึทืงืืขืจ ืกืืจืื ืืก - 'ื', 'ืขื', ืืื"ื ื ืืขื ืขื ืึธืคื ืืขืคืื ืขื ืฆืืืืฉื ืืื.
ืืึธืืืจ ืืืืื ืขืืืขืืข ืงืึธืืืจ ืฆื ืืื ืืืขืจ ืืขืื (ืฉืืจืขื ื 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>'
ืคืืจืืืืก ืืื ืืื ืืขืฉืจืืื ืืื ืงืืื?
ืืืขืจ ืขืก ืืื ืึท ืืขืจ ืฉืืื ืืืืืื ื. ืงืึธื ืืืขืจื ืฆื 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
