Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Standardized table based SQL Server monitoring email with PowerShell


By:   |   Last Updated: 2017-07-28   |   Comments (2)   |   Related Tips: More > 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):

Results from Brent Ozar FirstResponderKit sp_BlitzCache

We’re going to enter this query into our PowerShell script and run it:

Powershell interface with the report script

And here is part of the result:

Received email
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.


Last Updated: 2017-07-28


get scripts

next tip button



About the author
MSSQLTips author Pablo Echeverria I've worked for more than 10 years as a software programmer and analyst. In 2016, I switched jobs to a DBA position, where I have implement new processes, created better monitoring tools and grown my data scientist skills.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, August 14, 2018 - 5:32:01 PM - Pablo Echeverria Back To Top

Hi jeff_yao, actually the QueryText column contained the text of a stored procedure, so I had to erase the sensitive parts of it. But there is a newer version of this script, please check it out: https://www.mssqltips.com/sqlservertip/5167/comprehensive-sql-server-monitoring-report-via-email/


Tuesday, August 14, 2018 - 4:52:47 PM - jeff_yao Back To Top

Well, the report needs better formatting for column [QueryText], it is kind of un-readable as it is.


Learn more about SQL Server tools