How to create report for "All Configured Alerts" triggered history.
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
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!
etcentene its old but may be you can get some idea from:
These are great SQL reports... Thank you!!!
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!
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.