Search Exchange

Search All Sites

Nagios Live Webinars

Let our experts show you how Nagios can help your organization.

Contact Us

Phone: 1-888-NAGIOS-1
Email: sales@nagios.com

Login

Remember Me

check_db

Rating
3 votes
Favoured:
0
Hits
105643
Files:
FileDescription
check_dbShell Script wrapper
DbCheck.classCompiled DbCheck Class
DbCheck.javaDbCheck java source code
JSAP-2.0a.jarJSAP 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)
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.
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
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()'