How to create report for "All Configured Alerts" triggered history.
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
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
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!
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
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?
etcentene its old but may be you can get some idea from:
Triggered Alert History - Last 30 Days
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!
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.
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...
How we can add date and time stamp query in this report?
