Unique log monitoring

[jkinning]

I have an application team that is using CSV files and a MS SQL 2016 database to write logs to. They have asked me if Nagios is able to monitor these and if the 5th column of the CSV contains Warn, Error, or Fatal a notification needs to be sent out to the appropriate notification group.

On the database side, they’re logging to MS SQL 2016. Specifically a digital_log_p database on SQLPROD_DIGITALLOG,4000 in the LOG_ENTRY table.

The LOG_ENTRY table has a “LOG_SRC_ID” column which signifies which application the log is for.
The LOG_ENV_ID column signifies the environment (Dev, Test, Stage, Prod).
The LOG_SVRTY_LEVEL_ID column signifies the severity of the alert.

They want a separate monitor for each application when an entry occurs that has a LOG_ENV_ID of 4 and a severity level of 4 or higher.
sql-prod-dgl-log-clic-dev-read
sql-prod-dgl-log-clic-test-read
sql-prod-dgl-log-clic-stg-read
sql-prod-dgl-log-clic-read
sql-prod-dgl-log-llic-dev-read
sql-prod-dgl-log-llic-test-read
sql-prod-dgl-log-llic-stg-read
sql-prod-dgl-log-llic-read
sql-prod-dgl-log-services-dev-read
sql-prod-dgl-log-services-test-read
sql-prod-dgl-log-services-stg-read
sql-prod-dgl-log-services-read

I do have the NSClient++ on the Windows server that the csv file log is getting written to but not sure how to put together a check to monitor the 5th column of the CSV and I've never done any database monitor of tables. Looking for some suggestions and advice.

Thanks!

[SteveBeauchemin]

I recently was asked to setup something similar to what you are asking for. In NSClient++ 0.5.0 I ended up using a real-time log file test that lets us know when a specific text syntax is seen in a Log File. Not an eventlog, but a normal text file, such as a CSV file.

The nsclient.ini pertinent settings that made this work for me are as follows.

Note that the setup uses NSCA to send to Nagios as this is real time alerting.

Code: Select all

;-----------------------------
; A list of modules.
[/modules]

; CheckLogFile - Check for errors and warnings in text log files.
CheckLogFile = enabled

; CheckHelpers - Various helper function to extend other checks.
CheckHelpers = enabled

; NSCAClient - NSCA client can be used both from command line and from queries to submit passive checks via NSCA
NSCAClient = enabled

;-----------------------------
; Section for the Log File Checker
[/settings/logfile/real-time]

enabled=1

;-----------------------------
; Section for the Log File 
[/settings/logfile/real-time/checks/Logfile_Test]

;debug=1

column split=;
destination=NSCA
maximum age=20m
empty message=Log found no new records

; example data to match for critical alert
; 3-17-2017 13:53:49.14      0.00: Aborted - Position Delta > 0

file = c:/Log-Data/Log-History.txt

critical = column1 like 'Aborted'
;warning = column2 like 'wurld'
;ok = column1 not like 'ERROR'

top syntax = Data for file ${file} shows "${line}"

;-----------------------------
; Section for NSCA passive check module.
[/settings/NSCA/client]

; HOSTNAME - The host name of the monitored computer. 
; Set this to auto (default) to use the windows name of the computer.  
; auto			Hostname 
; ${host}		Hostname 
; ${host_lc}	Hostname in lowercase 
; ${host_uc}	Hostname in uppercase 
; ${domain}		Domainname 
; ${domain_lc}	Domainname in lowercase 
; ${domain_uc}	Domainname in uppercase 
; hostname = auto
; hostname = ${host_lc}.${domain_lc}
hostname = testhost001
;hostname = testhost002

; CHANNEL - The channel to listen to.
channel = NSCA

From this information, you should be able to set up a text file that you control. Use the following to test.
Make sure that the ;debug=1 is not commented while testing

In an Admin cmd window - run NSClient interactively to see what happens

Code: Select all

net stop nscp
nscp test

In a different window, feed the log file some test data to simulate what you need to alert on.

Code: Select all

echo "column1;column2;column3;column4;critical in column 5;column6" >> c:/Log-Data/Log-History.txt

That will append to the log file you care about - but this is just figuring it out for now, so play with it.

In the nscp test window you should see that it noticed new data, and it should also tell you if it found a match or not.

The data should also show up in your Nagios Unconfigured Object screen.
The line [/settings/logfile/real-time/checks/Logfile_Test] will sent a passive with a service name of Logfile_Test. This should clue you in as to how you can have many files added.
The hostname defined in the ini file will be what is expected in Nagios. So you can manually force what you need for testing.

On the Nagios host, the nsca.cfg file has a decryption method that needs to match the nsclient.ini
I found that in nsca.cfg

Code: Select all

#       14 = RIJNDAEL-128   #   AES 128
decryption_method=14

matches up with nsclient.ini

Code: Select all

[/settings/NSCA/client/targets/default]
encryption = aes

That is the iterative process I went through to figure out my setup.
In the nscp test window - type exit, edit the nsclient.ini file to make a change, restart the nscp test, and try another echo... until it plays nice. Rinse and repeat.

Good luck - I am sure you will succeed.

Steve B

[jkinning]

Does that require any changes to my current checks that are using check_nrpe?

I have never done anything with NSCA but I am guessing the information you have provided is just adding some information to the nsclient.ini file on the Windows server? Also wondering if I can have multiple critical = entries for the Warn, Error, or Fatal entries in that column.

[avandemore]

The closest thing I can think of is this plugin:

https://labs.consol.de/nagios/check_logfiles/

Or you may need to write your own plugin. Here is some info for writing plugins:

https://nagios-plugins.org/doc/guidelines.html
https://mathias-kettner.de/checkmk_localchecks.html
http://www.yourownlinux.com/2014/06/how ... cript.html

[SteveBeauchemin]

The data I provided was the first time I needed to use NSCA. It was a small learning curve to get it right, but I think I provided all the hard stuff in the previous post.

Does that require any changes to my current checks that are using check_nrpe?

This does not negate any previous setups, it just adds to it.

Also wondering if I can have multiple critical = entries for the Warn, Error, or Fatal entries in that column.

Use the echo statement and play.

You can define more complicated scenarios than what I showed. Finding existing information on how to do it 'Exactly' was a challenge. I had to just bite the bullet and use my imagination. I also looked at other folks work that they graciously publish for others to learn from. Remember, Google is your friend.

And so is Willem! -> https://outsideit.net/

Try things out until it does what you need. I call it play, some call it work.

Steve B

[avandemore]

I will also point out Nagios Log Server has is functionality built in and is free for use w/ under 500MB of logs per day.

[jkinning]

I will also point out Nagios Log Server has is functionality built in and is free for use w/ under 500MB of logs per day.

I might need to take a look at that option. I'll have to dive into the documentation but does Log Server tie into XI? Just a quick glance it looks like a separate stand alone elasticsearch product.

[avandemore]

It is a standalone product that requires a completely separate install. However it does tie into Nagios XI. Here is some documentation on that:

https://assets.nagios.com/downloads/nag ... Server.pdf