Triggered Alert History - Last 30 Days

Version 3

    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

     

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