Creating Advisory Conditions in SQL Sentry
I was looking through SQL Sentry blog posts and realized that, while we have several posts around creating Advisory Conditions, I couldn't find one that covers most everything. My plan for this post is to cover the whole kit and kaboodle, but I'll be referring to the existing blogs so that this does not become War and Peace.
For those of you using any SQL Sentry product, you should be familiar with Advisory Conditions. When you download our toolset, it comes with a group of 75 pre-built Advisory Conditions. Here is a partial list:
In a nutshell, Advisory Conditions allow you to collect and compare the results of a T-SQL query, WMI query, Performance Counter value, or combination of values and queries. This allows you to automate responses on items specific to your environment and/or the applications that you support. Responses include obvious things like notifications, but can also be used for logging, executing commands, starting jobs, and a host of other actions.
So, let's walk through how to create an Advisory Condition.
When you click on Create Advisory Condition, you'll be presented with the different types of conditions that can be created (see Figure 1). These options determine the metrics that are available. For any of these options, Repository Queries are available, but the rest are as follows:
- Windows: Windows Performance Counters, Windows Performance Counters(User), WMI queries
- SQL Server: Windows Performance Counters, Windows Performance Counters(User), SQL Server Performance Counters, SQL Server queries, WMI queries
- Analysis Services: Windows Performance Counters, Windows Performance Counters(User), SQL Server Analysis Services Performance Counters, WMI queries
- APS: APS/SQL DW Peformance Counters, APS queries
- SQL DW: APS/SQL DW Performance Counters, SQL DW queries
- Tintri: Tintri Performance Counters
- SQL Sentry: Repository queries
As a note, Performance Counter (User) allows you to access any performance counters that exist on a particular server. If you choose Performance Counter(User), when you get to creating the condition, you'll need to indicate the server that you'll be gathering the performance counters from, so that a list can be generated.
After choosing what you would to create your condition on the Advisory Condition, you'll be presented with the Advisory Condition framework. The title area is pretty self-explanatory. The description is what you'll see when the condition has been raised. Additionally, if you decide to have your conditions displayed on the Performance Analysis dashboard, this is the description that will appear. It's a rich text editor, so you can include hyperlinks, instructions, and whatever other information would be pertinent.
Below the description are the different options that can be set for individual conditions (see Figure 2).
Figure 2: Options
Most of these are self-explanatory, but I'll go through each one:
- Default Evaluation Frequency: How often SQL Sentry checks that condition. This defaults to every 30 seconds, but you may choose to change that. For example you might check CPU utilization more frequently than disk free space.
- Trigger Threshold: How long does the condition need to remain true to raise an alert. For conditions that are Boolean, no Trigger Threshold needs to be set. Melissa Connors goes into more detail in Custom Conditions: Trigger Threshold .
- Severity: Again self-explanatory, but important to set appropriately. The severity level affects how heavily conditions are weighted for the environment health score.
- Evaluation Timeout: This is optional. This is how long SQL Sentry will continue to try and evaluate a condition before timing out.
- Maximum Instance Count: If you have a condition that could have more than one result, the count would be set to that value. For example, the number of database files that have less than 10% free space left. This needs to be used in conjunction with the keyword Any rather than Value in the condition. Melissa Connors goes into more detail in Custom Conditions: Maximum Instance Count .
- Color: This indicates the line color on the Performance Analysis Dashboard and Event Calendar
- Highlight on Dashboard Chart: If you choose the Action Send to Alerting Channels, this will define where the alert will display on the dashboard. You can choose as many as are applicable.
- Supported versions: If your condition contains metrics that are unavailable in builds of Windows or SQL Server, this can be used to define which versions should be evaluated. This will be a build number.
Figure 3: Comparison Type
Figure 4: Expression
Figure 5: Expression
Now we're ready to create the condition. The conditions are really just logical expressions - comparing the values on the right and left sides of the equation. Since often there is more than one equation, it would be beneficial to review Custom Conditions: Trigger Threshold .regarding logical operators in multi-step conditions.
For each side of the condition, you'll define a type (see Figure 3). The types available will be dependent on the type of condition you are creating. In additon to the queries and performance counters that you can use, there are some additional options. Explicit value is when you will be using a hard value like 5 or 'Failed'. Either side of the equation can also be an expression - such as transactions per second are greater than the average transactions per second multiplied by 2. Duration returns the amount of time that it took to obtain the value.
The types available on the right side of the condition can include some additional types. Last value will look at the value returned during the prior evaluation. This is helpful in determining change over time. Additionally, if you are creating a condition that looks at a metric contained in a baseline, you have the option of comparing it to available baselines. The condition will need to be scoped to the server or instance where the baseline exists or it can be used globally, if you have global baselines.
So, this is the soup to nuts post on creating Advisory Conditions. In my next post, I'll create some example conditions for you to work with.