SQL Sentry alerting is extremely powerful but ensuring the alerts you get are always meaningful and actionable requires a deliberate approach. Easy-to-consume alerts are critical for shifting from a reactive posture to a proactive posture, but what about the presentation and formatting of information provided in alerts?
SQL Sentry’s default alert messages provide a lot of information. It is all great information, but there is a lot of it. Over time, I have observed that as more and more people check their email from their phones and loop in other stakeholders, more diverse needs arise surrounding the consumability of alerts. This blog post will give you an example of email alert customization today and looks forward to upcoming enhancements to SQL Sentry alert and automation configuration.
If you haven’t already, check out Lori Edwards’ blog post, “Customizing Your Alert Emails in SentryOne.” She describes the basic setup for what I’m going to cover in this blog post, which is using the Execute SQL action to customize a SQL Sentry alert message.
Changing Up the Display of Information in SQL Sentry Alert Emails
For this example, we will be looking at the % Free Space – Disk condition.
Below is an example of the standard % Free Space – Disk email alert. It shows the query used to pull related data, the evaluation results, and other configuration information, such as at what level the condition is configured at or if a ruleset has been applied. However, if I am sending this type of alert to an infrastructure team, or if I were an MSP that was sending the alert to one of my customers, the person or team receiving the alert might not have experience using SQL Sentry or be able to quickly digest this information. Likely, all they want to see is the evaluation results to determine which drives are getting low on disk space.
Default % Free Disk Space alert email message
Alternatively, we can use the Execute SQL action to take the same results from our default alert message and throw it into an HTML formatted table. In the example below, I also opted to include additional information such as capacity and free space (shown in both percentage and GB).
New % Free Disk Space alert email message
Before getting into setup on the SentryOne side, let’s take a look at the code base and some options you have.
Variables
The default settings are to pull back any drives with 20% or less free space, warning highlighting for drives with 15% or less free space, and critical highlighting for drives with 10% or less free space.
Available parameters
@ResultSetThreshold: This will set the max % free space for drives to make it into the result set. This could be set to 100 to return all drives if you plan to use this for reporting purposes rather than alerting.
@CriticalPercent: This is the % threshold for drive space with a critical amount of free space. This threshold is ultimately used for highlighting purposes.
@WarningPercent: This is the % threshold for drive space with a concerning amount of free space. This threshold is ultimately used for highlighting purposes.
@email_recipients: This is who will receive the email. Note that for multiple recipients, you simply continue to add names separated by commas, within the same set of single quotes.
Core Data Pull
The query shown below is ultimately responsible for the result set returned. It queries the SentryOne database. So, you can modify the query as needed to filter out certain drive letters, servers, etc. If you are using this query for an alert, make sure that filters in the Advisory Condition that is checking for free space match the filter modifications made to this code base (i.e., drive exclusions, server exclusions).
Core data pull query
Legends
If you go back up to the example output provided earlier in the blog post, you will see a legend in the bottom left. If you decide to change the default criticality threshold for highlighting, you will also want to update the legends to match using the code below:
Highlighting legend configuration
Email Procedure
This is the procedure that sends the email. You could use the traditional dbmail, but since this is often disabled for management and security purposes, I opted to use the sp_sentry_dbmail_20 proc. This proc should already be created on the SentryOne database instance. As you can see, you can change the email subject here as well.
Send email procedure
Implementation
Again, Lori’s blog post that I referenced earlier provides the general steps for setting up an Execute SQL action. However, it is important to note that for this implementation, you need to specify the SentryOne database server as the target for query execution, as shown in the screenshot below.
Action Settings configuration for the Execute SQL action
It is also worth mentioning that this same code can be thrown into a job and executed on a desired schedule to be used as more of a report.
Other Considerations
In environments with diverse storage needs, percent-based alerting does not always work well. 10% of 1TB is much different than 10% of 100GB. So, I have also provided a condition and the code base for alerting based on raw space available in GB. All my comments in this blog post still fully apply, just in the context of GB instead of percentage.
Conclusion
I hope you find this example to be helpful for modifying how data is presented in a SentryOne alert, as well as a simple example of custom reporting.
Download the Code Covered in This Blog Post
