Custom SQL Alert on a different Condition/Table

The new log manager product logs to the table dbo.events messages like "Message from syslog dropped since 1.1.1.1 is not monitored on engine xxxxxxx"

1 - People with view limitations wont see this event as the node might be theirs, but they are not admins

2 - People dont tend to look at the events list like they do Active Orion Alerts.

3 - when this is true, we wont get node alerts because messages are being dropped.

My solution - to create a Custom Orion alert to tell us when this message is seen, as the event message shows the source IP the syslog/trap has been dropped from. Whichever admin then is assigned the alert, has the responsibility to fix it.

We can use an SQL query like the below to filter these messages in the events table. the EventID appears to be 6600, and we can filter for the words "traps" or "syslog". We can then also run this, for example every 4/6 hours to tell us if we have had any messages dropped.

the problem is that the top level "Set up your SQL condition" does not allow you to choose any table, only a pre-defined list.

I have read about doing a "join" but we can't join on a table for nodes, as the node is probably not known (thats why the message is dropped).

Does anyone have an idea how I could make this SQL query run and generate an alert with any records that are true within the Orion alert?

SELECT * FROM [dbo].[Events] where EventType = 6600 AND EventTime > DATEADD (minute, -480, Sysdatetime()) AND Message LIKE '%Syslog%'

Find more posts tagged with

custom sql alerts

Accepted answers

All comments

mesverrum

You could join it to your polling engine based on the engineid that received the message. Generate an alarm along the lines of "%servername% has dropped 500 messages from unknown nodes in the last hour." Personally, I would probably just make it a report though since I only alert on things that need immediate resolution and a bulk list of nodes that are not yet in orion feels more like a once a day to do list kind of item to me.

ashleyh

I got the join working using a "right" join, but because of the mandatory top level select statement on pollers, we will never be able to see the "message" coloumn of the events table.

SELECT Engines.ServerName, Engines.EngineID FROM Engines

RIGHT JOIN Events ON Engines.EngineID=Events.EngineID

where EventType = 6600 AND EventTime > DATEADD (minute, -480, Sysdatetime()) AND Message LIKE '%Syslog%'

works but obviously only shows the Server Name and engineID column which is mandated.

Semi-regular reporting it is, I hate emailing reports all the time!

Thanks.

mesverrum

Yeah, you could pull those messages using some sql trickery in the message body. You'd add a custom sql variable to your message and the body of it would be something similar to the method discussed in this thread

sql select in alert message body

Basically, custom sql variables can only return a single "cell" of data, but you use can sql with xml and special character manipulation to make a list of values into a single cell that is formatted in a way that the mail server will display it as a table of values instead of one gnarly string. Sounds harder than it is once you learn the correct syntax, I use it pretty often.