CheckByJdbc

Project Notes

Check By Jdbc Nagios Plugin - version 1.0.c - READ-ME

1. Introduction

1.1. What is CheckByJdbc Nagios Plugin?

Nagios CheckByJdbc plugin allows you to check a SQL query from a file on database ( Oracle, Microsoft SqlServer, Mysql and
IBM DB2/400) by a JDBC connection. For CheckByJdbc plugin it doesn't mind what
database are you connecting and what your SQL query does, unless it adtopt the
following convention:

* First column in result set must be number datatype following Nagios
STATUS convention:
- 0 : "OK" status
- 1 : "WARN" status
- 2 : "CRITICAL" status
- others: "UNKNOW" status
* Second column in result will be concatenated to Nagios STATUS string,
separated by commas ( <r1c2>; <r2c2>; ...; <rnc2> )
* You can result more than one line. If your query reults more then one
STATUS then plugin return order is "CRITICAL", "WARN", "UNKNOW", "OK"
* Do *not* turn verbose level > 0 running in production unless you don't
mind for your STATUS message string
* You can use built in variables in your sqlfiles:
- '&w': Warning command line parameter
- '&c': Critical command line parameter

1.2. For developers that want to study and customize

If you want to compile or adjuste source code, you must:
* download CheckByJdbc.zip plugin from repository
* download JSAP-2.0a.jar and unzip it into directory
* download jdbc drivers and leave it into CheckByJdbc directory. You
can find up-to-date ones searching in Internet:
- Oracle: classes12.jar
- MySql: mysql-connector-java-5.1.18-bin
- MSSqlServer:
o sqljdbc4.jar (Windows)
o sqljdbc_4.0.2206.100_enu.tar (Linux)
- IBM DB2/400:
o jt400.jar ( http://jt400.sourceforge.net/ )
* file 'makefile.bat' ( 'makefile.sh' for Linux) helps you to compile
source code
* all source code is in file 'CheckByJdbc.java'
* I've studied two others plugins available in Nagios Exchange:
- check_db
- check_tablespace_oracle
These were two good plugins, but I'd like something that I could put
my own custom SQL Query, and evaluate result as Nagios Status (OK,
WARNING, CRITICAL, UNKNOWN). Some plugin compatible to any besides of
Oracle. Some plugin that can connect by JDBC to any database to
execute some query that can be customized in command line parameter.
* Evolution proposed:
- PL/SQL stored procedure check
* I don't think it could help, I did everything I need using SELECT
- Return adequated format performance data
* Sébastien Guerin colaborated with this develpment and launched
release 1.0.c

1.3. For pragmatic users that just want to download and use

* download CheckByJdbc.zip plugin from repository
* download JSAP-2.0a.jar and leave it into CheckByJdbc directory
* download jdbc drivers and leave it into CheckByJdbc directory. You
can find up-to-date ones searching in Internet:
- Oracle:
* classes12.jar
- MySql:
* mysql-connector-java-5.1.18-bin
- Microsoft SqlServer:
* sqljdbc4.jar (Windows)
* sqljdbc_4.0.2206.100_enu.tar (Linux)
- IBM DB2/400:
o jt400.jar ( http://jt400.sourceforge.net/ )

2. Installing and deploying CheckByJdbc plugin

2.1. Install Samples of Use
- Execute all 'deploy*.sql' script into specific database
* for Oracle Database:
- deploy_orcl_*.sql
* for Microsoft SQLServer:
- deploy_mssql*.sql
* for MySQL:
- deploy_mysql*.sql
* for IBM DB2/400:
- deploy_db2*.sql (waiting for contributions)
- View and execute each line of 'Samples.bat' ( 'Samples.sh' for Linux )
manualy and observe result

2.2. Install CheckByJdbc Nagios plugin into your Nagios environment
a) Set Nagios Home environment
$ export NAGIOS_HOME='/usr/local/nagios'
b) Unzip CheckByJdbc.zip into '../libexec' subdirectory
$ cd $NAGIOS_HOME/libexec
$ unzip CheckByJdbc
c) Change owner and permission
$ cd $NAGIOS_HOME/libexec
$ chown -R nagios:nagios ./CheckByJdbc/
$ chmod 777 ./CheckByJdbc/makefile.sh
$ chmod 777 ./CheckByJdbc/Samples.sh
$ chmod 777 ./CheckByJdbc/*.jar
d) Configure a command for CheckByJdbc plugin
$ cd $NAGIOS_HOME/etc/objects
$ vim commands.cfg
+-----------------------------------------------------------------------------+
| : |
| : |
# 'check_by_jdbc' command definition
define command{
command_name check_by_jdbc
command_line java -cp $USER1$/CheckByJdbc/.:$USER1$/CheckByJdbc/$ARG1$ CheckByJdbc -d $ARG2$ -H $ARG3$ -p $ARG4$ -s $ARG5$ -l $ARG6$ -x $ARG7$ -m $ARG8$ -f $USER1$/CheckByJdbc/$ARG9$ -R "$ARG10$" -w $ARG11$ -c $ARG12$ -M "$ARG13$" -v $ARG14$
}
| : |
| : |
+-----------------------------------------------------------------------------+

- Configure a service to check with CheckByJdbc plugin
$ cd $NAGIOS_HOME/etc/objects
$ vim localhost.cfg
+-----------------------------------------------------------------------------+
| : |
| : |
# define a service to be checked by CheckByJdbc
define service{
use local-service
host_name vmwsrv01
service_description CheckByJdbc Oracle Tablespaces
check_command check_by_jdbc!classes12.jar!orcl!192.168.133.2!1521!orcl!nagios!nagios!f!sqlfile1.sql!!80!90!CheckByJdbcTablespaces!0
check_interval 1
retry_interval 1
}
define service{
use local-service
host_name vmwsrv01
service_description CheckByJdbc Oracle Invalid Objects
check_command check_by_jdbc!classes12.jar!orcl!192.168.133.2!1521!orcl!nagios!nagios!f!sqlfile2.sql!&OWNERLIST='MANAGER','RM'!0!0!CheckByJdbc Invalid Objects!0
check_interval 1
retry_interval 1
}
define service{
use local-service
host_name vmwsrv01
service_description CheckByJdbc Oracle LongTimeUserQuery
check_command check_by_jdbc!classes12.jar!orcl!192.168.133.2!1521!orcl!nagios!nagios!f!sqlfile3.sql!!300!900!CheckByJdbc OrclLongTimeUserQuery!0
check_interval 15
retry_interval 15
}
| : |
| : |
+-----------------------------------------------------------------------------+

- Test CheckByJdbc plugin outside nagios
$ export USER1='/usr/local/nagios/libexec'
$ export ARG1='classes12.jar'
$ export ARG2='orcl'
$ export ARG3='192.168.133.2'
$ export ARG4='1521'
$ vexport ARG5='orcl'
$ export ARG6='nagios'
$ export ARG7='nagios'
$ export ARG8='f'
$ export ARG9='sqlfile1.sql'
$ export ARG10=''
$ export ARG11='80'
$ export ARG12='90'
$ export ARG13='CheckByJdbcTablespaces'
$ export ARG14='3'
$
$ java -cp $USER1/CheckByJdbc/.:$USER1/CheckByJdbc/$ARG1 CheckByJdbc -d $ARG2 -H $ARG3 -p $ARG4 -s $ARG5 -l $ARG6 -x $ARG7 -m $ARG8 -f $USER1/CheckByJdbc/$ARG9 -R "$ARG10" -w $ARG11 -c $ARG12 -M "$ARG13" -v $ARG14
+----------------------------------------------------------------------------------------------+
| CRITICAL - CheckByJdbcTablespaces: [Tbs: MANAGER, Max: 464mb, Used: 463.375mb, %Used: 100% ] |
+----------------------------------------------------------------------------------------------+

A. Apendix

A.1. References
- Download from:
* http://