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.

Report for triggered alerts past 1 day for 2 specified alerts only

Hi All need help setting up a report for triggered alerts history for past 1 day but only on for the two alerts below

Thanks in advance 

Parents
  • What Information do you need in the report? Here's something that I think is close, its SWQL. 

    SELECT 
        AH.AlertObjects.AlertConfigurations.DisplayName as [Alert Name],
        AH.AlertHistoryID, 
        AH.EventType, 
        AH.Message, 
        AH.TimeStamp,
        AH.AlertObjects.EntityCaption, 
        AH.AlertObjects.EntityDetailsUrl, 
        AH.AlertObjects.RelatedNodeCaption, 
        AH.AlertObjects.RelatedNodeDetailsUrl
    FROM Orion.AlertHistory AH
    Where 
           (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
        or  AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)' )
        and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
        and AH.EventType = 0 -- Event for alert trigger
    Order By AH.TimeStamp DESC






  • Just how many times over the past 24 hours the alert triggered 

  • If you want the count, then try: 

    SELECT 
        Count( AH.AlertHistoryID) as [Alerts Triggered]
    FROM Orion.AlertHistory AH
    Where 
           (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
        or  AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)' )
        and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
    
        and AH.EventType = 0 -- Event for alert trigger


    But in either case that looks like the report page, so make sure your data source is using 'SWQL' if it is, then I am not sure why it doesn't run. We will have to look deeper. 

  • Need to add also the following alert name - 

    Meraki Device Interface Down (P3)

    Not sure on swql structure for this 

  • SELECT
        AH.AlertObjects.AlertConfigurations.DisplayName, 
        Count( AH.AlertHistoryID) as [Alerts Triggered]
    FROM Orion.AlertHistory AH
    Where 
           (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
        or  AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)' )
        and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
        and AH.EventType = 0 -- Event for alert trigger
    Group By AH.AlertObjects.AlertConfigurations.DisplayName

    Is this what you mean? Group counts by name

  • sorry i didnt make it clear enough i need the following alert added into the swql as well 

    Meraki Device Interface Down (P3)

  • so basically, it will report on 3 alerts 

Reply Children
  • I missed the word interface, thought it was the same, sorry 

    SELECT
        AH.AlertObjects.AlertConfigurations.DisplayName, 
        Count( AH.AlertHistoryID) as [Alerts Triggered]
    FROM Orion.AlertHistory AH
    Where 
           (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
        or  AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)' 
        or  AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Interface Down (P3)' )
        and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
        and AH.EventType = 0 -- Event for alert trigger
    Group By AH.AlertObjects.AlertConfigurations.DisplayName



  • anyway we can add the alert Count to the following swql  - Count( AH.AlertHistoryID) as [Alerts Triggered]

    SELECT
    AH.AlertObjects.AlertConfigurations.DisplayName as [Alert Name],
    AH.AlertHistoryID,
    AH.EventType,
    AH.Message,
    AH.TimeStamp,
    AH.AlertObjects.EntityCaption,
    AH.AlertObjects.EntityDetailsUrl,
    AH.AlertObjects.RelatedNodeCaption,
    AH.AlertObjects.RelatedNodeDetailsUrl
    FROM Orion.AlertHistory AH
    Where
    (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
    or AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)'
    or AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Interface Down (P3)' )
    and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
    and AH.EventType = 0 -- Event for alert trigger
    Order By AH.TimeStamp DESC

    thanks

  • Try adding the Count as another property. 

    SELECT
    AH.AlertObjects.AlertConfigurations.DisplayName as [Alert Name],
    Count (AH.AlertHistoryID) as [Alerts Triggered],
    AH.AlertHistoryID,
    AH.EventType,
    AH.Message,
    AH.TimeStamp,
    AH.AlertObjects.EntityCaption,
    AH.AlertObjects.EntityDetailsUrl,
    AH.AlertObjects.RelatedNodeCaption,
    AH.AlertObjects.RelatedNodeDetailsUrl
    FROM Orion.AlertHistory AH
    Where
    (AH.AlertObjects.AlertConfigurations.DisplayName like 'Cisco Store Router Down'
    or AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Down (P3)'
    or AH.AlertObjects.AlertConfigurations.DisplayName like 'Meraki Device Interface Down (P3)' )
    and HOURDIFF(AH.TimeStamp , GetDate()) < 24 -- last day in hours
    and AH.EventType = 0 -- Event for alert trigger
    Order By AH.TimeStamp DESC

  • Hi - have tried the above but its not giving me any alert count 

  • i have tried adding the alert triggered table to the report, but it is only picking up one of the alert names instead of 3 

  • Here is another query. I commented the AlertID out, it may be easier to use that in the future.

    SELECT COUNT([AH].AlertHistoryID) AS [Alerts Triggered]
         , [AH].AlertObjects.AlertConfigurations.DisplayName as [Alert Name]
    --     , [AH].AlertObjects.AlertConfigurations.AlertID
         , [AH].EventType
         , [AH].Message
         , [AH].TimeStamp
         , [AH].AlertObjects.EntityCaption
         , [AH].AlertObjects.EntityDetailsUrl
         , [AH].AlertObjects.RelatedNodeCaption
         , [AH].AlertObjects.RelatedNodeDetailsUrl
    FROM Orion.AlertHistory AS [AH]
    WHERE [AH].EventType = 0
      AND [AH].TimeStamp >= ADDHOUR(-24,GETDATE())
    --  AND [AH].AlertObjects.AlertConfigurations.AlertID in (584, 555, 582, 504, 139)
      AND [AH].AlertObjects.AlertConfigurations.DisplayName in ('Cisco Store Router Down', 'Meraki Device Down (P3)', 'Meraki Device Interface Down (P3)')
    GROUP BY [AH].Message
    ORDER BY [AH].TimeStamp DESC
    

  • Have tried the above but for some reason it just not picking up the alert count for meraki device down

  • Have those triggered in the last 24 hours?, you can try a test to extend that by more time to see if they show up. Another thought would be to try using the alertIDs instead of the display names in case the syntax is causing problems. 

  • Yep i changed the time to 48 hours just to test