SentryOne provides pre-built alerts to cover a wide variety of performance events; however, there might be scenarios that call for customization of alerts. SentryOne Advisory Conditions were designed with this need in mind.
This blog post will demonstrate how to create a custom Advisory Condition in SentryOne SQL Sentry, add the condition to alerts, and set an automated response and custom email.
Example Scenario
Imagine the back end for your website lives on AWS and is monitored by SQL Sentry. Occasionally, there are issues that cause a flood of query timeouts. Given the volume and scope of these timeouts, it would be easiest to get a single consolidated list of the queries, databases, or timestamps where problems occurred, rather than a series of individual Top SQL: Error alerts. This list makes troubleshooting easier because you can correlate the timeouts in question all at once. You can also ensure the customized alert stands out from the others.
Let’s look at how to build a custom Advisory Condition for this situation. (For more information about Advisory Conditions, please refer to our documentation here.)
Building a Custom Advisory Condition
There are several ways to build a custom condition. In this example scenario, I want to know if any timeouts are present on any of my monitored AWS targets, so I will be using a count. I am also limiting the scope to the previous 5 minutes and evaluating every 5 minutes to make sure new timeouts are detected.
Note that this is a SentryOne database query, meaning I am looking at previously collected data within the SentryOne database and alerting on it. In this example, I am searching the PerformanceAnalysisTraceData table (where Top SQL data is stored) and checking for ObjectNames that include AmazonAWS.
There are many other tables you can build conditions with. For more information about data mining the SentryOne repository, please refer to Jason Hall’s “Mining Performance Data from SQL Sentry” blog series.
Adding a Condition to Alerts
Now that I have built the condition, I need to activate it for alerting. To do so, I will navigate to View > Conditions to open the Conditions pane.
With the Conditions pane open, I will select Advisory Conditions from the drop-down menu and click Add.
Then, I need to find the new condition in the list and select Execute SQL in the Action column. Doing so allows me to provide a SQL script that will be automatically executed on a given target when the specified condition evaluates as true.
Triggering an Automated Response
In this scenario, I will be executing a script against the SentryOne repository server and using sp_sentry_dbmail to send a custom email.
The first part of the SQL script is taking the selected values and inserting them into a table.
I then use the sp_sentry_dbmail stored procedure to send the email to the specified address. This method can be used for any information you would like to send in a table via email. Note that the server I have specified in the Server drop-down menu is hosting the SentryOne database.
The Email Notification
Here is the email message sent when the alert is triggered. It displays the list of processes that timed out in tabular format.
You can, of course, customize what data is displayed in the table. However, this information allows me to quickly see which of my targets had timeouts and provides relevant information about the queries that encountered those errors. I can then open SentryOne, navigate to the appropriate time range, and begin troubleshooting.
Recap
In this blog post, we walked through an example of how to create a custom condition, trigger an action to execute a SQL script, and use sp_sentry_dbmail to send the output of that script via email. I have included the example condition and script below, so feel free to test them out or modify them to your liking. Although this example is related to Top SQL timeouts, my hope is that it gets you thinking about other use cases for sending specific data in your desired format.
