As a customer success engineer, I hear directly from customers about what issues they need help solving with SolarWinds SQL Sentry®. This insight first motivated me to create posts for the SQL Sentry Tips & Tricks series, which explains how to get the most out of SQL Sentry.
Now, I’d like to create more tutorials about what I consider to be one of the most powerful features in SQL Sentry: Advisory Conditions. I spend much of my day to day helping customers understand and leverage Advisory Conditions to meet their alerting needs and have found while this feature is heavily documented, there are some key concepts and ‘gotchas’ to be aware of to be able to jump in and start creating your own custom alerts.
Advisory Conditions are, at their core, custom conditions, which can allow you to alert on just about any counter or quarriable data. In my tutorials, I will also share custom Advisory Conditions created by, or for, other SQL Sentry customers—providing examples of how Advisory Conditions can help solve common customer pain points and accommodate a wide range of real-world alerting situations.
Do you have an alerting horror story that continues to haunt you? Or a specific alerting need you can’t quite get right? Leave a comment to let me know what you’re trying to solve for in SQL Sentry, and it just might become the next Advisory Condition how-to post I create.
Key Advisory Condition Concepts
Before jumping right into sharing examples of custom Advisory Conditions, I felt it was important to kick off this blog series by covering some key settings and concepts foundational to creating and leveraging custom Advisory Conditions.
In her post here, Lori Edwards has already written up a great blog covering and describing the core options and settings for Advisory Conditions. I will not be re-covering all of this in detail, so I highly recommend skimming through her blog first.
There are some important concepts I want to mention or reiterate here:
- Supported Column Count: While Advisory Conditions provide the ability to turn most queries into custom alerts, they cannot return more than two columns. Returning a single column is fine if the query used returns a single row, like a SELECT COUNT(*). But if the query returns a result set, you will need two columns. The first column is considered the Identity column, used to uniquely identify the object each row maps to. While the second column is known as the Key column, which is ultimately the data will be evaluated against for triggering the Advisory Condition.
Keep in mind you can combine however many columns from the available columns to ensure you have all the information you need. Take our “% Free Space – Disk” condition, for instance. The first column combines the Host Name and Drive Name, since providing one of these would not be enough information to uniquely identify which drive on which server had met the condition.
- Maximum Instance Count: This setting is the max number of rows returned from a query result to be evaluated in the Advisory Condition. If returning a single row and value, like the SELECT COUNT(*) example, this can stay 1. But if the query used in the Advisory Condition will return multiple rows, then you will want to go ahead and set the Maximum Instance Count to 100, which is the max value for this setting. There is really no benefit to setting this to anything under 100 if a multi-row result set is returned.
Since 100 rows are the max value, you also want to consider the likelihood of the result set returning many more rows. If this is the case, it could be pertinent to filter the core query itself to only return rows you would want to alert on rather than all results where some may meet the threshold and others may not.
Take a simple example like alerting on the status of a database using the logic below which will return all databases and check if the status is not Online. If you have targets with more than 100 databases, you could easily miss databases not online due to not being evaluated:
Instead, as shown below, I would want to include my evaluation logic in the WHERE clause to only return rows to be alerted on, and heavily minimize the potential of missing an alert for a particular database not being online:
- Value vs. Any Qualifier: When returning a result set to be evaluated against the Value or Any option, there is another consideration. If you have the Maximum instance count set to 100 and this qualifier is set to Value, it will only evaluate the first row. Anytime you are returning a result set, you want to also set this to Any, so it will also evaluate against any rows returned. For more information on this, check out Melissa Connors’ blog post here.
- Trigger Threshold and Evaluation Frequency: The default Trigger Threshold in 30 seconds when creating a new Advisory Condition. A mistake I often find people fall into when creating a new Advisory Condition is not updating the Trigger Threshold based on their Evaluation Frequency.
For example, you set up an Advisory Condition to evaluate every 24 hours, but you do not change the default Trigger Threshold of 30 seconds. When doing this, you are defining logic stating the condition must be true for 30 seconds to trigger an action, but you are only evaluating every 24 hours. This means if the initial evaluation was True, the condition will not trigger unless it is true when it checks again 24 hours later. Make sure the Trigger threshold is Zero if you want the condition to trigger as soon as it evaluates to true, or the Trigger threshold is equal to or greater than the Evaluation Frequency and is also some cadence of the Evaluation Frequency.
Example: If you have an Evaluation Frequency of two minutes, the Trigger Threshold (if not zero) has to be at least two minutes or some cadence of two minutes (e.g., two, four, six, eight minutes, etc.)
Additional SQL Sentry Advisory Conditions Tips and Tricks
Now that we covered some key concepts and “gotchas,” here are other little tips and tricks to help you get the most out of the Advisory Conditions you set up.
Advisory Conditions List
You can get access to the library of available Advisory Conditions by opening the Global Conditions List (Navigator Pane > Advisory Conditions > Conditions List)
However, it is important to note although an Advisory Condition is in this list, this does not mean it is actively being evaluated. You will need to add Advisory Conditions to the Conditions Pane with the desired Action for an Advisory Condition to evaluate and trigger the configured Action(s).
In the Conditions List, you can:
- View, create, and edit Advisory Conditions
- Review their descriptions and links to additional information
- Manually evaluate the selected Advisory Condition
It is also important to note you cannot edit default Advisory Conditions. Instead, you will be prompted with the option to make a copy of the selected Advisory Condition when attempting to edit, which will allow you to fully modify the copied version. Keep in mind, though, as this copy is a separate condition, you will likely want to remove the default version from the Conditions Pane and add the newly modified copy to effectively swap them out.
Also, if you manually evaluate an Advisory Condition returning multiple rows evaluated to True, you can view the result set by hovering over the evaluation results:
Event Logs
Under the same Global Advisory Condition node where we found the Conditions List, you will also see the Events Log node. The Events Log is where you can review historical occurrences of triggered Advisory Conditions. Here are a few tips and tricks to get the most out of this view:
- Filtering: Hovering over any given column will populate a funnel icon in the top right of the column; selecting it will provide you with the ability to filter for any of the available values in the column.
- Event Notes: By expanding any occurrence in the Events Log, you can add notes to the event, or view already applied notes. These notes will be visible for the events they are tied to in the Event Calendar, and you can choose whether to include a note in the actual email notification. This can be helpful for things like communicating standard troubleshooting steps or regular culprits to look for.
- Jumping to the Dashboard: As you see events of interest in the Event Logs, you do not need to go through a bunch of navigation overhead to view the Dashboard during the time the event in question took place. You can simply right-click a specific event > Jump to > Dashboard. This will instantly take you to the time range in which the Advisory Condition was active, allowing you to quickly start correlation analysis.
Send to Alerting Channels Action
A unique action for Advisory Conditions is the “Send to Alerting Channels” action. This will populate a visual overlay directly on your Dashboard during the timeframe in which a particular Advisory Condition was True. This is great for providing another layer of data on your Dashboard to provide additional insight and facilitate correlation analysis.
For example, in the screenshot above, I can see a decent spike in CPU. However, I have an Advisory Condition tied to High Context Switching shown as being true during that same spike. While I do not have the Context Switches counter as an available metric on the Dashboard, this allows me to see elevated values in the metric at that time, providing additional insight and context.
Note anytime there is an Advisory Condition on the Dashboard, there will also be a yellow triangle icon in the top left of the chart. Selecting this icon will populate a view of all the overlaid Advisory Conditions in the view and descriptions for those events.
You can choose the color of this overlay, and which chart it is overlaid on in the configuration options for each Advisory Condition.
More Resources on Advisory Conditions
If you found this information informative, make sure to also check out related Advisory Condition posts for more helpful insights. Also, you can leave a comment to let me know what Advisory Conditions issues you are trying to solve with SQL Sentry.
