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

Reply Children
No Data