cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

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]
,tot_ah.QTY
,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
WHERE
TimeStamp BETWEEN DATEADD(Month, -1, getdate()) and getdate()
and isnull(Message, '') <> ''
and ltrim(rtrim(ac1.Name)) <> ltrim(rtrim(Message))
group by ac1.name
) tot_ah on tot_ah.name = ac.Name
WHERE
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, ac.name, EntityCaption, RelatedNodeCaption
order by [Alert Count 30 days] desc

 

 

0 Kudos
4 Replies

First line will be select top 10 etc
- Marc Netterfield, Github

thanks mate

0 Kudos

Just to build on this the line you're looking for will be:

SELECT TOP 10 distinct ac.Name [Alert Name]

 

- David Smith

thanks much

0 Kudos