Creating Advisory Conditions in SentryOne - Part 2 - The Examples

In my previous blog post, I went through the Advisory Condition framework to show the different features used to create Advisory Conditions. In this post, we'll create some specific conditions highlighting functionality that might not be readily apparent.

Creating a Condition That Returns Multiple Values

For most of the conditions we have, they check for a condition and return a single result. In many cases, though, there is the possibility of multiple results. If I want to get alerted when a data or log file has less than 10% free space left, it could be that more than one file would fit that condition. To handle this scenario, just prior to the comparison operator, we're going to choose the option 'Any' rather than 'Value':

By doing this, when the condition fires, you can see all of the matching results by hovering over the word Any.

Here is the T-SQL being executed against the SentryOne repository for that condition:

SELECT ESC.ServerName + ': (' + PASF.Name + ')',
       100 - (CONVERT (DECIMAL (12, 2), ((CAST (FileUsedSize AS FLOAT) 
	       / (CAST (FileTotalSize AS FLOAT))))) * 100)
FROM   PerformanceAnalysisSqlFile AS PASF
       INNER JOIN
       EventSourceConnection AS ESC
       ON ESC.ID = PASF.EventSourceConnectionID
WHERE  DatabaseID <> 2 AND ESC.IsPerformanceAnalysisEnabled = 1

When sending emails alerts with multiple results, there are a few ways to modify the message being sent - I've covered those methods in an earlier blog post.

Creating a Condition from a Baseline Value

Creating advisory conditons on baseline values is a great way to create alerts based on the expected behavior of a specific server/instance. Because the baseline is scoped to that particular server, the condition has to be built there as well.

Additionally, the condition needs to reference a metric that appears on the Performance Analysis Dashboard. If it is not a metric contained in the Dashboard, the option to compare to a Baseline will not appear. There are four types of values that you can use for your baseline metrics - Average, Minimum, Maximum, and Standard Deviation. In addition to being able to compare against distinct values, you can also use those values as part of an expression. For example, you might want to be alerted if the Compiles/sec are 10% higher than the maximum for a specific baseline.

Note: The metric specified on the left side of the condition will be the one pulled from the baseline metrics on the right side.

Last Value

This is one of my favorites - as we're evaluating conditions, we keep those values in our Events Log. Because of that, we can create conditions that look at the value that existed during the last evaluation. This can be used to check for a configuration change - for example comparing the MAXDOP setting to the last value set. By comparing the current value to the previous value, you can check for unusual trends. Since I apparently love 10%, we could create a condition to check whether TEMPDB is 10% larger than the last time that we checked. If we're checking every minute, that might be an unusual growth trend. Here's what that condition would look like:

Going Forward

Advisory Conditions provide a incredibly flexible framework for creating customized notifications. If you are a SentryOne customer, I hope that you're making use of them in your environment. If you're not a customer yet, go download a free, 15-day evaluation and see what we can do for you.