check_db

Submit review Recommend Print

Rating

3 votes

Favoured:

Owner

nagiosexchange

Hits

112998

Files:

File	Description
check_db	Shell Script wrapper
DbCheck.class	Compiled DbCheck Class
DbCheck.java	DbCheck java source code
JSAP-2.0a.jar	JSAP java command line parser

Network Monitoring Software - Download Nagios XI

Log Management Software - Nagios Log Server - Download

Netflow Analysis Software - Nagios Network Analyzer - Download

Remote oracle database check using JDBC drivers. Supports custom SQL queries and regular expression match. Provides similar funcationality as sitescope Db monitor.

This plugin can check almost every aspect of oracle database, written in java for portability.

Compiled with JDK 1.5.0_06

Uses JSAP command line parser http://www.martiansoftware.com/jsap/

to compile : copy DbCheck.java JSAP-2.0a.jar in a directory, expand JSAP-2.0a.jar in the same directory using command jar xvf JSAP-2.0a.jar

Use : javac -cp .:com/martiansoftware/jsap/JSAP.* DbCheck.java

this should generate DbCheck.class file

Setup Nagios Environment:

Copy DbCheck.class, check_db to /usr/local/nagios/libexec
create /usr/local/nagios/libexec/lib directory
Copy JSAP-2.0a.jar, classes12.jar (Take from $ORACLE_HOME/jdbc/lib), sql files to /usr/local/nagios/libexec/lib directory

Nagios plugin to check output of a sql query, matched with a regular expression. Currently supports numeric data checks only.
--help

Print help message

-H

Hostname/ip address of database server.

-p

Listener port, default is 1521. (default: 1521)

-s

Oracle database SID.

-l

Oracle database user to connect.

-x

Oracle database user password.

-f

Full path of sql file to be executed.

-r

PERL style Regular Expression to match in SQL output. Check value must
be enclosed between (), should match only one field and must be enclosed
between single quotes. Example 'SYSTEM.*,.*,(.*)'

-w

Warning value for matched expression. Normally warning < critical value.
If your check want to alert when result < warning , set critical less
than warning

-c

Critical value for matched expression. Should be more than Warning

1. If not check behaviour is reversed, see warning help text above.

-L

Label for matched regex value i.e FieldName.

Usage: DbCheck --help -H -p -s -l -x -f -r -w -c -L

Reviews (3)

Need more documentation

bycipher, May 30, 2012

i need to check the sessions used in a oracle DB and alert if it exceeds. i have compiled your plugin successfully.

what i want i am listing below can you tell me how i cud do the follow: with your plugin if possible

My Oracle DB server details

operating System: Solaris
database: oracle
IP: 192.168.1.2 (not real ip)

My Nagios Server details
OS : Ubuntu
i have configured nagios 3.x

i connect to my db server from my nagios as follow:

ssh -Y root@192.168.1.2
Password: xxxxxxxxx
[root@vboxdb /]# su - oracle

Sun Microsystems Inc. SunOS 5.10 Generic January 2005
[oracle@vboxdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 30 04:53:48 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

what i want to do i will list below ;

SQL> select current_utilization, max_utilization, limit_value from v$resource_limit where resource_name =’sessions’;

CURRENT_UTILIZATION MAX_UTILIZATION LIMIT_VALUE
——————- ————— ————————- —————

386 404 1528

if the current utilization reaches 1500 alert critical, 1450 warning state. else normal.
can you tell me how i can use your plugin to do this.

Works with many changes, but no documentation

byHoward, March 27, 2012

Unlike the other reviewer (who apparently didn't read what little documentation there is since he/she tried to run a .java file at the command line which is nowhere suggested in the notes), I got this to work (kinda).

In later relases, you normally connect to an Oracle environment with a service_name, not a SID, so the old host:port:sid format no longer works. You need to pass host:port/service_name. I changed the .java file from the : to a /, recompiled, and I could conenct. However, the JSA file downloaded is a zip, not a jar, so that doesn't work, either. Easily fixed, but confusing if you aren't aware.

I also still don't know what the regular expression is supposed to do.

Also, the .sql file you run shouldn't be terminated with a semicolon, as you may be used to in oracle. The string is executed by JDBC dynamically, so you just pass "select blah from table", not "select blah from table;"

Lastly, the plugin must be protected (they all shoudl be, admittedly), as the SQL is just run "as is". At least it requires a query, so you can't accidentally run "truncate table big_table" :)

It just needs about two hours of work to take it over the goal line.

Below is where I ended up after all the changes I mentioned above...

[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r % -w 1 -c 2 -L "who knows"
java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:546)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:236)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)
at java.sql.DriverManager.getConnection(DriverManager.java:620)
at java.sql.DriverManager.getConnection(DriverManager.java:200)
at DbCheck.testSS(DbCheck.java:156)
at DbCheck.main(DbCheck.java:140)
Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)
at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)
... 8 more
[root@ADM-SHOWARD ~]# vi DbCheck.java
[root@ADM-SHOWARD ~]# javac DbCheck.java
[root@ADM-SHOWARD ~]# export CLASSPATH=/root/ojdbc6.jar:/root/JSAP-2.0a.zip:.
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r % -w 1 -c 2 -L "who knows"
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at DbCheck.testSS(DbCheck.java:158)
at DbCheck.main(DbCheck.java:140)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '(%)' -w 1 -c 2 -L "who knows"
java.sql.SQLSyntaxErrorException: ORA-00911: invalid character

at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406)
at DbCheck.testSS(DbCheck.java:158)
at DbCheck.main(DbCheck.java:140)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '(%)' -w 1 -c 2 -L "who knows"
No match found for regular expression:(%)
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN%' -w 1 -c 2 -L "STATUS"
OPEN
No match found for regular expression:OPEN%
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
OPEN
Unsupported data type detected..
[root@ADM-SHOWARD ~]# vi DbCheck.java
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
5
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# vi mysql.sql
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r 'OPEN' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:OPEN
[root@ADM-SHOWARD ~]# ./check_db -H 172.26.24.6 -p 1521 -s "ecomonline.expdev.local" -l system -x express -f mysql.sql -r '5' -w 1 -c 2 -L "STATUS"
0
No match found for regular expression:5
[root@ADM-SHOWARD ~]#

mysql.sql file contents are below:

select case status when 'OPEN' then 0 when 'MOUNTED' then 1 when 'NOT MOUNTED' then 2 end case from v$instance

didn't work

byMajed, July 22, 2011

first i had much trouble getting the jar command to work but after it did i did everything instructed, not sure about the sql files though. i cpied just one file with the .sql extension. anyway on redhat from command prompt here is the output:
[root@orcl libexec]# ./DbCheck.java -H 10.1.1.247 -s orc1
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
import: unable to open X server `'.
./DbCheck.java: line 12: public: command not found
./DbCheck.java: line 15: syntax error near unexpected token `('
./DbCheck.java: line 15: ` public DbCheck()'

Nagios, the Nagios logo, and Nagios graphics are the servicemarks, trademarks, or registered trademarks owned by Nagios Enterprises. All other servicemarks and trademarks are the property of their respective owner. The files and information on this site are the property of their respective owner(s). Nagios Enterprises makes no claims or warranties as to the fitness of any file or information on this website, for any purpose whatsoever. In fact, we officially disclaim all liability. We do, however, think these community contributions are pretty damn cool. Website Copyright © 2009-2024 Nagios Enterprises, LLC. All rights reserved.