Alert Acknowledge massage in Report with event details.

Hi,

Anyone is using the report where we can get alert triggered event with alert acknowledge massage, below is the test example test sheet based on this we can create report,

Event TimeNodeEvent TypeMessageAcknowledged-statusAcknowledged Alert-Note Acknowledged-timeAcknowledged-By

Please suggest, how we can create a report. with above details.

Parents
  • something like this?

    select

    ahv.TimeStamp

    ,Caption

    ,ahv.EventTypeWord AS 'Event Type'

    ,Message

    ,asv.Acknowledged AS 'ACK Status'

    ,asv.Notes

    ,asv.AcknowledgedTime

    ,asv.AcknowledgedBy

    FROM AlertHistoryView ahv with(nolock)

    JOIN Nodes n WITH(NOLOCK) ON N.NodeID = RelatedNodeId

    JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID

    JOIN AlertDefinitionsView adv with(nolock) ON adv.AlertDefID = asv.AlertDefID

    where ahv.EventTypeWord IN ('Note','Triggered','Acknowledged') AND

    ahv.timestamp > DATEADD(dd,-7,getdate()) --7 day filter

  • Hi,

    thanks for giving the quick response, however the query is working fine but as i need again your help to get the correct report as i want,

    Actually i am using the bellow query to get the report for last 7 days, which is working fine and giving the correct report

    *************************************************************************************

    Select NodeID, Event_Time, NodeName, Event_Type,  Cast(Message As nvarchar(250)) as Message, Region_Country, Region, Company, Office, Server_Class, Server_Description, Owner From ( SELECT Nodes.NodeID AS NodeID,

    Events.EventTime AS Event_Time,

    Nodes.Caption AS NodeName,

    Events.EventType AS Event_Type,

    Events.Message AS Message,

    Nodes.Region_Country AS Region_Country,

    Nodes.Region AS Region,

    Nodes.Company AS Company,

    Nodes.Office AS Office,

    Nodes.Server_Class AS Server_Class,

    Nodes.Server_Description AS Server_Description,

    Nodes.Owner AS Owner

    FROM

    Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

    WHERE

    ( EventTime BETWEEN 42654 AND 42661.6666666667 )

    AND 

    (

      (Events.EventType = 5000 ) OR

      (Events.EventType = 520 ) OR

      (Events.EventType =  521) OR

      (Events.EventType = 10)

    )

    ) As r ORDER BY 2 DESC

    { Note - 5000 means - alert triggered, 521 - hardware critical, 520 - Hardware warning, 10 - Interface down }

    ***************************************************************************************************************

    out put of report is like -

    pastedImage_12.png

    But i would like to add below more column in above query -

    pastedImage_13.png

    Could you please merge the below query with above one so that i can get the correct report.

    ****************************************************************

    select

    ahv.TimeStamp

    ,Caption

    ,ahv.EventTypeWord AS 'Event Type'

    ,Message

    ,asv.Acknowledged AS 'ACK Status'

    ,asv.Notes

    ,asv.AcknowledgedTime

    ,asv.AcknowledgedBy

    FROM AlertHistoryView ahv with(nolock)

    JOIN Nodes n WITH(NOLOCK) ON N.NodeID = RelatedNodeId

    JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID

    JOIN AlertDefinitionsView adv with(nolock) ON adv.AlertDefID = asv.AlertDefID

    where ahv.EventTypeWord IN ('Note','Triggered','Acknowledged') AND

    ahv.timestamp > DATEADD(dd,-7,getdate()) --7 day filter

    **********************************************************************************************

    Thanks in advance.

  • Hi I_Like_Eggs,

    Have you get any trick to merge the both query,

    Thanks

  • HI,

    Have you got any trick to merge the both query. i need this report - please ,

    Actually daily 1000 of alerts are generating, i want to report which one alert is acknowledge or not acknowledged by team.

    Thanks

    k

Reply Children
  • Yo dude, apologizes for the delay thanks for the nudge emoticons_grin.png i had to comment out the CP but should give you what you want

    pastedImage_6.png

    Select NodeID, Event_Time, NodeName, Event_Type, RELATEDNODEID, Acknowledged,AcknowledgedBy, Cast(Message As nvarchar(250)) as Message

    From ( SELECT Nodes.NodeID AS NodeID,

    Events.EventTime AS Event_Time,

    Nodes.Caption AS NodeName,

    Events.EventType AS Event_Type,

    Events.Message AS Message

    ,AHV.RelatedNodeId

    ,ASV.Acknowledged

    ,ASV.AcknowledgedBy

    --Nodes.Region_Country AS Region_Country,

    --Nodes.Region AS Region,

    --Nodes.Company AS Company,

    --Nodes.Office AS Office,

    --Nodes.Server_Class AS Server_Class,

    --Nodes.Server_Description AS Server_Description,

    --Nodes.Owner AS Owner

    FROM

    Nodes INNER JOIN (Events INNER JOIN EventTypes Events_EventTypes ON (Events.EventType = Events_EventTypes.EventType)) ON (Nodes.NodeID = Events.NetworkNode)

    INNER JOIN AlertHistoryView ahv with(nolock) ON AHV.RelatedNodeId = NODES.NodeID

    INNER JOIN AlertStatusView asv with(nolock)ON asv.AlertDefID = ahv.AlertRefID AND ahv.AlertObjectID = asv.AlertObjectID

    WHERE

    ( Events.EventTime > DATEADD(day,-7,getdate())) -- check for 7 days

    AND

    (

      (Events.EventType = 5000 ) OR

      (Events.EventType = 520 ) OR

      (Events.EventType =  521) OR

      (Events.EventType = 10)

    )

    ) As r ORDER BY 2 DESC

  • Hi,

    When i am running the above query the result is showing not fruitful means, node name are showing multiple times for same date alert, but when i checked the trigger event in node, not showing the all alert for same date, query is picking the data  from table for old days,

    pastedImage_0.png

  • SELECT n.Caption AS NODE

    ,CASE

        WHEN DATEDIFF(DAY, e.EventTime, getdate()) > 3

        THEN CONVERT(NVARCHAR(50), DATEDIFF(DAY, e.EventTime, getUTCdate())) + ' days ago'

        ELSE CASE

          WHEN DATEDIFF(HOUR, e.EventTime, getdate()) > 3

          THEN CONVERT(NVARCHAR(50), DATEDIFF(HOUR, e.EventTime, getdate())) + ' hours ago'

          ELSE CONVERT(NVARCHAR(50), DATEDIFF(MINUTE, e.EventTime, getdate())) + ' min ago'

        END

      END AS 'Time'

      ,et.EventType AS 'TYPE'

    ,e.Message 'Message'

    ,ast.Notes

    ,ast.Acknowledged AS 'ACK'

    ,ast.AcknowledgedTime AS 'ACK TIME'

    ,ast.AcknowledgedBy AS 'ACK BY'

    FROM Events E

    INNER JOIN EventTypes ET ON (E.EventType = ET.EventType)

    INNER JOIN Nodes N ON (N.NodeID = E.NetworkNode)

    INNER JOIN AlertStatusView ast WITH(NOLOCK) ON (ast.ActiveObject = E.NetworkNode)

    WHERE

    ((E.EventTime > DATEADD(DAY,-7,GETDATE())))

    AND ET.EventType IN ('5000','520','521','10')

    order by E.EventTime desc

    try this little beauty out emoticons_grin.png