One of the most fundamental and useful features of SQL Sentry is the alerting system: configuring conditions to send you emails when any sort of event happens in your environment. You can turn some of these off if you don't need to know about them, but what if you want to get alerted for a particular condition only sometimes? I see a lot of questions come in to our support team about how to filter unwanted email alerts, so I thought I'd share how to accomplish this.
Let's take the "SQL Server: Top SQL: Duration Threshold Max" condition as an example. If any query runs longer than the maximum allowed threshold, you'll get alerted. What if you have some queries that you expect to run longer than your threshold? You still want to be alerted on other queries, so you can't just disable the alert altogether. Maybe you even have a specific query that you want to be alerted on if it runs using a certain login or against a certain database, but otherwise you do not want an email. This is just one example of the level of customization that you have available.
In the Navigator pane, select the Shared Groups (Global) node in order to set the scope to the Global level. Now, in the main menu bar of the client, select View > Conditions to open the Conditions pane. Selecting the global node in this case means that any changes we make to an action will affect your entire environment. If you only want to configure these filters for a subset of your servers, you can divide them into Sites or Groups and then just select one of these instead of the global node.
Select the configured Condition/Action pair that you want to edit. In this case, we will be using the SQL Server: Top SQL: Runtime Threshold Max condition. After selecting it, move down to the Condition Settings tab near the bottom of the Conditions pane. Click ‘New’ to create a new condition filter. The action you have assigned to this condition will only fire if the rules you create here are evaluated as 'True.' Click the + button next to ‘And’ to add in a new line. You will see something like this:
There are 3 different segments of this line that you can change. The first, in square brackets and colored blue, is some sort of feature or data from the event. The options available here will change according to the context, so if you are working with a condition where [Application Name] wouldn’t make sense, such as “Event Chain: Runtime Threshold Max,” you won’t see it. You’ll get a new set of options here.
The second segment is the green-colored text. This describes the logic your filter will use. In our case, we will be selecting “Does not contain” so that we can enter the name of the application which we do not want to be alerted on. You can also select "Contains" so that you only get alerted for a specific application. Remember that you can keep adding logic to this filter so you could potentially filter out a couple applications at a time. You can be as specific or as general as you want.
The final segment of this filter is the value that you must enter. This will be the actual name of the application for this example.
If you want to add additional rules to the filter, just click the + button again. When you are done, remember to click the Save Button. The filters you create are reusable on other conditions that have the same filterable criteria, so you can create one filter for all of your Agent Job conditions, for example.
This is just one of the many methods you have to customize email alerts. In this case, you're filtering based on the content of the alert, but you also have the option to use Rulesets – which define how often to send alerts, or Email windows – which are configured in each user or group and let you specify time frames a user should or should not receive emails.