Customizing Your Alert Emails in SentryOne

SentryOne has some of the most robust and dynamic alerting capabilities in our market. Out of the box, we provide a number of options for responding to virtually any type of condition, and these are by no means limited to sending an email:

Still, email is one of the more popular options, and we get the occasional question about how to control the appearance of those emails and the information they contain. In this blog post, I will show you how to customize Advisory Conditions to produce a formatted email response. There are two different levels of complexity here, which require two slightly different solutions. One level of complexity is getting the information you need into the email, which can usually be solved easily; the other is customizing things further, such as a tailored, context-specific subject, body, or from address.

It is fairly straightforward, but I think there could be a number of use cases for this. In this post, I will be creating two different Advisory Conditions to alert on any databases that have gone offline. The conditions will be run against all of our monitored instances, and return a list of databases that are not online, as well as their current state. To start off, in both cases, we’ll open the Advisory Conditions list under All Targets:

The conditions we’re going to create will be querying the sys.databases catalog view. As I said before, there are actually two different conditions I’m going to cover here: One that uses the default e-mail template and provides a list of databases that aren’t online, and one that allows for a subsequent query to pull more information about the databases and produce a more tailored email.

Condition 1 : Using the Default Email Template

Assuming that a plain text email is sufficient, and that all you need is a listing of the databases that are not online, we can just use a Send Email action. However, we’ll have to create the condition in a certain way to achieve that result.

When you create a condition that just returns a true or false result, or a count, that is the single piece of information you get in the email. You can easily augment the query, though, to ensure that more information is included. When you use the Value qualifier for comparison, only the first value is considered, which is why many conditions simply return a single result – either scenario x exists, or it doesn’t, or this is how many things meet scenario y. If you use the Any qualifier, however, you can return multiple results, and these are returned as a key/value pair (Melissa Connors described this in a previous post). All you have to do is make sure that the key column outputs the information you need – since it is a single output column, this just means you need to get a bit creative with string concatenation (the value column is used for comparison).

In this example, we can include the set of databases that are not online, along with their server/instance information and their current state, using the following query:

-- use string concatenation to produce a meaningful key
SELECT [key] = @@SERVERNAME + N'.' + name + N' status: ' + CASE [state]  
          WHEN 1 THEN N'Restoring'  
          WHEN 2 THEN N'Recovering'  
          WHEN 3 THEN N'Recovery Pending'  
          WHEN 4 THEN N'Suspect'
          WHEN 5 THEN N'Emergency'  
          WHEN 6 THEN N'Offline'  
          ELSE N'Copying (applies to Azure SQL Database)' END, 
       [value] = [state]
  FROM sys.databases
  WHERE [state] <> 0;

Select the Create Advisory Condition button and choose SQL Server, since we will be querying the master database on each target. You’ll need to name the condition; I’ve called this one “Databases Offline – Send Email.” You can add a description, so that the purpose of the condition is clear to other users (including future you).

There are a couple of settings that I want to pay attention to in the initial Advisory Condition settings: Trigger Threshold and Maximum Instance Count. We can set Trigger Threshold to 0, because we don’t need a database to be offline for a certain amount of time before alerting on it. Maximum Instance Count refers to the maximum results that could be returned. I would think you should set this equal to the number of databases you’re monitoring, even though it would be horrible if they all went offline simultaneously. If the instance count is over 100, it is possible that you could see some impact during evaluation; so, let’s limit it to 100, and hope that if more than 100 databases go offline simultaneously, something bigger is going on (in which case you should have plenty of other notifications for that).

Next, we can add a single AND criteria to the condition. Use a numeric SQL Server query against master, then add the query above, choose the Any qualifier, pick Does not equal, and enter an Explicit Value of 0. This is what the condition should look like:

The key column includes the textual information we want in the email for each database that is not online, and the value column (state) is what the condition will use to evaluate to true when any rows are returned.

With this condition enabled, SentryOne will be collecting data on the state of the databases you are monitoring. When you save the condition, you will be asked to add actions (you can always do this later, by right-clicking the condition in the list, and choosing Add Actions). In this case, we’ll add a Send Email action:

(Send to Alerting Channels is checked by default. This will cause the alert to show on the dashboard when it is active, but you can uncheck this if you like.)

Next, go to View -> Conditions, highlight the Databases Offline – Send Email condition, and select the user(s) to email when the condition finds a database that is not online. Here you can customize the importance and from address of the email if desired:

On one of the instances I’m actively monitoring, I set two databases to offline, and let the condition evaluate. Here is a portion of the email I received:

It’s not perfect, but I can see the server/instance name, all of the databases that failed the check, and the state of each database.

Condition 2 : Generating Your Own Email

Some customers want a message that’s easier to digest – a customized subject, an HTML table with the results, or the removal of some of the other details (such as the condition statement that led to the alert firing). For this, we can use an Execute SQL action. In order to get there, the initial query in the condition can be much simpler – we will need to trigger a subsequent query if any databases are not online (it could also use an EXISTS check, because at this point, even the number is irrelevant):

SELECT COUNT(database_id) FROM sys.databases WHERE [state] <> 0;

In this case, I’ve named the condition “Databases Offline – Execute SQL” – and I’ve used the same setting as before for Trigger Threshold (0). I did not need to modify the Maximum Instance Count, since I am only comparing one value.

The condition query will look like this – a numeric SQL Server against master, with the above COUNT query, and this will time we can just check if the Value qualifier is greater than an explicit value of zero:

Once you’ve saved that condition, again, a dialog box will pop up asking if you would like to set actions for that condition. Select Yes, and we will continue on to add an Execute SQL action.

For the script, we’ll be using the SentryOne stored procedure sp_sentry_dbmail_20 (so no need to mess with Database Mail). While I’ve used the @body, @body_format, @recipients, and @subject parameters, there are other parameters available.

In order to build HTML content for the email, we can use FOR XML PATH to collapse all of the rows from the query into HTML table rows, with the following query:

DECLARE @tablerows nvarchar(max), @body nvarchar(max);
 
-- this uses XML formatting to collapse
-- the output to a string of <td>s
SELECT @tablerows = CONVERT(nvarchar(max), 
(SELECT td = @@SERVERNAME + N'.' + name, N'', 
    td = CASE state  
         WHEN 1 THEN N'Restoring'  
         WHEN 2 THEN N'Recovering'  
         WHEN 3 THEN N'Recovery Pending'  
         WHEN 4 THEN N'Suspect'
         WHEN 5 THEN N'Emergency'  
         WHEN 6 THEN N'Offline'  
         ELSE N'Copying (applies to Azure SQL Database)'
    END
FROM sys.databases
WHERE [state] <> 0
FOR XML PATH(N'tr'), ELEMENTS));
 
SET @body = N'<html><head>
<style> * { font-family: Segoe UI, calibri } </style>
</head><body>
<H4>The following databases are not online:</H4>
<p><table border="1" cellpadding="10"> 
<tr><th>Instance</th><th>State</th></tr>'
  + @tablerows + N'</table></body></html>';
 
EXEC msdb.dbo.sp_sentry_dbmail_20	
     @body        = @body,
     @body_format = N'HTML',
     @recipients  = N'ledwards@neveropen.co.za',
     @subject     = N'Databases Offline';

Again going to View -> Conditions and highlighting the row with the new condition and the Execute SQL action, you can enter this query into the text box below:

Now, when we evaluate this condition, we have a nicely formatted email with all of your offline databases in an HTML table:

Conclusion

I am sure that you can think of a number of cases where customizing the email would be helpful. Have fun playing with these options!

Lori Edwards

Lori (@loriedwards) has been with SentryOne since 2013, and has recently transitioned from her role as Senior Solutions Engineer to Training Manager. She is responsible for all learning strategy and architecture, including building, executing, measuring, and evaluating training for SentryOne. Lori is also currently serving as a Director at Large for PASS.

Lori shares tips and tidbits for DBAs picked up over years of working as a DBA, as well as stories about her job here. Whether you are a new DBA who wants to learn or someone interested in learning about what it is like to work for SentryOne, be sure to check out Lori’s blog.