i have a query that returns the alert count for a list of nodes. the alert i'm trying to report on has two trigger actions which i believe is throwing the count off. the alert fires ONCE, but counts that as 2 because of the two trigger actions, or so i suspect. so The alert count for the last 45 days and 24 hours return 2, but should return 1.
Would someone mind taking a look at this and point me in the right direction?
thank you!
select distinct ac.Name as [Alert Name]
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Alert Name]
,ah.Message
,count(ah.message) as [Alert Count 45 days]
,today.[Alert count] as [Alert Count 24 hours]
,EntityCaption as [Trigger Object]
,EntityDetailsUrl as [_linkfor_Trigger Object]
,RelatedNodeCaption as [Parent Node]
,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]
FROM Orion.AlertHistory ah
join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
join Orion.Actions a on a.actionid=ah.actionid
left JOIN (
select distinct ac.Name as AlertName
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]
--,ah.Message
,count(ah.message) as [Alert Count]
,EntityCaption as [Trigger Object]
,EntityDetailsUrl as [_linkfor_Trigger Object]
,RelatedNodeCaption as [Parent Node]
,RelatedNodeDetailsUrl as [_linkfor_Parent Node]
,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]
FROM Orion.AlertHistory ah
join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid
join Orion.AlertConfigurations ac on ac.alertid=ao.alertid
join Orion.Actions a on a.actionid=ah.actionid
WHERE
hourdiff(ah.timestamp,GETUTCDATE())<24
and ah.timestamp < getutcdate()
and ac.name like '%TCC VS01%' --<******************************alert name
group by name, [Trigger Object], [Parent Node]
) today on today.[_linkfor_Name] = '/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID)
WHERE
daydiff(ah.timestamp,GETUTCDATE())<45
and ah.timestamp < getutcdate()
and ac.name like '%TCC VS01%' --<*********************************alert name
--and ( EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%' or ac.name like '%${SEARCH_STRING}%' )
group by name, [Trigger Object], [Parent Node]
order by [Alert Count 45 days] desc
