Nodes down with acknowledge report no longer working properly after enabling web based alerts.

rarbuckle1892

We recently enabled the web based alerting platform and it seems to have broken one of our downed nodes displays. We use the below query to get the node status, name, who acknowledged the alert, the notes, and the length down. Immediately after turning on the alerts I had to modify the query to use AlertStatusView instead of AlertStatus and we are now having an issue where previous acknowledge notes are showing each time the node goes down again. For example If a node is down today and I acknowledge it with a note that same note will appear if that node goes down again tomorrow or the next day and any new acknowledgement I make is added to the end of the existing note. Before this change the notes would only be for that particular instance of the alert...

DECLARE @CurrentTime As DATETIME

SET @CurrentTime = GETDATE()

SELECT

        n.NodeID,

        n.GroupStatus ,

        n.Caption ,

        ev.EventID ,

        ev.EventTime AS EventStartTime , a.AcknowledgedBy, a.Notes,

        CASE WHEN DATEDIFF(DAY, ev.EventTime, @CurrentTime) > 1

             THEN CONVERT(VARCHAR, ev.EventTime, 101) + ' '

                  + CONVERT(VARCHAR, ev.EventTime, 108)

             ELSE CONVERT(VARCHAR, ev.EventTime, 108)

        END AS DownAt ,

        CASE WHEN ev.EventTime IS NULL Then 'Unknown'

             WHEN DATEDIFF(second, ev.EventTime, @CurrentTime) < 60

             THEN CONVERT(VARCHAR(10), DATEDIFF(second, ev.EventTime,

                                                @CurrentTime)) + ' Seconds'

             WHEN DATEDIFF(minute, ev.EventTime, @CurrentTime) < 60

             THEN CONVERT(VARCHAR(10), DATEDIFF(minute, ev.EventTime,

                                                @CurrentTime)) + ' Minutes'

             WHEN DATEDIFF(minute, ev.EventTime, @CurrentTime) < 1440

             THEN CONVERT(VARCHAR(10), DATEDIFF(hour, ev.EventTime,

                                                @CurrentTime)) + ' Hours'

             WHEN DATEDIFF(minute, ev.EventTime, @CurrentTime) < 44640

             THEN CONVERT(VARCHAR(10), DATEDIFF(day, ev.EventTime,

                                                @CurrentTime)) + ' Days'

             WHEN DATEDIFF(mi, ev.EventTime, @CurrentTime) < 525600

             THEN CONVERT(VARCHAR(10), DATEDIFF(month, ev.EventTime,

                                                @CurrentTime)) + ' Months'

             ELSE CONVERT(VARCHAR(10), DATEDIFF(minute, ev.EventTime, @CurrentTime))

        END AS LengthDown

FROM   dbo.Nodes n

        LEFT JOIN Events ev ON ev.EventID =  (SELECT TOP 1 in_ev.EventID FROM Events in_ev WHERE in_ev.NetworkNode = n.NodeID AND in_ev.EventType = 1 ORDER BY in_ev.EventTime DESC)

INNER JOIN AlertStatusView a ON a.ActiveObject = n.NodeID AND a.AlertDefID = 'f9c359b3-3626-49e2-a250-51d0b3bd389d'

WHERE   n.StatusDescription like '%Down%'

ORDER BY ev.EventTime DESC