Triggered Alert History - Last 30 Days

This report will enumerate the triggered alerts over the last 30 days as such:

2016-09-01_12-37-39.png

This is a Web-based Report (not Report Writer) based on a SQL query, simply go to your SolarWinds website, Settings > Manage Reports > Import the file

SQL Query:

=================================================================================

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

=================================================================================

Anonymous