Hi there, I've been working on this today and I think I finally worked up a solution. Basically this alert uses a custom SQL query which will check if the status value of the UnDP has changed, if so it will get triggered.
I've searched in the forums for this solution but I couldn't find it so I though I might share it in case someone finds it helpful.
Here is the query:
SELECT CustomPollerAssignmentView.AssignmentName
, CustomPollerAssignmentView.CustomPollerAssignmentID
FROM CustomPollerAssignmentView
JOIN (
SELECT CustomPollerAssignmentID
, DateTime
, Status
FROM CustomPollerStatus
WHERE CustomPollerAssignmentID IN (
SELECT CustomPollerAssignmentID
FROM CustomPollerAssignment
WHERE CustomPollerID = '3c52aa41-28a2-40bf-914c-9c94041ae545'
)
) AS t1
ON t1.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID
JOIN (
SELECT *
FROM (
SELECT CustomPollerAssignmentID
, DateTime
, Status
, ROW_NUMBER() OVER (
PARTITION BY CustomPollerAssignmentID ORDER BY DATETIME DESC
) AS rn
FROM CustomPollerStatistics AS cps
WHERE CustomPollerAssignmentID IN (
SELECT CustomPollerAssignmentID
FROM CustomPollerAssignment
WHERE CustomPollerID = '3c52aa41-28a2-40bf-914c-9c94041ae545'
)
) AS tt
WHERE tt.rn = 2
) AS t2
ON t1.CustomPollerAssignmentID = t2.CustomPollerAssignmentID
WHERE t1.Status <> t2.Status