SQL Sentry Advisory Conditions

Introduction

Advisory Conditions are a Performance Analysis feature that allows for customized alerting based on performance counter metrics, query results, WMI queries, duration of events, and state changes. Advisory Conditions can be applied globally or at specific targets and instances.

Downloading the Advisory Conditions Pack

When opening the Conditions List at the global level, you'll be prompted to download the latest Advisory Conditions Pack. This pack contains predefined Advisory Conditions that help illustrate the functionality that the Advisory Conditions feature has to offer. If you decide not to download the Advisory Conditions Pack at this point, access it through the Download Latest Advisory Conditions Pack option in the Tools menu. If you choose to disable the automatic checking for Advisory Conditions Packs, checking can be re-enabled in User Preferences in the Updates section.

After downloading the Advisory Conditions Pack, you'll be prompted to apply actions. Selecting Yes opens the Actions Selector to apply actions to your new Advisory Conditions.

Building an Advisory Condition

Create an Advisory Condition by selecting Create Advisory Condition in the Conditions List, or right-click on the All Targets (Global) node, group node, target node, or instance node in the Navigator pane, and then, select Add Advisory Condition. Owner object of the Advisory Condition is dependent on where the Advisory Condition is defined. The following sections explain all the options available when creating an Advisory Condition. 

Defining an Advisory Condition

In the Navigator pane, there are Advisory Condition folders located at various levels. All preconfigured Advisory Conditions are created at the global level. The following chart outlines the various levels and which types of conditions can be configured at those levels.

Location in Hierarchy	Advisory Condition Types Available
Root Global Site Target	Amazon RDS for SQL Server Analysis Services APS Azure SQL Database SQL Sentry (or SentryOne) SQL DW SQL Server Trintri VMware Host Windows
SQL Server Instance	Windows/SQL Server
SSAS Instance	Windows/SSAS

Importing and Exporting Advisory Conditions

You can import and export your any of your saved advisory conditions from the Global or Instance level Conditions List. Open the Navigator Pane, expand the Advisory Conditions node at the Global or Instance level, and then select Conditions List to open the Conditions List for your Advisory Conditions.  

Importing an Advisory Condition

Import a saved Advisory Condition by completing the following steps:

1. Open the Navigator pane and expand the Advisory Conditions node at the Global or desired Instance level. Select Conditions List to open the Conditions List.
   
2. Select File > Import Condition to open the file selector window.
   
3. Navigate to the desired .condition file, and then select Open to import your Advisory Condition. 
4. Select Yes to open the Actions Selector and add Actions to your Advisory Condition or select No if you don't want to add any Actions.
   

Success: You have imported an Advisory Condition to SQL Sentry!

Exporting an Advisory Condition

Export a saved Advisory Condition by completing the following steps:

1. Open the Navigator pane and expand the Advisory Conditions node at the Global or desired Instance level. Select Conditions List to open the Conditions List.
   
2. Select the Advisory Condition you want to export.  Select File > Export Condition to open the Save As window.
   
3. Select Save to export and save your Advisory Condition. 

Success: You have exported an SQL Sentry Advisory Condition!

Using the PowerShell Module to Import and Export Advisory Conditions

You can use the SQL Sentry PowerShell Module to import, export, and remove Advisory Conditions in your SQL Sentry environment. 

Advisory Condition Types

Type is the first option presented when creating an Advisory Condition. The type determines where the condition is evaluated and which metrics are available.

Type	Description
Amazon RDS Instance	Examples:  See the Amazon RDS conditions on GitHub such as Amazon RDS - High CPU 80% or Amazon RDS - % Free Space - Disk.
Analysis Services	This condition is evaluated at the SSAS instance level and has access to Windows and SSAS performance metrics. Examples:  See the SSAS conditions on GitHub such as SSAS Formula Engine Query Pool Job Queuing or SSAS Sustained Cache Evictions.
APS	Examples:  See the DataWarehouse conditions on GitHub such as APS Inaccurate Statistics or APS High Memory Utilization Detected.
Azure SQL Database	This condition is evaluated at the Azure SQL Database level and has access to Azure SQL Database performance metrics. Examples:  See the community-submitted Azure SQL DB conditions on GitHub such as Azure SQL Server Operation Failed or Azure SQL DB High Resource Utilization.
Azure Synapse Analytics	Examples:  See the DataWarehouse conditions on GitHub such as Azure DW High Memory Utilization Detected or Azure DW Distribution Skew Exceeds 10%.
SQL Sentry (or SentryOne)	This condition type is evaluated against the SQL Sentry database and is used to query the SQL Sentry database for information. Examples:  See the 'Incorrect Compatibility Level or Page Verification Check conditions in your installation or the Advisory Conditions Pack article.
SQL Server	This condition is evaluated at the SQL Server instance level and has access to Windows and SQL Server performance metrics. Examples:  See the 'Cost Threshold of Parallelism' Changed or High Avg Wait Time per User Session conditions in your installation or the Advisory Conditions Pack article.
Tintri	Examples:  See the Tintri High VM Throttle or Tintri High Datastore Throttle Latency conditions in your installation or the Advisory Conditions Pack article.
VMware Host	This condition is evaluated at the VMware Host level and has access to vCenter performance metrics associated with a VMware Host.
Windows	This condition is evaluated at the Target level and has access to Windows performance metrics. Examples:  See the High Context Switches or VMware High Ballooning conditions in your installation or the Advisory Conditions Pack article.

Advisory Condition Options

Option	Description
Default Evaluation Frequency	Defines how often the condition is evaluated. The evaluation frequency can be adjusted individually throughout the hierarchy. Note:  Scheduled evaluations won't occur until an action is assigned to the Advisory Condition. Note:  Query data is cached for one minute in the monitoring service before it is written to the SQL Sentry database. If you have a condition that uses T-SQL queries, and you evaluate more often than once per minute (e.g. a DefaultEvaluation Frequency of 30 Seconds), the result data will be retrieved from the cache and may not be current. Wait at least one minute to evaluate (e.g. a Default Evaluation Frequency of every 1 Minute or 5 Minutes) to obtain a new result.
Trigger Threshold	Defines how long the condition must be true before the associated action is taken. Additional Information: See this blog post for detailed instructions on using the trigger threshold.
Severity	Defines the setting of severity that can be used as a sorting parameter in the Events Log. Important:  Advisory Conditions with a severity of High or Critical can also cause the Advisory Conditions folder in the Navigator pane to turn red when true.
Evaluation Timeout	Defines the limit on how long each evaluation can run before a timeout occurs.
Maximum Instance Count	Defines the number of results returned when the Any instance option is used in used in the comparison (instead of Value). Additional Information: See this blog post for detailed instructions on using Maximum Instance Count, Any, and returning a key/value pair in the results to know which instance in the set of results met the defined condition.
Color	Defines the color of the duration line that appears on the Performance Analysis Dashboard.

Highlight on Dashboard Chart

Defines which chart(s) will show the duration line (in the selected color from the options) on the Performance Analysis Dashboard. See the Send to Alerting Channels section of the Actions article for an example of how this appears when a condition evaluates to true.

Supported Versions

When creating an Advisory Condition in the higher levels of the hierarchy, restrict certain conditions to run only on designated versions of Windows and/or SQL Server. For example, if you wanted to restrict the condition to only evaluate on servers with Windows Server 2008 R2 and newer, put 6.1 in the minimum field.

Defining an Advisory Condition

Select one of the following logical operators:

Logical Operator	Description
And	All conditions must be true for an action to occur.
Or	One or more condition(s) must be true for an action to occur.
Not And	One or more condition(s) must be false for an action to occur.
Not Or	All conditions must be false for an action to occur.

Select the plus + icon, and then select a comparison type or an existing Advisory Condition. For more information about logical operators, see the Logical Choice article. 

The following table shows which value retriever types are available for the various comparison types. 

Value Retriever Types	Comparison Types
Performance Counter	Numeric Comparison Expression Left Side Expression Right Side Note:  Performance Counter instances are only available at the appropriate instance context for Additional AC Type Restrictions.
Performance Counter Baseline	Numeric Comparison Expression Left Side Expression Right Side Note:  User-defined baselines must exist on the instance for Additional AC Type Restrictions.
SQL Server Query	Numeric Comparison String Comparison Date/Time Comparison Expression Left Side Expression Right Side Note:  SQL Server only for Additional AC Type Restrictions.
SQL Sentry Database Query (formerly SentryOne Database Query or Repository Query)	Numeric Comparison String Comparison Date/Time Comparison Additional AC Type Restrictions Expression Left Side Expression Right Side
SSAS Query	Numeric Comparison Expression Left Side Expression Right Side
Resource Exhaustion	Windows Advisory Condition Type Date/Time Comparison Expression Left Side Expression Right Side
Azure SQL Database Query	Numeric Comparison String Comparison Date/Time Comparison Expression Left Side Expression Right Side Note:  Azure SQL Database only for Additional AC Type Restrictions.
VMware Host	Note:  VMware Host only.
WMI Query	Numeric Comparison String Comparison Additional AC Type Restrictions Expression Left Side Expression Right Side
Explicit Value	Numeric Comparison String Comparison Date/Time Comparison Additional AC Type Restrictions Expression Left Side Expression Right Side
Last Value	Numeric Comparison String Comparison Date/Time Comparison Additional AC Type Restrictions Expression Right Side
Expression	Numeric Comparison Additional AC Type Restrictions Expression Left Side Expression Right Side
Duration	Numeric Comparison Additional AC Type Restrictions Expression Left Side Expression Right Side
Existing Condition	Additional AC Type Restrictions

Value Retriever Types

Type	Description
Performance Counter	Performance Counters are available based on the type of condition that's being created. Conditions being built at the target and instance level are context aware and have access to specific counter instances. When defined at the target level or lower, use baselines that are associated with Performance Counters. Note:  SQL Sentry provides virtual Performance Counters for use in Advisory Conditions. These virtual counters are gathered using stored procedures and may cause more overhead than counters collected through PerfLib. Virtual Performance Counters are identified by the lack of a colon. For example, SQL Server Data File Size is a virtual counter.
Performance Counter (User)	The Performance Counter (User) option allows for the selection of any performance counter. After selecting this option, select Connect, enter a server name, and then select Connect again. After connecting to the server, select the category field to view a list of all of the Performance Counter categories on that server. After selecting a category, select the counter that's to be evaluated. Note:  Connecting to a server is only necessary to provide a list of Performance Counter categories and counters. During evaluation, the counter is evaluated in the context of each monitored instance.
Performance Counter Baseline	Performance Counter Baselines are available when the Advisory Condition is created at the target or instance level. A user defined baseline must exist at the instance level to be used.
SQL Server Query	Enter the database that the query runs against in the first field. If the condition is being defined at the instance level, there's a button next to the field that provides a list of databases available for that instance. The next field is for the query. The query can be entered manually or can be selected from a list of queries using the Query Editor. If a query returns multiple results, the first result is used for the evaluation.
Azure SQL Database Query	Enter the database that the query runs against in the first field. The next field is for the query. The query can be entered manually or can be selected from a list of queries using the Query Editor. If a query returns multiple results, the first result is used for the evaluation.
SQL Sentry Database Query (formerly SQL Sentry Database Query or Repository Query)	Allows you to perform a query against the SQL Sentry database. See the Availability Replicas Hosted on Same Virtual Host, Availability Replicas With Disks in Same Datastore, and SQL Sentry Monitoring Service Offline conditions as examples. When writing a SQL Sentry Database Query, use the variables @ComputerID and @ConnectionID in your query to dynamically adjust depending on the computer or connection that the Advisory Condition is evaluating against. @ComputerID = the [ID] column in the [dbo].[Device] table @ConnectionID = the [ID] column in the [dbo].[EventSourceConnection] table The error message "The query string must contain a reference to @ComputerID or @ConnectionID" appears if a required reference is missing. SQL Sentry Database Query usage example: SELECT ServiceConnectionStatus FROM dbo.EventSourceConnection WHERE ID = @ConnectionID SELECT ServiceConnectionStatus FROM dbo.EventSourceConnection C INNER JOIN dbo.Device D    ON D.ID = C.DeviceID WHERE D.ID = @ComputerID
Resource Exhaustion	Allows you to perform a check against a Relative Date Value when using the Forecasting feature. For more information on enabling Disk Forecasting, see the Disk Space and Monitoring Service Settings articles.
SSAS Query	Allows you to issue DMV queries in Advisory Conditions. DMV queries offer additional insight into SSAS metadata, and can be executed as MDX or DMX queries. Enter a query string in the provided field, or use the Query Editor to enter the query manually.
WMI Query	Allows you to query WMI using WQL. For more information, see the Querying with WQL article.
Explicit Value	Allows for an explicitly defined value.
Last Value	This is a right side specific option that returns the previous evaluations value of the left side of the argument.
Expression	Using the expression option allows for basic mathematical operation to be applied to other values. Operations include addition, subtraction, multiplication, and division. These mathematical operations can be performed against any of the other numeric value options.
Duration	This option returns the amount of time elapsed to acquire the specified value. Various time units are available, ranging from milliseconds to days. Note:  When using any query retriever types in conjunction with the Any qualifier to compare multiple results, the query is required to return a key/value pair in order to identify which value met the defined condition. This also ensures that conditions using queries on both sides of the condition, or comparing the last value, automatically matches values with the same key.

Query Editor

When using a query retriever type like the SQL Server or SQL Sentry Database query, select the button to the right of the query input box to launch the Query Editor.

The Query Editor is where queries can be edited and saved for future use in Advisory Conditions.

Button	Description
New	Opens the New Query window.
Test	Opens an instance dialog window. Specify the server and database name that the query is to be executed against. The resulting value is displayed in the field to the right of the test button.
Delete	Deletes the selected query.

Conditions List

The Conditions List is accessed from various locations within the hierarchy of the Navigator pane. The number in parentheses beside the Advisory Conditions folder indicates how many Advisory Conditions are defined at that level within the hierarchy. An exception to this is the Advisory Conditions folder located beneath the Contacts node in the Navigator pane. The number here indicates the total number of Advisory Conditions defined across the environment.

Conditions Pane

The Conditions pane provides a list of all available conditions. This pane also displays the owner object, creation date, and last modified date of the condition as well as which user modified it. Disable an Advisory Condition by unchecking the checkbox in the Enabled column. Also add tags to assist with sorting and organizing the conditions.

Select Add Advisory Condition to open Windows, Windows/SQL Server, or Windows/SSAS. Select the condition type to open the Condition Editor. For more information about adding a condition, see the Condition Settings topics. 

Select Edit Advisory Condition to open the Condition Editor for the condition that is currently selected in the Conditions pane.

The Show Events Log button takes you to the Events Log filtered by the Advisory Condition that is selected in the Conditions list. 

The  row allows you to search in each row. 

The following is a list of important columns to remember: 

Column	Description
Type	The target type applicable to the condition.
Object	Actual name of the object where the condition was created (may be Global).
Object Type	Specifies what the object is (e.g. Global, SQL Server, etc.).
Enable	Allows you to enable or disable the Advisory Condition in each row.
Tags	Used to organize the Advisory Conditions by keywords.

Context Menu

Task	Description
Edit	Allows for editing of the selected Advisory Condition.
Show Events Log	Opens the Events Log filtered by the selected Advisory Condition.
Snooze > This Condition/All Objects	Disables actions and alerting on the selected Advisory Condition for the selected period.
Snooze > All Conditions/All Objects	Disables actions and alerting on all Advisory Conditions for the selected period.
Unsnooze > This Condition/All Objects	Re-enables actions and alerting on the Advisory Condition.
Unsnooze > All Conditions/All Objects	Re-enables actions and alerting for all Advisory Conditions.
Clone	Creates a copy of the selected Advisory Condition with a new name with the owner object being in the context of the Conditions List.
Export	Exports the selected Advisory Condition to a .condition file.
Import	Imports an Advisory Condition from a .condition file.
Disable	Disables the selected Advisory Condition.
Delete	Deletes the selected Advisory Condition.

Evaluation Status Pane

The Evaluation Status pane displays the last evaluation for the selected condition. This pane shows the object that was evaluated, the result of the evaluation, the duration and time of the evaluation, and information pertaining to any errors that may have occurred. The bottom of the pane provides a count of the objects that were evaluated. Selecting an evaluation in this pane displays the results of that evaluation in the Evaluation Results pane. 

Evaluate Selected and Evaluate All

The Evaluate Selected button performs an evaluation of the Advisory Condition against the selected object in the Evaluation Status pane. The Evaluate All button performs an evaluation of the Advisory Condition against all legal targets.

With Logging

The Evaluate Selected and Evaluate All options have a drop-down menu which contains a With Logging option. The With Logging option generates an entry in the SQL Sentry Events Log if the condition doesn't have the Log to Alerting Channels action assigned to it.

To view events generated by the With Logging option, select Show Events Log from the Conditions List.

The Events Log displays entries for the conditions that evaluated to true when With Logging was selected for the evaluation.

Context Menu

Task	Description
Snooze This Condition/This Object	Disables actions and alerting for the selected Advisory Condition on the object for the selected amount of time.
Jump To Performance Analysis Dashboard	Jumps to the time frame of the Advisory Condition event.
Show Events Log	Opens the Events Log filtered by the selected Advisory Condition.

Evaluation Results Pane

The Evaluation Results pane, located at the bottom, shows the evaluation step results of an Advisory Condition along with any values retrieved through Performance Counters or queries. Hover over any of the values to open a tooltip with additional information like the duration of retrieving that value, the non-rounded value, error messages, and Performance Counter instance names.

Events Log