Documentation forSQL Sentry

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. 

SQL Sentry Conditions pane Global Conditions

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.

Conditions pane Inherited conditions and Explicit conditions

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.  

SQL Sentry Conditions pane Overridden conditions

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.

Conditions pane Explicit conditions

Adding a New Action

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

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.


SQL Sentry Actions Selector

Note:  You may also choose to quickly Disable, Override, or Combine any inherited actions. To do so, select a Condition in the Inherited section of the Conditions pane and choose the desired command (Disable , Override, or Combine).

Inherited conditions toolbar

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.

Note:  Configured actions of the same action type, defined at the same object level, have their behavior types bound together because inherited actions can't simultaneously be in more than one state (Disabled, Combined, and Overridden).
Note:  For any condition you can choose multiple actions.

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. 

Kill Task action

Note:  It's not applicable to the Started condition, or any conditions implying event instance completion.

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.

Log to Windows Event Log action

Settings

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

Log to Windows Event Log Actions Settings

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.

SQL Sentry Windows Event Viewer

Note:  To find the logged Advisory Condition event, use the Windows Event Viewer. The logged events are under Windows Logs and are of Application type. The Source is SQL Sentry.
Additional Information: SQL Sentry is capable of interfacing with SCOM for purposes of alerting through SNMP traps or by passing alerts through the Windows Event Log. See the SQL Sentry and System Center Operations Manager blog for more details.

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.

Log To Disk action

Settings

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

Log to Disk Action Settings

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.

Send Email action

Note:  Stop receiving the Outlook Security Notice when you select an alert email link by enabling trust for the SQL Sentry protocol. Enable the SQL Sentry protocol by entering the appropriate key in the Windows Registry Editor: 

For Outlook 2010

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\14.0\Common\Security\Trusted Protocols\All Applications\sqlsentry:]

For Outlook 2013

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\15.0\Common\Security\Trusted Protocols\All Applications\sqlsentry:]

For Outlook 2015 or higher

[HKEY_CURRENT_USER\Software\Policies\Microsoft\Office\16.0\Common\Security\Trusted Protocols\All Applications\sqlsentry:]
Additional Information: For more information about enabling trust to a specified protocol, see this MSDN article.

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.

SQL Sentry Conditions pane Action Settings Select Targets

Execute SQL

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

SQL Sentry Execute SQL action

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.

Conditions pane Action Settings Execute SQL

Note: In an environment with multiple monitoring services, it's important to note that the service that detects the condition is the service that fires the action. If that service doesn't have connectivity to the targeted server, the action fails.

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.

SQL Sentry Send SNMP Trap action

Note: Locate the files for configuring SNMP MIBs in the SentryOne application folder C:\Program Files\SentryOne\2021.0\SNMP MIBs.

SentryOne SNMP MIBs
Additional Information: SQL Sentry is capable of interfacing with SCOM for purposes of alerting through SNMP traps. See the SQL Sentry and System Center Operations Manager blog for more details.

Log To Database

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

Log To Database action

Note:Any of the other actions also write to the database, so if you’ve chosen another action this may be redundant.

Execute Process

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

Execute Process action

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.

Conditions pane Action Settings Execute Process

Note:You can pass certain system parameters, as well as user defined parameters through output text.

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.

Send Page action

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.

Conditions pane Actions Settings Select Targets

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.

Run QuickTrace™ Action

Note:The QuickTrace™ action is now available for a wide range of conditions.

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).Select Site and Open Conditions pane
  2. Select the desired Condition type from the drop-down list, and then select Add to open the Action Selector window.
    Conditions pane Add Condition and Action
  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.Action Selector Run QuickTrace™ action

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.

Note:  To avoid impacting server performance on very busy systems, there are certain cases where SQL Sentry further restricts QuickTrace™ functionality. If one of these cases is detected the QuickTrace™ action doesn't fire. For more information, see Quick Trace restrictions.

Settings

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


 Conditions Pane Action Settings tab

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.

Execute Job action

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.

Conditions pane Actions Settings Job to Execute

Execute PowerShell

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

Execute PowerShell action

Note:You may also use system parameters in the PowerShell script, with a format of <%ParameterName%> (e.g, $ServerName = '<%ServerName%>').

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. 

Note:  A valid user name and password must be provided in the Action Settings if the domain account or local account options are selected.

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.

Conditions pane Action Settings Execute PowerShell

Additional Information: See the Using the SQL Sentry PowerShell Action blog post for in-depth coverage of the Execute PowerShell action, including how to send notifications to Slack and Microsoft Teams.

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.

Send To Alerting Channels Highlight on Performance Analysis Dashboard

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.

Send To Alerting Channels Action Settings

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

Send To Alerting Channels Events Log

Note:  Response Ruleset options are not available for this action.

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.

Edit Advisory Condition Highlight on Dashboard Settings

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

Additional Information: You can integrate with IFTTT (If This Then That) to received notifications. See the Get SQL Sentry Alerts on Slack blog post for an example. See the SQL Sentry Integrations article for more ideas.

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.