Custom Conditions : A Logical Choice

If you are not familiar with Custom Conditions in SQL Sentry, you may want to review the following blog posts before proceeding:

• Greg Gonzalez (b|t) : SQL Sentry v8: Intelligent Alerting Redefined

In this post, I’m going to cover how the logic works in Custom Conditions, and how you can use that to reach the outcome you are looking for in each situation. It’s important to understand what the logic looks like in the SQL Sentry application.

You’ve probably seen many one-step binary conditions, like the High Active User Sessions condition below:

In this one-step condition, a count from a SQL Server query is compared to an explicit value of 300. The value of that count is greater than 300 (true) or it isn’t (false). There are two possible scenarios (2¹) allowed in this binary situation. It makes for a rather boring truth table and logic problem; however, that can be a good thing for quickly creating straightforward and simple alerts.

If you want to create Custom Conditions with two or more steps, then things start to get interesting, and you’ll want to ensure that you have the logic set up correctly to achieve the desired end result. If you want to check for two conditions, then you have four possible scenarios (2²) and two possible outcomes. You’ll need to decide when you want to be alerted, for example, only when both of the conditions are true or when even one of the conditions is true.

The Large Windows File Cache Custom Condition is an existing example of a condition that requires both of the steps to be true. You can see that they are joined by an “And” operator in the upper left corner:

There are four operator choices available for selection (“And”, “Or”, “Not And”, and “Not Or”):

Using some logical operations and truth tables, I’m going to walk you through what those look like and how they are configured in SQL Sentry.

Truth Table: And (also depicted as “∧”)

Show truth table

SQL Sentry Example: And

First Condition A = 1?	Second Condition B = 2?	Result	Condition
False	Skipped	False
False	Skipped	False
True	False	False
True	True	True

For the sake of feasibility in illustrating the possible scenarios in SQL Sentry, I’ll use explicit value comparisons. You’ll notice in the SQL Sentry table above that the second condition is skipped when the first one isn’t true. In an “And” situation there is no need to check the next condition if the first one isn’t true because it will not change the final outcome, which will still be false. The order of the steps in SQL Sentry is important and useful. For example, in the Server MAXDOP changed condition it first checks that the server has more than one processor; if it doesn’t have more than one processor, then it doesn’t bother running the SQL Server Query to see if the MAXDOP value changed. If the steps were in the reverse order, then you might execute unnecessary queries against your server.

Truth Table: Or (also depicted as “∨”)

Show truth table

SQL Sentry Example: Or

First Condition A = 1?	Second Condition B = 2?	Result	Condition
False	False	False
False	True	True
True	Skipped	True
True	Skipped	True

Because this is an “Or” condition, when the first step is true, it skips the subsequent steps and goes straight to a true outcome.

You could also have “Not” situations as in “Not A and B” or “Not A or B”. Those don’t seem to occur as often, but I will list them before moving on to combinations of conditions.

Truth Table: Not And (also depicted as “¬” with “∧”)

Show truth table

SQL Sentry Example: Not And

First Condition A = 1?	Second Condition B = 2?	Result	Condition
False	Skipped	True
False	Skipped	True
True	False	True
True	True	False

Truth Table: Not Or (also depicted as “¬” with “∨”)

Show truth table

SQL Sentry Example: Not Or

First Condition A = 1?	Second Condition B = 2?	Result	Condition
False	False	True
False	True	False
True	Skipped	False
True	Skipped	False

If you want to check for three conditions, then you have eight possible scenarios (2³) and two possible outcomes. You could have a simple AND statement with three different conditions:

An AND statement with three steps

An AND with three steps is used in the existing High Avg Wait Time per User Session condition:

Existing example of a three-step AND

Or, you could have something that combines different logic statements, by adding a new condition group:

Click on the Venn diagram symbol to Add a New Condition Group

A multi-step condition containing an “AND” and an “OR”

This same logic is used in the existing High Disk Waits and Latency condition:

Existing example of an AND/OR logic combination

The custom condition above corresponds to the truth table shown below:

Truth Table: And (Or) Combination

Show truth table

SQL Sentry Example: And (Or) Combination

First Condition A = 1?	Second Condition B = 2?	Third Condition C = 3?	Result	Condition
False	Skipped	Skipped	False
False	Skipped	Skipped	False
False	Skipped	Skipped	False
False	Skipped	Skipped	False
True	False	False	False
True	False	True	True
True	True	Skipped	True
True	True	Skipped	True

These tables start to grow very large rather quickly when adding just a couple of more conditions to the check. I’ll max out at five input values for my examples. If there are five conditions, then you have thirty-two (2⁵) possible combinations and two outcomes.

Truth Table: And ((Or) And (Or)) Combination

Show truth table

SQL Sentry Example: And ((Or) And (Or)) Combination

In the first sixteen rows, A is False, and because of the “AND” logic, this means that no matter what the values of B, C, D, and E are, the overall condition result will be False, therefore those condition steps are skipped by SQL Sentry during the evaluation.

A	B	C	D	E	Result	Condition
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
F	–	–	–	–	False
T	F	F	–	–	False
T	F	F	–	–	False
T	F	F	–	–	False
T	F	F	–	–	False
T	F	T	F	F	False
T	F	T	F	T	True
T	F	T	T	–	True
T	F	T	T	–	True
T	T	–	F	F	False
T	T	–	F	T	True
T	T	–	T	–	True
T	T	–	T	–	True
T	T	–	F	F	False
T	T	–	F	T	True
T	T	–	T	–	True
T	T	–	T	–	True

One thing that might cause some confusion when you first get started with Custom Conditions is making sure that your logic is properly configured in SQL Sentry. The “Add a New Condition Group” is like adding a new set of parentheses. You need to ensure that you are on the correct step/level when adding a new group. In the example above we had (A ∧ ((B ∨ C) ∧ (D ∨ E))), but if that second “OR” group had been added as a new group to the “OR” instead of the “AND” level, we would have something very different.

(A ∧ ((B ∨ C) ∧ (D ∨ E)))

Grouping 1

(A ∧ ((B ∨ C) ∨ (D ∨ E)))

Grouping 2

(A ∧ ((B ∨ C ∨ D ∨ E)))

Grouping 3 (Essentially the same outcomes as Grouping 2)

In Grouping 2 and Grouping 3, if A is true, then the overall condition is true as long as just one other condition (B, C, D, or E) is true.

Not all custom conditions will require complex logic to create useful alerts, in fact, many of the custom conditions you’ll see only require one or two steps to build a crucial alert. As shown in the first few truth tables at the beginning of this post, those custom conditions are easy to build and are quickly tested and validated. Sometimes, however, you will want to create custom conditions with more complicated logic. The High Compiles + High CPU condition is an excellent example of what you can accomplish with nested logic in SQL Sentry Custom Conditions:

Existing example of multiple steps & nested logic in Custom Conditions

Also worth noting about the High Compiles + High CPU condition is that it uses other custom conditions as building blocks. You can build a custom condition that is a combination of existing custom conditions. As mentioned earlier, I used the explicit values so I could create examples for all combinations, but it’s worth pointing out that you can have combinations of Performance Counters, SQL Server Queries, Repository Queries, WMI Queries, Expressions, and Duration in there as well. If you aren’t familiar with those options and features, please check out the blog posts I mentioned at the beginning.

If you create a custom condition that results in a “No Value” or error at any step, then that step will be considered false. For example, if the High Mirroring Send or Redo Queue custom condition is evaluated against a SQL Server without Database Mirroring, then the No Value will appear.

You may get a “No Value” message when the condition is not applicable

You have the ability to create intelligent alerts with SQL Sentry Custom Conditions that meet your exact criteria, based on meeting multiple conditions. It’s important to have the logic configured correctly in Custom Conditions to get alerts or other actions only when you want them. As illustrated, you can use explicit values to quickly test and validate the logic in your Custom Conditions. I highly recommend doing this when creating them, especially if you are just getting started and learning how to map the logic to different levels and condition groups. Remember that the order of your steps matters when it comes to efficiency; you can avoid running an unnecessary query by strategically ordering the steps in the condition. You can do many exciting things with Custom Conditions in SQL Sentry, and we’ll soon release a new Custom Conditions Pack with some fantastic new alerts.

Melissa Connors

Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.