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

How to create "Triggered Alerts History" report for "All Configured Alerts"

Jump to solution

How to create report for "All Configured Alerts" triggered history.

1 Solution
Level 9

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!

View solution in original post

0 Kudos
10 Replies
Level 14

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!

0 Kudos

you cannot use wildcards with != in sql, you should do relatednodecaption not like 'usxxx%'

- Marc Netterfield, Github
0 Kudos

I tried with NOT LIKE as well... It did not make a difference...  I am still seeing USXXX devices in my report.  Very puzzling...

0 Kudos

I did try the NOT LIKE filter as well and got the same results.  I just tried again to confirm...  Any ideas?  Thanks again.

0 Kudos

You'll have to post your whole script to see what you are missing, sounds like you are breaking the syntax somewhere in there.

- Marc Netterfield, Github
0 Kudos
Level 9

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!

View solution in original post

0 Kudos

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

0 Kudos

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

pastedImage_5.png

- Marc Netterfield, Github
0 Kudos

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.

0 Kudos