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.

table of unacknowledged critical alerts

This is what I am trying to use to get a table of active unacknowledged critical alerts:

SELECT [dbo].[AlertActive].alertactiveid, [dbo].[ALERTHISTORYVIEW].alertactiveid, [dbo].[ALERTHISTORYVIEW].Name, [dbo].[ALERTHISTORYVIEW].eventtypeword , [dbo].[ALERTHISTORYVIEW].alertnote, [dbo].[AlertActive].Acknowledgedby, [dbo].[AlertActive].triggereddatetime, [dbo].[ALERTHISTORYVIEW].severity

FROM [dbo].[AlertActive]

join [dbo].[ALERTHISTORYVIEW]

on [dbo].[AlertActive].alertactiveid = [dbo].[ALERTHISTORYVIEW].alertactiveid

where [dbo].[ALERTHISTORYVIEW].eventtypeword = 'Triggered'

and [dbo].[AlertActive].Acknowledgedby is NULL

and [dbo].[ALERTHISTORYVIEW].severity = 2

order by [dbo].[AlertActive].triggereddatetime

however when i run:

SELECT [dbo].[AlertActive].alertactiveid, [dbo].[ALERTHISTORYVIEW].alertactiveid, [dbo].[ALERTHISTORYVIEW].Name, [dbo].[ALERTHISTORYVIEW].eventtypeword , [dbo].[ALERTHISTORYVIEW].alertnote, [dbo].[AlertActive].Acknowledgedby, [dbo].[AlertActive].triggereddatetime, [dbo].[ALERTHISTORYVIEW].severity

FROM [dbo].[AlertActive]

join [dbo].[ALERTHISTORYVIEW]

on [dbo].[AlertActive].alertactiveid = [dbo].[ALERTHISTORYVIEW].alertactiveid

where [dbo].[ALERTHISTORYVIEW].eventtypeword = 'Triggered'

I see 283 results, when I look at the web console I see 311 active alerts.

I want to pull data from the alerthistoryview table to work out if an alert is acknowledged and critical but it seems the actions recorded in the alerthistoryview do not record a trigger for each active alert and can see no pattern as to when this is or isn't happening.

As this table is to be critical to ensuring that critical alerts are triggered by engineers I do not want them becoming overly dependant on a table that might not show all active unacknowledged critical alerts.

I have also tried autoacknowledgement of non critical alerts(to allow me to just have a table of unacknowledged alerts for engineers) but it seems as though credentials to access the orion web console cannot be passed through a POST or GET action.

  • Hi All,

    Use the below Query to get the exact result which you expected as I see above query missed to pull RESET alerts.

    SELECT A.AlertObjectID,

           B.Name as [AlertName],

       B.RelatedNodeCaption as [NodeName],

       A.TriggeredMessage,

           B.eventtypeword as Status ,

    alertnote as Notes,

    Acknowledgedby,

    triggereddatetime,

    severity

    FROM AlertActive A

    join AlertHistoryView B

    on A.AlertObjectID = B.AlertObjectID

    WHERE B.EventTypeWord='RESET'

    OR B.EventTypeWord='TRIGGERED'

    OR B.EventTypeWord='NOTE'