We have three SEUM playback sites. When all 3 sites agree that a URL is down (Steps 1 or 2 of the transaction), we want to raise an alert. The custom SQL below checks for that condition and only returns rows when specific transactions are "down" from all 3 locations. I've confirmed that the query returns blank when things are online, and returns rows when URLs are down - one row for each failed transaction, so sometimes multiple rows are returned. When we use this query as a Trigger Condition, the Reset Condition never fires when the URL comes back online ("Reset when trigger conditions are no longer true"). The alert remains in the console indefinitely, even though the SQL query returns blank (confirmed independently in SQL Mgmt Studio). Why won't this alert reset?
SELECT SEUM_TransactionsAlertsData.TransactionId AS NetObjectID, SEUM_TransactionsAlertsData.Name AS Name FROM SEUM_TransactionsAlertsData
WHERE
(
SELECT COUNT(DISTINCT tad.TransactionId) AS FailingTransactionsOnFirstStep
FROM SEUM_TransactionsAlertsData tad JOIN SEUM_TransactionStepsAlertsData tsad
ON tad.TransactionId = tsad.TransactionId
JOIN SEUM_RecordingSteps rs
ON rs.StepId = tsad.StepId
WHERE tsad.Status in (0,2)
AND tad.RecordingId = SEUM_TransactionsAlertsData.RecordingId
AND rs.StepOrder in (1,2)
)
=
(
SELECT COUNT(DISTINCT tad2.TransactionId) AS AllTransactions
FROM SEUM_TransactionsAlertsData tad2
WHERE tad2.RecordingId = SEUM_TransactionsAlertsData.RecordingId
)
AND SEUM_TransactionsAlertsData.TransactionId = (
SELECT MIN(tad3.TransactionId) AS FirstTransaction
FROM SEUM_TransactionsAlertsData tad3
WHERE tad3.RecordingId = SEUM_TransactionsAlertsData.RecordingId
)