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.

How to create "Triggered Alerts History" report for "All Configured Alerts"

How to create report for "All Configured Alerts" triggered history.

  • Not pretty clear on the query that you have posted, active alerts can still be pulled from Solarwinds but not the one's that have cleared unless you are logging it separately in your actions.

  • This is the report I use, it shows which objects are firing which alerts and how many times in the previous 30 days, but it would be pretty straight forward to change it to just show a count of alerts or whatever arrangement of information makes sense for your purposes.

    --report on alerts triggered

    select ac.Name

    --,ah.Message

    ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]

    ,count(ah.message) as [Alert Count]

    ,EntityCaption as [Trigger Object]

    ,EntityDetailsUrl as [_linkfor_Trigger Object]

    ,case

    WHEN RelatedNodeCaption=EntityCaption THEN 'Self'

    When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

    End as [Parent Node]

    ,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

    ,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]

    ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

    FROM Orion.AlertHistory ah

    left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid

    left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid

    left join Orion.Actions a on a.actionid=ah.actionid

    left join Orion.Nodes p on p.nodeid=RelatedNodeID

    WHERE

    daydiff(ah.timestamp,GETUTCDATE())<30

    and eventtype=0

    group by name,  [Trigger Object], RelatedNodeCaption

    order by [Alert Count] desc

    pastedImage_5.png

  • Found better report for All Triggered Alerts in last 1 Day , 7 Days and 30 Days. 

    SELECT DISTINCT 

      ahv.Name 'Alert Name' 

      ,ahv.RelatedNodeCaption 'Parent Device'   

      ,ahv.EntityCaption 'Alert Object' 

      ,ISNULL(lastOne.Qty,0) 'Last 1 Day' 

      ,ISNULL(lastSeven.Qty,0) 'Last 7 Days' 

      ,ISNULL(lastThirty.Qty,0) 'Last 30 Days' 

    FROM AlertHistoryView ahv 

    FULL OUTER JOIN 

      (SELECT   

      Name 

      ,RelatedNodeCaption 

      ,EntityCaption 

      ,COUNT(1) 'Qty' 

      FROM AlertHistoryView   

      WHERE EventTypeWord = 'Triggered'   

      AND TimeStamp > (GetDate()-1)  

      GROUP BY Name, RelatedNodeCaption, EntityCaption) lastOne ON ahv.Name = lastOne.Name AND ahv.RelatedNodeCaption = lastOne.RelatedNodeCaption AND ahv.EntityCaption = lastOne.EntityCaption 

    FULL OUTER JOIN 

      (SELECT   

      Name 

      ,RelatedNodeCaption 

      ,EntityCaption 

      ,COUNT(1) 'Qty' 

      FROM AlertHistoryView   

      WHERE EventTypeWord = 'Triggered'   

      AND TimeStamp > (GetDate()-7)   

      GROUP BY Name, RelatedNodeCaption, EntityCaption) lastSeven ON ahv.Name = lastSeven.Name AND ahv.RelatedNodeCaption = lastSeven.RelatedNodeCaption AND ahv.EntityCaption = lastSeven.EntityCaption 

    FULL OUTER JOIN 

      (SELECT   

      Name 

      ,RelatedNodeCaption 

      ,EntityCaption 

      ,COUNT(1) 'Qty' 

      FROM AlertHistoryView   

      WHERE EventTypeWord = 'Triggered'   

      AND TimeStamp > (GetDate()-30)

      GROUP BY Name, RelatedNodeCaption, EntityCaption) lastThirty ON ahv.Name = lastThirty.Name AND ahv.RelatedNodeCaption = lastThirty.RelatedNodeCaption AND ahv.EntityCaption = lastThirty.EntityCaption

     

    WHERE ahv.Name IS NOT NULL

    AND

    (

      lastOne.Qty > 0 OR

      lastSeven.Qty > 0 OR

      lastThirty.Qty > 0

    )

    -- AND ahv.Name IN ('Alert me when an application goes down','Alert me when a component goes down')

    ORDER BY ahv.Name, ISNULL(lastThirty.Qty,0) DESC, ahv.RelatedNodeCaption, ahv.EntityCaption

    Thank you!

  • How would I add a TOTALS column to each of the time spans - Basically need total number of triggered alerts for today, last 7 days, last 30 days?

  • These are great SQL reports... Thank you!!! emoticons_cool.png

    By the way, I would like to exclude some hostnames/Parent Devices from the list.  I tried adding  

    WHERE RelatedNodeCaption != 'USXXX%

    However,  depending where I put the above filter, I get Cannot Be Bound errors, syntax errors or the filter seems to be ignored altogether.  Can you tell me how I need to set this filter to exclude some hostnames?


    Again, many thanks!

  • you cannot use wildcards with != in sql, you should do relatednodecaption not like 'usxxx%'

  • I did try the NOT LIKE filter as well and got the same results.  I just tried again to confirm...  Any ideas?  Thanks again.emoticons_cool.png

  • You'll have to post your whole script to see what you are missing, sounds like you are breaking the syntax somewhere in there.

  • I tried with NOT LIKE as well... It did not make a difference...  I am still seeing USXXX devices in my report.  Very puzzling...emoticons_confused.png