This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Alert report with Acknowledge details

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

  • 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!

  • Thanks for your immediate reply. I tried above changes but its not giving any output or neither any error.

    If possible can you run this query in your infra.

    I have used "-4" in query because I am getting output in UTC time.