Advisory Conditions : Available Actions
SentryOne Advisory Conditions provide a powerful and flexible solution for monitoring environments across a diverse range of situations. This post focuses on the available actions and alerting options for Advisory Conditions. As shown below, the options for available actions are as robust as the conditions themselves.
To illustrate how the actions work, I've created a dummy condition that checks for
1=1. Therefore, it will evaluate to True each time during testing.
Figure 1: Example Advisory Condition
First of all, we need to add an action to a condition. If you right-click on a condition in the Conditions List and select Add Actions from the context menu, or go to View -> Conditions -> Add, the Available Actions dialog shown below appears:
Figure 2: Actions Selector: Advisory Conditions
Second, we need to check an action to add and click OK. Finally, we'll configure it through the Conditions panel. The Conditions panel is also accessible via the Conditions shortcut on the upper right corner of the screen (above the Settings shortcut). Through the Action Settings tab, we'll need to configure the details of the action, such as a contact to alert, or a statement to execute.
First on the list of available actions is Execute Job. Consequently, this requires SQL Server Agent running and the desired job already created on SQL Server to configure this action.
Figure 3: Execute Job Settings
Since this is for a quick example, the "AdvisoryConditionJob" merely performs a select from the SentryOne database. I can look at the Job History on SQL Server to verify its execution:
Due to checking "Write to the Windows Application event log" for "When the job completes" under Job Properties, SQL Server logs the event to Windows as well:
Figure 5: Executed Job Logged to Windows Events
Next is the option to execute a process based on a condition. In this example, I output the results of
netstat -e -s to
Figure 6: Action Settings: Execute Process
When the condition evaluates to True, the process copies network statistics from netstat into the text file:
Figure 7: Process Completed
This example requires enabling xp_cmdshell. If xp_cmdshell is disabled, the monitoring service sends a notice of the failed action:
[Global]: GLOBAL [Message]: Monitoring Service: Action Failed [Action]: Executes a process on a remote SQL Server. [Error]: An error has occurred: Message: SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
Define the SQL statement to execute when a condition transpires in the Action Settings tab, as shown below:
Figure 8: Action Settings: Execute SQL
As documented in Lori's post, you can use more complex SQL statements here to meet your requirements.
Log To Database
Logging Advisory Condition messages to the SentryOne Database is another available action. Conditions logged to the database are viewable through the Actions Log in the SentryOne client. These actions are available directly from the database via ObjectConditionActionHistory and vwObjectConditionActionHistory.
Note that the Actions Log allows you to see many other actions, such as when the condition was disabled/enabled.
Log To Disk
You can also log Advisory Condition Alerts to a file on the machine running the SentryOne Monitoring Service. To use this option you will need to set the file name and location via the Action Settings tab. In this example, I created a SentryOne folder on the C:\ and a new text file named "advisoryconditions.txt".
Figure 10: Log to Disk Settings
When the condition evaluates to True, the related-information for that evaluation is logged to disk:
Figure 11: Logged to Disk
Log to Windows Event Log
Logging to the Windows Event Log will send pertinent data about the condition to the Application Event Log on the machine running the SentryOne Monitoring Service.
Under the Action Settings tab, you may change the default Log Type and Event ID information. The Log Type may be set to "Information" (the default), "Warning", or "Error". The Event ID is "0" by default. You may use an Event ID between "0" and "65535".
Figure 12: Log to Windows Event Log Settings
View logged events under Event Viewer -> Windows Logs -> Application:
Run QuickTrace allows you to kick off a QuickTrace whenever a condition occurs. The Action Settings tab provides options for the server, duration, collection of statement events, and a maximum number of rows to collect. Note that a QuickTrace is not filtered to collect only the intensive events, and you'll want to limit the collection time/rows for performance considerations.
Figure 14: Actions Settings: Run QuickTrace
Details from the QuickTrace appear on the QuickTrace tab in Performance Analysis.
Figure 15: Viewing QuickTrace
This options allows you to send an email (or multiple emails) whenever a condition happens. As mentioned before, Lori walks through an example in her post, "Customizing Your Alert Emails in SentryOne."
Figure 16: Send An Email Settings
In the email settings (above), you also have the option to set the importance of the email, as well as the sender. This can be particularly helpful if you want to set up filters for emails or ensure that important alerts aren't trapped by your current filters.
Figure 17: Email Received
Selecting Send Page from Available Actions sends a page to specified users/groups available in your contacts:
Figure 18: Send Page Settings
The pager address pulls from the contact information. I don't have an actual pager service (or pager, for that matter), so I put the text message email address to my phone into that field as shown below:
Figure 19: Pager Address in Contacts Users/Groups
Finally, I captured a screen of the message on my phone to illustrate what that looks like:
Figure 20: Text Message via Page
Send SNMP Trap
This option allows you to send a Simple Network Management Protocol (SNMP) trap notification. Traps are one of the five main types of SNMP messages. See the SentryOne User Guide for information on SNMP configuration. There are no configuration options on the Action Settings tab when using SNMP traps.
To use SNMP Traps, you must configure the SNMP settings under Global Settings in the SentryOne client:
Figure 21: SentryOne SNMP Global Settings
You'll need a way to view the SNMP traps, as well. ManageEngine provides a Free SNMP MIB Browser.
Sent to Alerting Channels
This is the default action for all Advisory Conditions. It displays the alert on the client in different areas. For example, I set the Evaluates to True condition to Highlight "Windows: Network In" on the Dashboard Chart.
Figure 22: Highlight on Dashboard
The Action Settings tab allows configuration of the alerting channels as well as the option to auto-assign this event:
Figure 23: Action Settings Alerting Channels
As a result of selecting "Windows: Network In" on the condition, that's where it is highlighted on the Performance Advisor Dashboard:
Figure 24: Alert Highlighted on Dashboard
Since I assigned Events Log Entries to myself when configuring the action, that is automatically assigned as shown in the Events log for this condition:
Figure 25: Event Logged and Assigned
There may be times when you don't want a condition to send alerts. For a temporary scenario, you may right-click on a condition in the Conditions list and select Snooze (for this condition or all conditions) to do that for a specified period of time (1 hour, 4 hours, 8 hours, 1 day, 3 days, or 1 week are the choices). This can be undone by right-clicking and selecting Unsnooze.
There may also be conditions that are not applicable to your needs (or perhaps you've copied and modified conditions to more specifically meet your needs). If you do not want a condition to evaluate, right-click and select Disable. Reverse this setting by right-clicking and selecting Enable.
As shown in this post, the automatic alerts and responses (e.g. executing a job, process, or SQL statement) available for Advisory Conditions are vast. While many of these examples have been on the simple side of things, I hope you can imagine ways to leverage these available actions to create more complex and creative solutions for your scenarios as-needed.
Melissa (@MelikaNoKaOi) is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.