Custom Alert Query

Custom Alert Query

Summary

Custom Monitoring Queries are the foundation of Custom Alerts. You write a T-SQL query that returns the data you want to monitor, select the instances it should run on, and validate it against a live instance. Once validated, the query runs automatically every minute against all configured targets and feeds, and the results are directly fed into any Custom Alerts built from it.

A single validated query can power multiple Custom Alerts, each evaluating a different value and object column pair from the same result set.

Permissions required: Administrator or Power User — Admin Console → Monitoring

Accessing Custom Monitoring Queries

1. Open the Admin Console.
2. Go to Monitoring.
3. Select Custom Monitoring Queries.

The query list displays the following for each entry:

You can sort, search, and filter the list. Use the action menu on each row to edit or delete a query.

Designing Your Query: The Key/Value Pair Concept

Before writing your query, it is important to understand how WISdom evaluates Custom Alert results over time.

WISdom runs your query every minute and compares each result row to the configured alert threshold. To track whether a condition persists — and for how long — WISdom needs a consistent way to identify the same object between runs. That identification comes from the Object Column, which acts as a stable key.

Think of each row as a key/value pair:

• Key (Object Column) — Uniquely identifies the object being measured. This value must remain stable between query executions so WISdom can track the same object over time.
• Value (Value Column) — The numeric measurement being evaluated against the alert threshold.

Choosing a Good Key

A good key is one that clearly identifies the object and does not change across evaluations. Good examples include database names, table names, drive letters, instance names, or concatenated identifiers.

Avoid using timestamps or auto-incrementing values as part of your key. Since the query runs every minute, a timestamp key would produce a different value on every run, making it impossible for WISdom to track the same object across evaluations.

Returning Multiple Key/Value Pairs

Your query can return multiple objects and value columns in a single result set. When you create a Custom Alert from this query, you select which specific object column and value column pair to use for that alert. If you need to alert on multiple values from the same query, create a separate Custom Alert for each object/value pair — all referencing the same query.

Example: A query that monitors identity column exhaustion might return TableName as the object, along with both PercentUsed and last_value as separate value columns. You could create one alert for PercentUsed > 80 and a second alert for last_value > 2,147,000,000, both using the same query.

Creating a Custom Monitoring Query

Step 1 — Start a new query

1. Go to Admin Console → Monitoring → Custom Monitoring Queries.
2. Click the blue [+ Create New Query] button.

The Create Custom Query panel opens.

Step 2 — Enter basic information

Provide the following:

• Name (required) — Choose a clear, descriptive name that reflects what the query monitors.
  Example: AlwaysOn Redo Queue Size by Database.
• Description (required) — Describe what the query checks and why it is useful.
  Example: Monitors AlwaysOn redo queue size per database to detect replication lag.

Step 3 — Select target instances

Use the Target Instances selector to choose which SQL Server instances this query is allowed to run against. WISdom will only execute the query on the instances you select for alert evaluation.

You can select one or more instances. Only instances assigned to your monitored environment are available here.

Step 4 — Select validation instance

Use the Validate on Instance dropdown to select the SQL Server instance that will run the validation query. For best performance, choose a development or test instance with a lighter workload. WISdom will run the query on this instance to confirm it executes successfully, returns a valid result set, and passes safety checks.

Only SQL instances that are part of your monitored environment will appear in this list.

Step 5 — Write the SQL query

In the Query editor, paste or type your T-SQL. Your query must meet the following requirements:

• Read-only — No INSERT, UPDATE, DELETE, DROP, or any other write operations.
• Returns at least one numeric value — Used as the alert value when configuring the Custom Alert.
• Object identifier column — Including a stable identifier column is optional in the UI, but strongly recommended. See the Designing Your Query: The Key/Value Pair Concept section above for guidance.

Tip: Test your query directly in SQL Server Management Studio first to verify performance and confirm the results look as expected before adding it to WISdom. When testing, ensure you are connected to the master database context to replicate how WISdom will execute the query.

Example — Monitoring AlwaysOn Redo Queue Size:

SELECT
    a.name                      AS AvailabilityGroupName,
    d.name                      AS DatabaseName,
    SUM(s.redo_queue_size)      AS RedoQueueSizeKB
FROM sys.availability_groups a
JOIN sys.dm_hadr_database_replica_states s
    ON s.group_id = a.group_id
JOIN sys.databases d
    ON d.database_id = s.database_id
WHERE s.is_local = 1
GROUP BY a.name, d.name;

In this example, DatabaseName is the stable object key and RedoQueueSizeKB is the numeric value to monitor. You would map these columns when creating the Custom Alert.

Example — Concatenating multiple columns into a single unique key:

When no single column uniquely identifies an object, concatenate columns to form a composite key.

SELECT
    s.name + '.' + t.name + '.' + c.name   AS TableIdentityColumn,
    ic.last_value * 100.0 / ic.max_value    AS PercentUsed
FROM sys.identity_columns ic
JOIN sys.tables t ON t.object_id = ic.object_id
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = ic.object_id
    AND c.column_id = ic.column_id;

Here, TableIdentityColumn is a concatenated key that uniquely identifies each identity column across all tables and schemas. PercentUsed is the numeric value to evaluate.

Step 6 — Validate and save

Click [Validate and Save]. WISdom saves the query and runs validation against the instance you selected in Step 4. The Validation Status updates once validation completes.

Understanding Validation Status

Editing a Custom Monitoring Query

1. Go to Admin Console → Monitoring → Custom Monitoring Queries.
2. Click the query row, or open the action menu and select Edit.

What you can change depends on whether the query is currently in use by a Custom Alert:

Query not in use by any Custom Alert:
You can modify the Name, Description, Target Instances, and SQL text. Any change to the SQL will trigger re-validation when you save.

Query in use by one or more Custom Alerts:
The SQL text is locked and cannot be modified. You can still update the Name, Description, and Target Instances. This prevents breaking existing alerts that depend on the current query structure.

Related Topics

• Creating a Custom Alert
• Alert Profiles
• Alert Rules
• Silencing Windows