help - using the Oracle_Query

[yescobar01]

Hello All,

I am still learning to use Nagios. I been doing some research for using the Oracle_Query, but haven't come accross any good references. So i want to know if there are any tutorials or docs on using the Oracle_Query like adding arguments in the paramether $ARG(#)$. If I want to use only one database and having many different querys. Do I need to go to Configure tab > Monitor Wizards > Oracle Query every time and recconect using the same database with different query ? Also, what are some good queries that we want to use to monitor our database?

[slansing]

Besides our document on getting started with the oracle plugins/wizards we do not have additional documentation on the above since they are highly environment specific options that should be discussed with your DBA if possible. If you have some things in mind we can help you set up the checks but you will need to choose what you would like to monitor first.

[yescobar01]

I am writing a simple Oracle_Query but I get an error. Does Nagios support the special characters? I would think it should.

Code: Select all

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select * FROM dim_amundment" --warning 50 --critical 200

Here is the error i get:
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select \* FROM dim_amundment" --warning 50 --critical 200
OUTPUT: UNKNOWN - got no valid response for select \* FROM dim_amundment - ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 7 in 'select <*>\* FROM dim_amundment')

I know this query works because I also try this below and it worked.

Code: Select all

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select 5 from dual" --warning 50 --critical 200

output:
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select 5 from dual" --warning 50 --critical 200
OUTPUT: OK - select 5 from dual: 5 | 'select'=5;50;200

[abrist]

It should. Try using single quotes ( ' ) instead.

[newuser1]

single ' around the the star '*' ?

[yescobar01]

I added the single quotes around the star

I received this error

Code: Select all

UNKNOWN - got no valid response for select /* from dim_amendment - ORA-01742: comment not terminated properly (DBD ERROR: error possibly near <*> indicator at char 7 in &apos;select <*>/* from dim_amendment&apos;)

Any other suggesting with the escape special characteristic would be greatly appreciated !

[slansing]

Are you able to select a integer instead of using a wildcard? Lets test to make sure we can connect to the table to verify that the * is causing issues, which it does seem to be. Try the following:

Code: Select all

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select 1 FROM dim_amundment" --warning 50 --critical 200

Of course be sure to place you true IP in place of the obfuscated one. let us know what you find. Since it is friday we may not be able to answer until next monday, in which case try the following combinations for the original check:

Code: Select all

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select \* FROM dim_amundment" --warning 50 --critical 200

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select \*\ FROM dim_amundment" --warning 50 --critical 200

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select '*' FROM dim_amundment" --warning 50 --critical 200

-connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select '\*' FROM dim_amundment" --warning 50 --critical 200

Be sure to test that connect string from the command line and not the built in XI test function, some special characters will not work from there. Hopefully you can get back to us before the end of today.

[yescobar01]

I try the commands you suggested i also in the command line, I was not able to enter an integer instead of a wildcard

Code: Select all

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select 1 from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select 1 from dim_amundment - ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 14 in 'select 1 from <*>dim_amundment')

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select \* from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select \* from dim_amundment - ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 7 in 'select <*>\* from dim_amundment')

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select \*\ from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select \*\ from dim_amundment - ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 7 in 'select <*>\*\ from dim_amundment')


[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select '*' from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select '*' from dim_amundment - ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 16 in 'select '*' from <*>dim_amundment')


[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr--password "pwd" --mode sql --name="select '\*' from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select '\*' from dim_amundment - ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 17 in 'select '\*' from <*>dim_amundment')

I also try to use one of the columns in the table and it complain i think i also need an escape for the underscore '_' in the table name

Code: Select all

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select amendment_key from dim_amundment" --warning 50 --critical 200
UNKNOWN - got no valid response for select amendment_key from dim_amundment - ORA-00942: table or view does not exist (DBD ERROR: error possibly near <*> indicator at char 26 in 'select amendment_key from <*>dim_amundment')

If I try a test table i created in the database that has no special characters it works

Code: Select all

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username usr --password "pwd" --mode sql --name="select d,v from t2" --warning 50 --critical 200
OK - select d,v from t2: 1 0 | 'select'=1;50;200 'd,v'=0;;
[root@oravm2 libexec]#

I try this one with no special characters only the * in the select statement

Code: Select all

[root@oravm2 libexec]# ./check_oracle_health -connect 64.143.xxx.xxx:1522/cdtbd2 --username CDT_DBA --password "E3wcSnP4" --mode sql --name="select '\*' from t2" --warning 50 --critical 200
UNKNOWN - got no valid response for select '\*' from t2

[slansing]

We are doing some digging on this, we will get back to you ASAP on our findings, thanks for your patience!

[asytechacd]

Hello all,

Did anyone manage to get any workaround solution on the subject ?

I did the same tests as shown in the history and I get the same test results.


CASE 1: MY ORIGINAL SELECT STATEMENT
-------------------------------------------------
-connect 10.10.9.8:1521/livedb --username system --password "manager" --mode sql --name="select count(applied) from v\$archived_log where applied="NO"" --warning 50 --critical 200


RESULT: - (NOT WORKING)
---------
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health -connect 10.10.9.8:1521/livedb --username system --password "manager" --mode sql --name="select count\(applied\) from v\\\$archived_log where applied="NO"" --warning 50 --critical 200
OUTPUT: UNKNOWN - got no valid response for select count\(applied\) from v\$archived_log where applied=NO - ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 12 in 'select count<*>\(applied\) from v\$archived_log where applied=NO')



CASE 2: TEST SETTING ON DUAL
--------------------------------------
--connect 10.10.9.8:1521/livedb --username system --password "manager" --mode sql --name="select 3 from dual" --warning 50 --critical 200


RESULT: - (WORKING)
---------
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 10.10.9.8:1521/asywdb --username system --password "manager" --mode sql --name="select 3 from dual" --warning 50 --critical 200
OUTPUT: OK - select 3 from dual: 3 | 'select'=3;50;200




CASE 3: TEST SETTING ON TEST TABLE WITH NUMBER COLUMN
--------------------------------------------------------------------------

TABLE
--------
SQL> DESC TEST3
Name Null? Type
----------------------------------------- -------- ----------------------------
SALARY NUMBER(10)



(SELECTING THE COLUMN SALARY ITSELF)

--connect 10.10.9.8:1521/testdb --username test --password "test" --mode sql --name="select salary from test3" --warning 50 --critical 200


RESULT : - (WORKING)
---------
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 10.10.9.8:1521/testdb --username test --password "test" --mode sql --name="select salary from test3" --warning 50 --critical 200
OUTPUT: OK - select salary from test3: 1 | 'select'=1;50;200



(SELECTING THE COLUMN SALARY WITH THE USE OF COUNT(*) FUNCTION)

--connect 10.10.9.8:1521/testdb --username test --password "test" --mode sql --name="select count(*) from test3" --warning 50 --critical 200


RESULT: - (NOT WORKING)
---------
COMMAND: /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 /usr/local/nagios/libexec/check_oracle_health --connect 10.10.9.8:1521/testdb --username test --password "test" --mode sql --name="select count\(\*\) from test3" --warning 50 --critical 200
OUTPUT: UNKNOWN - got no valid response for select count\(\*\) from test3 - ORA-00911: invalid character (DBD ERROR: error possibly near <*> indicator at char 12 in 'select count<*>\(\*\) from test3')


--END OF TEST ---


It appears to me that the error has something to do with the Oracle Aggregate Function COUNT(). As shown above, selecting purely the number column from the test3 table OR selecting a number from dual does not have any problem BUT when the COUNT() function is applied then we get the error. Nagios' condition on the Oracle Query is that it should return a number and I cannot understand why the COUNT() wouldn't work.

Is there any development/solution/workarounds on the subject ?

Your response will be highly appreciated !

Geoffrey
(asytechacd)