Help! Top Alerts and Offenders

Hi Community,

I got a hold of an SQL query from our office archive files which displays the following:

Alert Name - the name of the triggered alert
QTY - is the total number of times the Alert Name has been triggered
Triggered Object - is the top device or interface (or a combination of the device and interface name depending on the triggered alert) name that triggered the 'Alert Name'
Alert Count 30 Days - is the total count in 30 days that the 'Triggered Object' triggered the 'Alert Name'
Most Recent Trigger - is the most recent date/time the 'Triggered Object' triggered the 'Alert Name'

I am a newbie in SQL and can seem to find a way to filter the query to get the desired output.

Wondering if anyone can help to filter the query to display the TOP 10 Triggered Object and the respective Alert Name they've triggered.

Thanks in advanced.


SELECT distinct ac.Name [Alert Name]
,case when EntityCaption = RelatedNodeCaption then entitycaption
else RelatedNodeCaption + ' - ' + entitycaption
end as [Trigger Object]
,count(Message) as [Alert Count 30 days]
,max(ah.TimeStamp) as [Most Recent Trigger]
FROM [dbo].[AlertHistory] ah
join AlertObjects ao on ao.alertobjectid=ah.alertobjectid
join AlertConfigurations ac on ac.alertid=ao.alertid
join Actions a on a.actionid=ah.actionid
join (SELECT distinct ac1.Name, count(Message) as QTY
FROM [dbo].[AlertHistory] ah1
join AlertObjects ao1 on ao1.alertobjectid=ah1.alertobjectid
join AlertConfigurations ac1 on ac1.alertid=ao1.alertid
join Actions a1 on a1.actionid=ah1.actionid
TimeStamp BETWEEN DATEADD(Month, -1, getdate()) and getdate()
and isnull(Message, '') <> ''
and ltrim(rtrim(ac1.Name)) <> ltrim(rtrim(Message))
group by
) tot_ah on = ac.Name
TimeStamp BETWEEN DATEADD(Month, -1, getdate()) and getdate()
--and ac.Name = 'Node rebooted'
and isnull(Message, '') <> ''
and ltrim(rtrim(ac.Name)) <> ltrim(rtrim(Message))
group by tot_ah.QTY,, EntityCaption, RelatedNodeCaption
order by [Alert Count 30 days] desc

Parents Reply Children