Oracle DB Query check

[Russell Scibetti]

I received a few requests about this, so I'm submitting it to the list. 
 I don't know if this belongs in the contrib section of the 
nagios-plugins, because it doesn't exactly fit the form.  If I improve 
it significantly, then I'll formally resubmit it.

Anyway, this requires that Java JDK1.3.1 or higher is installed on the 
monitoring machine, but you don't need to install the Oracle client. 
 You only need the Oracle JDBC libraries, found in the form of 
classes12.zip or classes12.jar.  My code uses the Java Thin Client to 
connect to the DB.  

You first need to compile the NagiosDBTest.java file to get 
NagiosDBTest.class.  Then, in the shell script, you'll see that I set 
the $CLASSPATH to wherever the classes12 file is.  My script then takes 
4 args:  host, sid, username, and password.  

The args that actually get passed to the java program are (in order), 
the connection string (which contains the host and sid), username, 
password, and query string (in quotes).  You can vary the shell script 
as you like to run different queries or take different args, as long as 
these 4 args get passed to the java program (or edit the .java file to 
set up your own defaults).

The java code simply makes a connection to the Oracle DB and runs the 
query.  If a SQLException occurs, or the resultset of the query is 
empty, the java code returns a critical, and either the Exception 
message or "Empty Set" will be returned as output.  If the query returns 
a resultset that has content, the java code returns an OK and outputs 
the result of the query.  If you write smarter shell scripts, you can 
use the resultset output to do more calculations (check # of 
connections, etc).

This code should work with non-Oracle databases are long as you have the 
proper JDBC libraries in the $CLASSPATH and use the correct connection 
string.

Please feel free to email if you have any questions.

Russell Scibetti

Carroll, Jim P [Contractor] wrote:

>Perhaps something which could be added to the contrib directory...?
>
>jc
>
>>-----Original Message-----
>>From: Russell Scibetti [mailto:russell at quadrix.com]
>>Sent: Monday, February 10, 2003 1:11 PM
>>To: nagios-users at lists.sourceforge.net
>>Subject: [Nagios-users] Oracle DB Query check
>>
>>
>>I have created a "plugin" to check connecting and querying an 
>>Oracle DB. 
>> However, instead of needing the Oracle client installed on your 
>>monitoring machine, I use java (JDK1.3.1 and up) and a 
>>Jar/Zip file of 
>>the Oracle JDBC libraries (classes12.zip for those that know 
>>it).  It is 
>>a small java class that connects, runs a query and exits, and you can 
>>wrap it with any number of different shell scripts for 
>>running different 
>>queries and processing the results.  
>>
>>The class takes a connection string, user, password, and 
>>query to run. 
>> It returns OK if the query executes and returns any 
>>resultset, which is 
>>printed to the OUTPUT string.  It returns a Critical if a 
>>SQLException 
>>occurs or the resultset of the query is empty.  The 
>>SQLException message 
>>is printed as the OUTPUT if one occurs.
>>
>>Is anyone interested in this code?  If so, I will email it out to the 
>>list.  I was just trying to avoid installing the Oracle 
>>client where I 
>>didn't need it, and take advantage of an already existing 
>>Java installation.
>>
>>Russell Scibetti
>>
>>-- 
>>Russell Scibetti
>>Quadrix Solutions, Inc.
>>http://www.quadrix.com
>>(732) 235-2335, ext. 7038
>>
>>
>>
>>
>>-------------------------------------------------------
>>This SF.NET email is sponsored by:
>>SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
>>http://www.vasoftware.com
>>_______________________________________________
>>Nagios-users mailing list
>>Nagios-users at lists.sourceforge.net
>>https://lists.sourceforge.net/lists/listinfo/nagios-users
>>
>
>
>-------------------------------------------------------
>This SF.NET email is sponsored by:
>SourceForge Enterprise Edition + IBM + LinuxWorld = Something 2 See!
>http://www.vasoftware.com
>_______________________________________________
>Nagios-users mailing list
>Nagios-users at lists.sourceforge.net
>https://lists.sourceforge.net/lists/listinfo/nagios-users
>
>

-- 
Russell Scibetti
Quadrix Solutions, Inc.
http://www.quadrix.com
(732) 235-2335, ext. 7038


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20030211/2d6f270d/attachment.html>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: NagiosDBTest.java
URL: <https://www.monitoring-lists.org/archive/users/attachments/20030211/2d6f270d/attachment.ksh>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: check_db_withjava.sh
URL: <https://www.monitoring-lists.org/archive/users/attachments/20030211/2d6f270d/attachment-0001.ksh>