Search Exchange
Search All Sites
Nagios Live Webinars
Let our experts show you how Nagios can help your organization.Login
Directory Tree
check_db
113609
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 |
Meet The New Nagios Core Services Platform
Built on over 25 years of monitoring experience, the Nagios Core Services Platform provides insightful monitoring dashboards, time-saving monitoring wizards, and unmatched ease of use. Use it for free indefinitely.
Monitoring Made Magically Better
- Nagios Core on Overdrive
- Powerful Monitoring Dashboards
- Time-Saving Configuration Wizards
- Open Source Powered Monitoring On Steroids
- And So Much More!
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
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)
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.
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.
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
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()'
[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()'