Custom Conditions : A Logical Choice - SQL Sentry

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.

    Single Step Conditions

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

    High Active User Sessions Custom Condition

    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 (21) 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.

    A A
    False False
    True True
    Two Step Conditions

    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 (22) 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:

    Large Windows File Cache Custom Condition

    And, Or, Not And, and Not Or Operators

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

    Custom Condition Logic Operators

    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.

    And

    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 SQL Sentry Custom Condition
    False Skipped False SQL Sentry Custom Condition
    True False False SQL Sentry Custom Condition
    True True True SQL Sentry Custom Condition

    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.

    Or

    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 SQL Sentry Custom Condition
    False True True SQL Sentry Custom Condition
    True Skipped True SQL Sentry Custom Condition
    True Skipped True SQL Sentry Custom Condition

    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.

    Not And

    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 SQL Sentry Custom Condition
    False Skipped True SQL Sentry Custom Condition
    True False True SQL Sentry Custom Condition
    True True False SQL Sentry Custom Condition
    Not Or

    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 SQL Sentry Custom Condition
    False True False SQL Sentry Custom Condition
    True Skipped False SQL Sentry Custom Condition
    True Skipped False SQL Sentry Custom Condition
    Multiple Step Conditions and Logic Combinations

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

    SQL Sentry Custom Condition AND Three Steps
    An AND statement with three steps

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

    Multiple Condition Groups
    Existing example of a three-step AND

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

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

    Multiple Condition Groups
    A multi-step condition containing an "AND" and an "OR"

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

    Multiple Condition Groups
    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 SQL Sentry Custom Condition
    False Skipped Skipped False SQL Sentry Custom Condition
    False Skipped Skipped False SQL Sentry Custom Condition
    False Skipped Skipped False SQL Sentry Custom Condition
    True False False False SQL Sentry Custom Condition
    True False True True SQL Sentry Custom Condition
    True True Skipped True SQL Sentry Custom Condition
    True True Skipped True SQL Sentry Custom Condition

    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 (25) 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 SQL Sentry Custom Condition
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    F False SQL Sentry Custom Conditions
    T F F False SQL Sentry Custom Conditions
    T F F False SQL Sentry Custom Conditions
    T F F False SQL Sentry Custom Conditions
    T F F False SQL Sentry Custom Conditions
    T F T F F False SQL Sentry Custom Conditions
    T F T F T True SQL Sentry Custom Conditions
    T F T T True SQL Sentry Custom Conditions
    T F T T True SQL Sentry Custom Conditions
    T T F F False SQL Sentry Custom Conditions
    T T F T True SQL Sentry Custom Conditions
    T T T True SQL Sentry Custom Conditions
    T T T True SQL Sentry Custom Conditions
    T T F F False SQL Sentry Custom Conditions
    T T F T True SQL Sentry Custom Conditions
    T T T True SQL Sentry Custom Conditions
    T T T True SQL Sentry Custom Conditions
    Grouping

    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)))
    Multiple Condition Groups
    Grouping 1

    (A ∧ ((B ∨ C) ∨ (D ∨ E)))
    Multiple Condition Groups
    Grouping 2

    (A ∧ ((B ∨ C ∨ D ∨ E)))
    Multiple Condition Groups
    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.

    Complex Logic

    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:

    Multiple Condition Groups
    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.

    No Value

    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.

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

    Summary

    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.

Comments ( 0 )

    Leave A Comment

    Your email address will not be published.

    This site uses Akismet to reduce spam. Learn how your comment data is processed.