MSSQL Database Error Log Monitoring

[jkinning]

I am trying to figure out a way to get alerted from specific entries on a MSSQL dbo.LOG_ERR log.

Here’s an example of what we need starting out:

On the Dev MSSQL server, I want a specific user to be emailed whenever there is an entry on dbo.LOG_ERR where

• ERR_LEVEL_TXT = “Error”
• MODULE_ID = 8

The Module ID will be different on every environment. This example is from the Dev environment so we'll have the same requirements for QA and Prod.

Down the line, I would like to have alerts for different types of error messages with different thresholds but for now I’d like us to just get emailed for every error just to be safe.

[mcapra]

The check_logfiles plugin is probably the most light-weight candidate:
https://exchange.nagios.org/directory/P ... es/details

Otherwise if you're aiming to look at and alert on multiple log files, Nagios Log Server is probably a better candidate.

[jkinning]

Can Nagios run a query against a SQL Server database? That dbo.LOG_ERR is a table within the database so I would need to query against that to find that information.

Other suggestion might be to have them write a bat using the SQLCMD to run the query looking for those values and writing the results to a file and then doing something like a check to see if the file is > 0 bytes to send an email. Not sure what value Nagios would be if that is the solution.

[mcapra]

That dbo.LOG_ERR is a table within the database so I would need to query against that to find that information.

Ah, my mistake! That seems obvious now reading your post again

Is the MSSQL Query configuration wizard provided with Nagios XI not adequate for your use case? That's the easiest way to run a query against a MSSQL database.

[jkinning]

I am going to test out this query using the MSSQL query check.
SELECT count(*) FROM Digital_d.dbo.LOG_ERR where ERR_LEVEL_TXT = 'Error' and MODULE_ID = 8;

My only concern is this check will continue to fire off a notification unless there is a way I can check every X number of minutes and just query for any new entries within that time frame; otherwise, it will always be true since these events will be present and send out notifications for alerts that have already been alerted on. Or does this check not work that way? Plan to test out this morning.

[mcapra]

My only concern is this check will continue to fire off a notification unless there is a way I can check every X number of minutes and just query for any new entries within that time frame

If the table has some sort of field indicating when the event occurred, you could reference that in your query. Otherwise yes this will indeed fire off a critical/warning if the entries exist regardless of when they showed up.

If this table is truncated on a regular basis, you could adjust your check interval to match that.