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:
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
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):
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.
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.
Now, when we evaluate this condition, we have a nicely formatted email with all of your offline databases in an HTML table:
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!