2 Replies Latest reply on May 21, 2016 1:51 AM by Vijay Raje

    Alert report with Acknowledge details

    Vijay Raje

      I have created below sql report to show Alert report with Acknowledge details for previous day. but its not showing correct data, can someone check and help me.

       

      Select o.AlertObjectID As Ticket_Number,o.AlertID,o.EntityCaption as Object, o.RelatedNodeCaption as NodeName, N.IP_Address, o.RelatedNodeID as NodeID,

      dateadd(HH,-4,a.TriggeredDateTime) as FirstAlertTime, dateadd(HH,-4,o.LastTriggeredDateTime) as LastAlertTime, a.TriggeredMessage, o.EntityType, o.AlertNote, a.AcknowledgedBy,dateadd(HH,-4,a.AcknowledgedDateTime) as AckTime

       

      From AlertObjects O

       

      INNER JOIN AlertActive A on (o.AlertObjectID = a.AlertObjectID)

      INNER JOIN Nodes N on (o.RelatedNodeID = N.NodeID)

       

      WHERE

      LastTriggeredDateTime > (dateadd(day,datediff(day,0,getdate()),0))

      LastTriggeredDateTime > DATEADD(d,-1,GETDATE())

       

      order by o.LastTriggeredDateTime asc

        • Re: Alert report with Acknowledge details
          Steven Klassen

          Hi there, I'm not sure what you're doing with the -4 math (time zone maybe?), but I've fixed the where clause so that it keeps your results between 00:00:00 and 23:59:59 yesterday:

           

          SELECT
              o.AlertObjectID AS Ticket_Number,
              o.AlertID,
              o.EntityCaption AS [Object],
              o.RelatedNodeCaption AS NodeName,
              n.IP_Address,
              o.RelatedNodeID AS NodeID,
              DATEADD(HH, -4, a.TriggeredDateTime) AS FirstAlertTime,
              DATEADD(HH, -4, o.LastTriggeredDateTime) AS LastAlertTime,
              a.TriggeredMessage,
              o.EntityType,
              o.AlertNote,
              a.AcknowledgedBy,
              DATEADD(HH, -4, a.AcknowledgedDateTime) AS AckTime
          From AlertObjects o
          INNER JOIN AlertActive a on (o.AlertObjectID = a.AlertObjectID)
          INNER JOIN Nodes n on (o.RelatedNodeID = n.NodeID)
          WHERE o.LastTriggeredDateTime >= (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) - 2)
          AND o.LastTriggeredDateTime < (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) - 1)
          --WHERE LastTriggeredDateTime > (DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
          --AND LastTriggeredDateTime > DATEADD(DAY, -1, GETDATE())
          ORDER BY o.LastTriggeredDateTime ASC
          

           

          Hope this helps!