Custom alerts are a way to extend the built-in alerts of SQL Governor. Custom alerts are not tied to performance counters the same way built-in alerts are. The only limitation is that custom alerts have to be based on T-SQL queries.
Navigating to Alerts > Custom alerts shows a grid with the custom alerts you've created (empty by default):
To create a new custom alert, click on Add new alert, which opens this form:
First, give the custom alert a Name and a Description. Next, select from the Grain dropdown whether this is an instance or database-level alert. For instance-level alerts, the SQL query is run only once per monitored instance (against the master database). For database-level alerts, the SQL query is run against every database being monitored.
Reversed means that lower values are worse and higher are better; i.e. the Warning and alert thresholds are reversed. If an alert is not reversed, any value returned by the SQL query that exceeds one of the thresholds will cause a warning or an alert. For a reversed alert, any value that is lower than either of the thresholds will cause a warning or an alert.
Email address is the address to which warning and alert emails will be sent. Email subject defines the template which is populated when the email is sent. The subject line can contain both static text and dynamic fields. Dynamic fields are marked by square brackets and include the following:
- [DOMAIN]
- [SERVER]
- [INSTANCE]
- [DATABASE]
- [ALERT]
- [VALUE]
- [TIME]
[DOMAIN], [SERVER], [INSTANCE] and [DATABASE] are references to the monitored instance/database that caused the alert. [ALERT] is the name given to the alert. [VALUE] is the value returned by the SQL query. [TIME] is the date and time the query was run.
For example, a custom alert with the email subject template "Alert [ALERT] caused by [DOMAIN].[SERVER].[INSTANCE] at [TIME]" could generate an alert email with the actual subject "Alert TEST caused by TESTDOMAIN.SQLSERVER12.MSSQLSERVER at 2021-11-05 10:09:20".
Interval type is either minutes, hours or days and, together with Interval, defines how often the SQL query is run. For example, an interval type of hours and an interval of 4 would mean the query is run every four hours. If Start time is empty, the query is first run as soon as the custom alert is created. If start time has a value, the interval begins from the defined start time, for example every four hours starting from 8 PM. For more information on scheduling alerts, see How to make custom alerts run on a given day of the week.
Enabled defines whether the custom alert is currently in use or not.
SQL text is the actual T-SQL that is run against every instance or database being monitored. For the proxy to recognize the query as a valid custom alert query, it has to return only one row and one column named VALUE_FIELD:
In the example above, we first query the job id's, run times and statuses of all SQL Agent jobs into a temp table, ranked by their run time from latest to oldest. Next, we select only the latest run of each job, calculate the percentage of failed runs and return this percentage as the VALUE_FIELD. The VALUE_FIELD, along with the thresholds defined in the custom alert will determine when a warning or alert is sent.