This report will enumerate the triggered alerts over the last 30 days as such:
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
=================================================================================
Top Comments