[Nagiosplug-help] check_db oracle check plugin

[Tim Dunphy]

hello, 
 
 I am attempting to using one of many checks that are designed to check oracle db. I chose this one because it seemed pretty flexible as you are able to feed some SQL statements into it with a flag.

 Here's the page for the plugin I am trying to use:

 http://exchange.nagios.org/directory/Plugins/Databases/Oracle/check_db/details


 I was able to get the check to work in 'usage' mode where it prints out it's help page. But when I actually go to run the check this is the error that I see:

 
[root at db07-dc2:/opt/nagios/libexec] $./check_db -H qaecom1-db-lh -p 1521 -s qaecom1 -l user -x secret -f /u01/app/oracle/sql/test.sql -r 'test' -w 15 -c 25 -L THRESHOLD
java.sql.SQLException: ORA-00933: SQL command not properly ended
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
        at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:850)
        at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2599)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2963)
        at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:653)
        at DbCheck.testSS(DbCheck.java:158)
        at DbCheck.main(DbCheck.java:140)


The error appears to be that the SQL I am giving it is not terminated correctly. Here's the contents of that SQL file I'm giving it:


root at db07-dc2:/opt/nagios/libexec] $cat /u01/app/oracle/sql/test.sql
select DISTINCT
USERNAME||'|'||
STATUS||'|'||
OSUSER||'|'||
MACHINE||'|'||
PROGRAM||'|'||
SQL_HASH_VALUE||'|'||
to_char(LOGON_TIME,'DD-MON-YYYY HH24:MI:SS')||'|'||
substr(sql_text,1,128)||'|'
from v$session a,v$sql b
where status ='ACTIVE'
and username is not null
and a.sql_hash_value = b.hash_value(+)
and
(select nvl((
select count(1) from v$session  where status ='ACTIVE'
and username is not null
group by status
having count(1) > 0),0) from dual) > 25      /* threshold count goes here */
order by 1



I guess what's most confusing about this is that if I go into sqlplus and open the file it does return a result:


SQL> @sql/test.sql
 21 

This shows the number of connections to the database.


I'd appreciate any advice you might have on how to get this check beyond this point.

thanks
tim