Advanced Alert using a query

Question

I am hoping someone has conquered this. I need to develop an advanced alert using the following query but the custom sql portion of the Alert engine restricts me to certain criteria.

SELECT C.Caption, Avg(C.AvgLoad)
from CPULoadByDays C
JOIN Nodes N
ON C.NodeID = N.NodeID
WHERE (N.Tower = 'DST' and avgload > 25)
AND C.DateTime >= dateadd(dd, -5, datediff(dd, 0, getdate()))
GROUP by Caption

The intent is to have Orion alert if a node has had an average CPU over the last 5 days over 25%. If so trigger an alert.

Am I over thinking this?

netlogix · Answer

I didn't do an avg or the avg's, so it was a little weird, I think this one is better:

JOIN CPULoadByDays ON CPULoadByDays.NodeID = Nodes.NodeID
  WHERE CPULoadByDays.DateTime >= dateadd(dd, -5, datediff(dd, 0, getdate()))
  GROUP by Nodes.NodeID, Nodes.Caption
  having AVG(CPULoadByDays.AvgLoad) > 25

FormerMember · Answer

Thanks, this is a great solution and seems to work for the most part. I put this in place on one of my servers and it reported some devices however on another Orion it doesn't alert. I took the SQL and ran it against the database and see one node that meets the criteria but can't get it to alert. It tests ok but not on the one that it needs to.

netlogix · Answer

(Edit: create custom sql alert - subtype node)

JOIN CPULoadByDays
ON CPULoadByDays.NodeID = Nodes.NodeID
WHERE (avgload > 25)
AND CPULoadByDays.DateTime >= dateadd(dd, -5, datediff(dd, 0, getdate()))
GROUP by Nodes.NodeID, Nodes.Caption

In the alert, use ${SQL:SELECT Avg(CPULoadByDays.AvgLoad) from CPULoadByDays where NodeID = 63 and DateTime >= dateadd(dd, -5, datediff(dd, 0, getdate()))} in your email