SQL Sentry Actions

Introduction

The alerting and response system in SQL Sentry uses the concept of Conditionsand Actions. Actions can be defined in response to certain Conditions being met within your environment. You can choose from a variety of Actions, depending on which Condition is being responded to.

All Actions work on the principle of inheritance, meaning that if you configure an Action in response to a Condition being met at the Global level (All Targets), it's automatically passed down to all applicable objects below it. This allows you to define global Actions for the most common issues across your environment once, and have those passed down to every monitored server automatically. You can further refine Actions at each level as needed. For a visual representation of how inheritance works within SQL Sentry, see the Alerting and Response System Hierarchy diagram.

Each Action that's configured in your environment has an associated behavior. The behavior controls how the Action is carried out relative to any inherited Actions. There are three action behaviors available: 

• Override Inherited Actions 
• Combine with Inherited Actions  
• Disabled 

For a complete explanation and example usage scenario for each behavior, see Action Behaviors below.

Configure Actions for a specific group of objects or an individual object to give you control over how SQL Sentry works throughout your environment. SQL Sentry comes with a number of global Actions predefined to get you up and running quickly. These Actions can be changed, as needed to fit the specific needs of your environment.

Response Rulesets control how often Actions are taken in response to Conditions being met. They do this by assigning additional criteria on top of the Condition itself, that must be met before an Action takes place. To control the time frame of when an Action is processed apply a Window directly to any configured Action.

Configuring Actions

If you're upgrading from a previous version of SQL Sentry, you'll notice that the Conditions and Settings pane has been updated.

The Conditions displayed in the Conditions pane change depending on which node or object you've selected in the Navigator pane. If you don't see the Conditions pane once you've selected a node in the Navigator pane, select View > Conditions.

Conditions pane layout as noted in the image above from top to bottom:

• The header reflects the currently selected object in the navigator.
• The inherited section shows conditions and actions that are being passed down to the current level.
• An inherited condition is grayed out in the inherited section when it's overridden or disabled.
• The explicit section shows conditions and actions that have been set at the current level.
• Disabled conditions display with red text in the explicit section.
• Select any condition in the inherited section to make the disable, override, and combine buttons visible.

Select the All Targets node to see globally applied Conditions in the Conditions pane. 

When you select any applicable object level below the All Targets node, you'll see two specific sets of applied Conditions in the Conditions pane.

The top section of the Conditions pane contains the Inherited section, which shows any applied Conditions that are being passed down to the current object level. The Inherited section also contains an Object column, which shows the object level the Condition is being inherited from. When an Inherited condition is overridden or disabled, it still shows up in the Inherited section, but its text is grayed-out, and its status says Overridden.  

Directly beneath the Inherited section, is the Explicit section. The Explicit section contains applied Conditions that have been set at the current object level. The Explicit section also displays a Behavior column. Each Action that's set up in your environment has an associated behavior. This behavior controls how the Action is carried out relative to any inherited actions.

Adding a New Action

1. To add a new Action, select the desired node in the Navigator pane.

2. Select Add in the Conditions pane (View > Conditions) . This opens the Action Selector window.

3. Expand the applicable object and Condition. Use the check box(es) to select which Actions should be taken in response to this Condition being met. Select OK to save your changes.

Action Behaviors

Each action that's setup in your environment has an associated behavior. This behavior controls how the action is carried out relative to any inherited actions.    

The user can change the Behavior of any Action to one of the following:

Behavior	Description
Override Inherited Actions	Actions defined with this behavior override all actions that are being inherited from a higher level. This Behavior acts as a special set of instructions that are followed instead of the passed down (inherited) instructions. When a Condition occurs that triggers an action defined with the Override Inherited Actions behavior, inherited actions aren't executed, but the action that's defined at that specific level is executed. The Override Inherited Actions behavior is the default behavior assigned for any newly defined action. For an example scenario, see the Override Inherited Actions Example.
Combine with Inherited Actions	Actions defined with this behavior combine with all inherited actions. This behavior acts as a set of instructions that are followed in addition to the passed down (inherited) instructions. When a Condition occurs that triggers an Action defined with the Combine with Inherited Actions behavior, all inherited actions execute, and all actions that are defined at that specific level also execute. For an example scenario, see the Combine with Inherited Actions Example.
Disabled	When an action is defined with the Disabled behavior it disables any action of the same action type that's being inherited for a certain Condition. This behavior acts as a special set of instructions that disallow the passed down (inherited) set of instructions. When a Condition occurs that triggers an Action defined with the Disabled behavior, inherited actions aren't executed. For an example scenario, see the Disabled behavior example in this article.

Multiple Actions per Condition

One of the new improvements made to the alerting and response system is the ability to add multiple versions of the same action type per condition. Use this ability, in combination with Response Rulesets to implement escalation procedures when certain Conditions take place.

Examples

Override Inherited Actions Example

Actions defined with the Override Inherited Actions behavior override all actions that are being inherited from a higher level. This behavior acts as a special set of instructions that are followed instead of the passed down(inherited) instructions.

For this example assume the following:

There's a Send Email action configured at the Global level that has a selected target of DBA1 for the SQL Server Agent Job: Failure condition.

With this Action configured as above, anytime a SQL Sever Agent Job fails across the entire monitored enterprise the following will occur:

1. The SQL Server Agent Job: Failure condition is met.
2. In response to the Condition being met, the Send Email action executes, sending a notification email to DBA1.

Now suppose there's a development server (DEVServer) in the environment, that's not managed by DBA1. For this server, when an Agent Job fails, you'd like notification emails sent to the developer or user DEV1 instead of the user DBA1 being alerted.

To accomplish this, add a Send Email action with a selected target of DEV1, for the SQL Server Agent Job: Failure condition at the instance level with a behavior of Override Inherited Actions.

With this Action configured as above, anytime a SQL Sever Agent Job fails on the development server the following occurs:

1. The SQL Server Agent Job: Failure condition is met.
2. In response to the condition being met, the Send Email action executes, sending a notification email to DEV1.

Only the development server is impacted by this change. The rest of the monitored enterprise is still subject to the globally configured Send Email action that has a selected target of DBA1.

Combine with Inherited Actions Example

Actions defined with the Combine with Inherited Actions behavior are combined with all inherited actions. This behavior acts as a set of instructions that are followed in addition to the passed down(inherited) instructions.

For this example assume the following:

There's a Send Email action configured at the global level that has a selected target of DBA1 for the SQL Server: Offline condition.

With this Action configured as above, anytime a SQL Sever is detected to be offline across the entire monitored enterprise, the following occurs:

1. The SQL Server: Offline condition is met.
2. In response to the Condition being met, the Send Email action executes, sending a notification email to DBA1.

Now suppose there's a SQL Server (ASPServer) in the environment that has an internet facing workload. When the SQL Server: Offline condition is met you'd still like to notify DBA1, but you'd also like to notify the web developer, WEBDEV1.

To accomplish this, add a Send Email action, with a selected target of WEBDEV1 for the SQL Server: Offline condition at the instance level with a behavior of Combine with Inherited Actions.

With this Action configured as above, anytime that the ASPServer is detected to be offline, the following occurs:

1. The SQL Server: Offline condition is met.
2. In response to the condition being met, the Send Email action executes, sending a notification email to DBA1 and WEBDEV1.

Only the ASPServer is impacted by this change. The rest of the monitored enterprise is still be subject to the globally configured Send Email action for the SQL Server: Offline condition, that has a selected target of DBA1.

Disabled Behavior Example

When an Action is configured for a Condition with the Disabled action behavior, it disables any action of the same action type that's being inherited. This behavior acts as a special set of instructions that simply disallow the passed down(inherited) set of instructions.

For this example assume the following:

On TestServer1 there's a Send Email action configured that has a selected target of DBA1 for the SQL Server Agent Job: Runtime Threshold Max condition. This action is configured at the instance level.

With this Action configured as above, anytime any SQL Agent Job meets the Runtime Threshold Max condition on TestServer1 the following occurs:

1. The SQL Server Agent Job: Runtime Threshold Max condition is met.
2. In response to the Condition being met, the Send Email action executes, sending a notification email to DBA1.

Now suppose there's a job, LongRunningJob, that's known to be long running on TestServer1. DBA1 doesn't need to be alerted about it and would like to disable runtime notifications about this job.

To accomplish this, add a Send Email action for the SQL Server Agent Job: Runtime Threshold Max condition at the object level(the LongRunningJob) with a behavior of Disabled.

With this Action configured as above when the LongRunningJob runs long, the following occurs:

1. The SQL Server Agent Job: Runtime Threshold Max condition is met.
2. In response to the Condition being met, the inherited Send Email action isn't executed.

Only the individual SQL Agent Job LongRunningJob is impacted by this change. The rest of the SQL Agent Jobs on TestServer1 are still subject to the instance level Send Email action configured for the SQL Server Agent Job: Runtime Threshold Max condition.

Available Actions

Kill Task

The Kill Task action kills, or cancels the current job. This action is typically associated with Runtime Maximum, Performance Threshold Maximum, Block, or Conflict conditions, to prevent resource contention situations. 

Log to Windows Event Log

The Log to Windows Event Log action sends data relevant to the condition to the application Event Log on the SQL Sentry monitoring service computer.

Settings

Within the Actions Settings tab, select the Log type (Information, Warning, or Error) and then assign an Event ID.

In this example, there is an Log Type of Information and an Event ID of 9990. When the associated Advisory Condition evaluates to true, the event is logged to the Windows Event Log on the computer running the SQL Sentry monitoring service.

Log to Disk

The Log to Disk action sends data relevant to the condition to the specified text file on the SQL Sentry monitoring service computer's file system.

Settings

Within the Actions Settings tab, enter the Log file where you want to store the condition data.

Send Email

The Send Email action sends an email alert to the Users or Groups specified in the Action Settings. For more information about Users and Groups, see the Contact Management topic. Certain alert emails in version 7.0 now include a View in SQL Sentry client section. You'll notice that the Event View section contains a hyperlink that begins with the SQL Sentry (or SentryOne) URL protocol. Selecting this link opens the Event View in the SQL Sentry client for the specific condition that triggered the alert.

Settings

The Actions Settings tab allows the following configurations:

Option	Description
Select Targets	Specifies which Users or Groups receive the email alert.
Importance	Specifies the importance level of the email alert.
From Address	Specifies the from email address of the alert. To use the default email address leave this blank. For more information, see the SQL Sentry Monitoring Service Settings topic.

Execute SQL

The Execute SQL action executes the T-SQL statement(s) on the specified SQL Server.

Settings

Use the server list to select a server, and enter the desired T-SQL in the command text box. Selecting the Target option executes the T-SQL statement against the server that triggered the condition.

Send SNMP Trap

The Send SNMP Trap action sends a Simple Network Management Protocol (SNMP) trap notification. For more information about configuring SNMP, see the SQL Sentry Monitoring Service Settings topic.

Log To Database

The Log To Database action logs data relevant to the condition to the Actions log in the SQL Sentry database.

Execute Process

The Execute Process action executes the defined command text on the specified SQL Server.

Settings

Within the Execute Process Action Settings tab you'll see the Execute Process section. Use the server list to select a server. Selecting the Target option from the server list executes the process against the server that triggered the condition. Use the Command Text box to enter the desired literal command.

Send Page

The Send Page action sends an alert to the pager address for the specified Users or Groups. For more information about Users and Groups, see the Contacts Management topic.

Settings

The Actions Settings tab for the Send Page action allows the following configurations:

Option	Description
Select Targets	Specifies which Users or Groups receive the pager alert.
Importance	Specifies the importance level of the pager alert.
From Address	Specifies the From address of the alert. To use the default email address leave this blank. For more information see the SMTP configuration section of the SQL Sentry Monitoring Service Settings topic.

Run QuickTrace™

The Run QuickTrace™ action executes a QuickTrace™ against a specified target server. A QuickTrace™ is a comprehensive snapshot of activity created by combining process-level data and trace events collected during a brief sample period. A QuickTrace™ isn't filtered, so it collects all events on the specified SQL Server.

Configuring the Run QuickTrace™ Action for a Condition

Enable the Run QuickTrace™ action to take place when a specified condition is met by completing the following steps:

1. Select the desired node in the Navigator pane, and then open the Conditions pane (View > Conditions).
2. Select the desired Condition type from the drop-down list, and then select Add to open the Action Selector window.
   
3. Select the desired Condition parameters on the left, and then select the Run QuickTrace™ action on the right. Select Ok to save your Action for the Condition.

To avoid impacting the performance of the target SQL Server, QuickTrace™ is time and size limited. Only one simultaneous QuickTrace™ can be run against any target server. QuickTraces are throttled to control the time between successive runs. The current allowed frequency is one minute, meaning that once a QuickTrace™ is completed, another QuickTrace™ can't begin on the same specified server until one minute has elapsed.

Settings

Configure settings for the Run QuickTrace™ action using the Actions Settings tab on the Conditions pane.

 

The Actions Settings tab for the Quick Trace™ action allows the following configurations:

Option	Description
Run QuickTrace Against	Specifies the Target server where the QuickTrace™ runs.
Run QuickTrace for	Specifies the length of time that the QuickTrace™ runs against the target server.
Collect Statement Events	Specifies whether to collect Statement Level Events. Enabling the Collect Statement Events option dramatically increases the amount trace data collected.
Limit Trace Data to	Specifies the maximum number of rows that the QuickTrace™ collects.

Execute Job

The Execute Job action executes a SQL agent job on the current server, or any other watched server in your enterprise. This action enables you to create one-to-one job chains by associating it with Completed, Success or Failure conditions. For more advanced chaining features, see Event Chains.

Settings

Within the Action Settings tab you'll see the Jobs To Execute section. Select a server from the server list. Specify the SQL agent job to run by using the job list. Once a target job has been specified for this action, it shows up in the Event Calendar view with a grid pattern background as an indication of its conditional execution.

Execute PowerShell

The Execute PowerShellAction executes the entered PowerShell script on the monitoring computer, the target computer, or any valid Windows target.

Settings

Within the Action Settings tab you'll see the Server section. Select the server where the script will be executed from the server list (on the monitoring server, on the target server, or on a specific watched server).Select whether the defined PowerShell Execution Account, a domain account, or a local account runs the script from the account list. 

Enter or load (selecting  Load from File beneath the text area) PowerShell script text into the PowerShell Script Text section. Select Test PoSh Script to test the entered script on a specified target.

Send To Alerting Channels

This is the default action for all Advisory Conditions. The Send to Alerting Channels action allows open Advisory Condition events to display in various places throughout the client. You can use the Send to Alerting Channels action to highlight applicable charts on the alerting channel of the Performance Analysis Dashboard. In addition to the highlight, a glyph also appears in the top left corner of the chart and provides additional information pertaining to the Advisory Condition event. You can also use the Send to Alerting Channels action to send a JSON post request to your configured webhook endpoints.

Performance Advisor Dashboard Example

In the image below, the High Disk Waits and Latency condition has been sent to the DISK IO chart. There is a caution glyph on the chart as well as a highlighted area for when the condition was true.

This action must be configured in both the condition Action Settings and the condition definition. 

Within the Action Settings tab, selecting the Performance Analysis Dashboard option shows the open Advisory Condition events on the Performance Analysis Dashboard provided that the Advisory Condition involves a performance counter that's associated with one of the charts on the Dashboard. 

If desired, select a user to be automatically assigned to an Advisory Condition event from the Automatically Assign Event Log Entries To drop-down list.

With the above settings, the user Melissa Connors is automatically assigned to these occurrences in the Events Log:

To complete the configuration, within the condition definition (Edit Advisory Condition), select the appropriate option(s) from the Highlight on Dashboard Chart list. In the Color drop-down, select the desired color to use when highlighting the Dashboard for this condition.

In this example, a strong orange color is selected to highlight the Disk and SQL Server Waits charts on the Performance Analysis Dashboard.

Webhook Example

For any Condition with a configured Send to Alerting Channels action, you can select to send a POST JSON request to your desired endpoint. For more information about SQL Sentry Monitoring Service webhook settings, see SQL Sentry Monitoring Service Settings.

In this example, the Audit: Settings Changed condition has the Send to Alerting Channels action configured. Within the Action Settings tab, (if a webhook has been configured), selecting the webhook will send a JSON POST request to the configured endpoint once the Audit: Settings Changed condition has been met.