Need to create an alert for database maintenance check

How can we create an alert for this database maintenance check

SELECT
TOLOCAL(EventTime) AS LocalEventTime, Message, EventType
FROM Orion.Events
Where Message LIKE '%Starting Nightly Maintenance%'
OR Message LIKE '%Nightly maintenance failed%'
OR Message LIKE '%Nightly Maintenance Completed%'
Order by EventTime DESC

Find more posts tagged with

alert configuration

database_maintenance

Comments

james.kluza

Frrom an alerting perspective for a failed condition, you are probably only interested in EventType = 5102. Then you need to establish some criteria to make it an alertable condition.

Something like a count in a particular timeframe and make it where the count is greater than zero to make it trigger. Then make it a Node type alert and associate it with the primary polling engine.

Other things to consider are evaluation time from the default every 1 minute to something closer to once a day since this only only happens at a prescribed time. Save the alerting engine from checking for that criteria all day long.

james.kluza

Something like this would work at the default evaluation interval and persist for about 10 minutes as an active alert:

***This portion is included as part of the advanced SWQL alert config for a Node***
SELECT Nodes.Uri, Nodes.DisplayName FROM Orion.Nodes AS Nodes
***You would add this in the query section***
JOIN (
SELECT COUNT(EVENTID) as [status], as NodeID
FROM Orion.Events
WHERE MINUTEDIFF(tolocal(eventtime),getdate()) < 10 AND EventType= 5102)
as t1 on nodes.nodeid = t1.nodeID
WHERE t1.[status] > 0

Replace <primary_poller_node_ID> with the nodeid of your primary polling engine.

swf.nfs

@james.kluza Thanks i will check and share the updates.

what will be the criteria and query for clearing and reset the alert in this case and may be alert creation for all 3 states like starting, completed and error or warning in database maintenance checks

james.kluza

This would use the default of reset when no longer true. Once that entry ages out of the last 10 minutes, the alert will reset. Alerting on the starting and completion message would not be an effective use of the alerting engine. You want to generate alert activity based on exceptions, not expected behavior.

Keep in mind these aren't 'states', these are log event entries that correlate with database maintenance activity.

I should also state that the console itself tracks this failure as an exception and will display it in the notifications area of the console as well as in the deployment health section of My Deployment.

swf.nfs

Oh yeah, I have an idea to reset the alert condition if event Type generated Nightly Maintenance Completed then alert will be reset and inform via email notification saying that alert has been cleared.

obaid.rahman

Would you please share some screenshots of your alert trigger condition and reset actions, i am actually stuck on it. thank you for your help