By: Pablo Echeverria | Comments (2) | Related: > Monitoring
Problem
We’re often asked to setup a variety of SQL Server monitoring tasks and we’re so used to using T-SQL code. The problem is that for table-based reports, it may take a lot of time because you need to work with XML; it takes some more time to test, and not to mention that it can’t be reused in another report, so you have to start all over again.
Solution
With PowerShell, you can have a report template that can be reused and doesn’t have to be tested again, you just modify it a little bit and you’re good to go and move to more important stuff.
Also, having a template helps to standardize the design and layout for all of the reports in your organization.
Code
Here is the template, and in the next sub-sections, the important and not so self-explanatory parts will be discussed.
$ErrorActionPreference = "Stop" #stop when an error is encountered
# Declare variables
$server = "YOURSERVER\YOURINSTANCE"
$database = "YOURDATABASE"
$query = @"
SELECT TOP 10 [qt].[text], [qs].[total_worker_time]
FROM [sys].[dm_exec_query_stats] [qs]
CROSS APPLY [sys].[dm_exec_sql_text]([qs].[sql_handle]) [qt]
ORDER BY [total_worker_time] DESC
"@
$querytimeout = 0 #0 means forever, change it as needed
$filter = {1 -eq 1}
$columns = 'text, total_worker_time' -split ", "
$excludeColumns = 'RowError, RowState, Table, ItemArray, HasErrors' -split ", "
$numberOfHeaderRowsToSkip = 1 #for applying the odd/even style to the table
$date = Get-Date
$reportBody = "<h4>Report as of $date</h4>"
$mailServer = "YOURSMTPSERVER"
$mailFrom = "[email protected]"
$mailTo = "[email protected]"
$mailSubject = "$server EMAILSUBJECT"
$reportHeader = "<style>
th {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; background-color:white;}
td {border:solid black 1px; border-collapse:collapse; padding-left:5px; padding-right:5px; padding-top:1px; padding-bottom:1px; color:black; vertical-align: top;}
tr.even {background-color: #lightgray}</style>"
# Import modules
#Import-Module SqlPs –DisableNameChecking #uncomment for running it directly in a ps command prompt
# Run query, apply filtering, convert to HTML with custom header and body
$result = (Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query -querytimeout $querytimeout) | where $filter | select $columns -ExcludeProperty $excludeColumns
if ($result -eq $NULL -or $result.count -eq 0) {
exit #if there are no rows, no report is sent and the job completes successfully
}
[string]$result = $result | ConvertTo-HTML -Fragment | Out-String
[xml]$result = $result.Replace("`0", "") #remove invalid characters that conflict with XML
for ($i = 0; $i -lt $result.table.tr.count - $numberOfHeaderRowsToSkip; $i++) {
$class = $result.CreateAttribute("class")
$class.value = if($i % 2 -eq 0) {"even"} else {"odd"}
$result.table.tr[$i+$numberOfHeaderRowsToSkip].attributes.append($class) | Out-Null
}
$reportBody += $result.InnerXml
# Send report
$message = New-Object System.Net.Mail.MailMessage $mailFrom, $mailTo
$message.Subject = $mailSubject
$message.IsBodyHTML = $true
$message.Body = ConvertTo-HTML -head $reportHeader -body $reportBody
$smtp = New-Object Net.Mail.SmtpClient($mailServer)
$smtp.Send($message)
Query
The query needs to be the exact same one that you run in SSMS, there’s no need to escape any character. Also, it can be the execution of a stored procedure like EXEC [dbo].[sp_who2] or a T-SQL query. Note that the stored procedures may return multiple result sets; it won’t matter because in the next sub-section we will select only the data we need.
Filter
If your query returns exactly what you need, just leave it as it is in the example.
Otherwise, you need to know a bit of PowerShell to get rid of the data you don’t need, like in the following examples:
Subtract two datetime columns, select the minutes, and only include rows that have this greater than a value. Another useful operator is lower than: -lt
($_."endtime" - $_."starttime").Minutes -gt 4
Remove all rows that have “excludestring” in the column A. Note the wildcard character “*” that acts like “%” in T-SQL. Use $_."A" -notlike "" to remove all rows that have an empty value. Another useful operator is like: -like
$_."A" -notlike "*excludestring*"
Remove all rows that have a value of “1” in the column A. Another useful operator is equal: -eq
$_.”A” -ne "1"
Note that your filter needs to be placed between the braces, and you have to join the logic clauses with and/or in the PowerShell syntax -and -or: {$_.”A” -ne "1" -and $_.”A” -ne "2"}
Columns
Note this one needs to be an array, so if you have the column names that you want, just put them separated by comma and at the end perform a split, like it is in the example.
If you explicitly enumerate the columns, you don’t need to specify the next variable “ExcludeColumns”, so just put $excludeColumns = ' ' (the white space is needed because it doesn’t allow an empty space). If you want all columns, put $columns = '*' but you may need to exclude some of them, which is described in the next sub-section.
If you need to perform operations on the values, or add new columns that are not in the result set, you need to create the array first:
$columns = @()
Then add the columns you need:
$columns += "A"
To add a new column subtracting two datetimes, but just return the minute’s part:
$columns += @{Name="Time running";Expression={($_."endtime" - $_."starttime").Minutes}}
To add a new column that is a calculation on a column:
$columns += @{Name="Duration (sec)";Expression={[math]::Round($_."Duration (ms)"/1000)}}
To remove text from a column:
$columns += @{Name="Query";Expression={$_."Query".Replace("<?query --", "").Replace("--?>", "")}}
Exclude columns
As described above, if you don’t need to exclude any column, just put $excludeColumns = ' '.
But if you have specified to include all columns by using $columns = '*', you need to know that for T-SQL code, along with your columns, there are also the following columns included: RowError, RowState, Table, ItemArray, Has Errors. So, because this one also needs to be an array, you can just enter them separated by comma and perform a split on the comma, as in the example.
Report header
This is where you specify the HTML CSS style for your report. If you don’t know CSS, here’s a quick start for you:
- th stands for table header. The code that follows it is the style to be applied to the header of the report table. Note that it is going to put a white background color to it (not transparent as is the default).
- td is used for table cells. The code that follows it is the style to be applied to the cells in the report table (apart from the header). Note that the text is going to be black and is going to be vertically aligned to the top of the cells.
- In this case it is not used, but tr is to specify the style of the table rows.
- The next line, tr.even, is used to specify the style of even rows: the even rows are going to have a light gray background color. You must know that the code in the template is also going to mark the odd rows with the “odd” class, so if you want to specify this style, you can add it here with tr.odd.
Example
Let’s start by defining a query that returns the top 10 most time-consuming queries. The results are ordered by average duration descending, without special characters so it can be exported to Excel (which suits perfectly for our HTML report) and without any extra columns (because we won’t need them anyway):
We’re going to enter this query into our PowerShell script and run it:
And here is part of the result:
Next Steps
- If you run it, you may notice there’s an empty table at the bottom of the report. I have not found a way to remove it, but you can include report footer in there with your signature, if you want.
- You may need to learn about PowerShell syntax and operators to implement more complex filtering and custom columns.
- You may need to learn about CSS to fully customize the report to your own style.
About the author
Pablo Echeverria is a talented database administrator and C#.Net software developer since 2006. Pablo wrote the book "Hands-on data virtualization with Polybase". He is also talented at tuning long-running queries in Oracle and SQL Server, reducing the execution time to milliseconds and the resource usage up to 10%. He loves learning and connecting new technologies providing expert-level insight as well as being proficient with scripting languages like PowerShell and bash. You can find several Oracle-related tips in his LinkedIn profile.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
