Oracle query Wizard issue

[RIDS_I2MP]

Hello Team,

We have to monitor one SQL query from Nagios XI. We are trying to monitor it using "Oracle Query Wizard". But getting some error related to bash while executing it.

I am attaching the Oracle query and database details here, please have a look at it.

IP Address: 10.144.223.197
PORT: 1521
SID: DRHPRODDB
Username: Nagios
Password: *******
Database: drhprodc

Error that we are getting:

(No output on stdout) stderr: /bin/sh: -c: line 0: unexpected EOF while looking for matching `"'

Thanks for your help in advance!!

[tgriep]

That plugin requires the Oracle Instant Client installed in the Nagios sevrer as well as the commands changed to use that client.
If you did not do that, follow these instructions and test the plugin again.
https://assets.nagios.com/downloads/nag ... lation.pdf

If you did what version of the Client did you install?
Can you show us how the service is configured by going to the Core Config Manager > Service menu and find the service.
Click on the View Config icon, copy the settings and paste it here.

[RIDS_I2MP]

Hello,

Thanks for your response!!

I am not sure if Oracle Instant Client is already installed on Nagios XI server or not as I am new to this work and Nagios XI was handled by someone else before me. I have gone through the document provided by you and I could find few things as mentioned below:

1. When I checked in Nagios XI server and I was able to find the below path:

[nagios@eu1papu003 lib]$ pwd
/usr/lib/oracle/12.1/client64/lib
[nagios@eu1papu003 lib]$ ls -lrt
total 212876
-rw-r--r--. 1 root root 136162781 May 24 2013 libociei.so
-rw-r--r--. 1 root root 114225 May 24 2013 liboramysql12.so
-rw-r--r--. 1 root root 330074 May 24 2013 libons.so
-rw-r--r--. 1 root root 6277261 May 24 2013 libnnz12.so
-rw-r--r--. 1 root root 152544 May 24 2013 libocijdbc12.so
-rw-r--r--. 1 root root 2523156 May 24 2013 libocci.so.12.1
-rw-r--r--. 1 root root 55453863 May 24 2013 libclntsh.so.12.1
-rw-r--r--. 1 root root 6750135 May 24 2013 libclntshcore.so.12.1
-rw-r--r--. 1 root root 1535672 May 24 2013 libsqlplus.so
-rw-r--r--. 1 root root 1561402 May 24 2013 libsqlplusic.so
-rw-r--r--. 1 root root 342 May 24 2013 glogin.sql
-rw-r--r--. 1 root root 311339 May 24 2013 ottclasses.zip
-rw-r--r--. 1 root root 3370513 May 24 2013 ojdbc6.jar
-rw-r--r--. 1 root root 3378365 May 24 2013 ojdbc7.jar
-rw-r--r--. 1 root root 35927 May 24 2013 xstreams.jar
lrwxrwxrwx. 1 root root 17 Sep 26 2014 libclntsh.so -> libclntsh.so.12.1
lrwxrwxrwx. 1 root root 21 Sep 26 2014 libclntshcore.so -> libclntshcore.so.12.1
lrwxrwxrwx. 1 root root 15 Sep 26 2014 libocci.so -> libocci.so.12.1
[nagios@eu1papu003 lib]$
[nagios@eu1papu003 lib]$ pwd
/usr/lib/oracle/12.1/client64/lib


2. When I checked in Nagios XI GUI, I could see below three in Commands section:

check_xi_oraclequery
check_xi_oracleserverspace
check_xi_oracletablespace

I am attaching the service definition that I have used and above commands definition as well.

Please check and let me know if I need to install the Oracle client on my Nagios XI server.

[tgriep]

The client looks like it is installed on the server and the commands look OK.

The plugin can only handle simple queries that return an integer. Does your query do that?

Another thing, your query is so large, it is probably getting cut off when it is stored in the database / files or it it not getting escaped correctly.
Try this, rerun the database and put single quotes around the query and see if that works as escaping the argument.

To see if it is cut off, open the /usr/local/nagios/var/objects.cache file and search for that service and see if the full argument is specified.
If not, post what is there so we can view it.

[RIDS_I2MP]

Hello,

The query will return few integer values, and then it will compare those values and give the output as Red, Amber, GREEN.

I checked the objects.cache file and could find a basic difference which I am attaching here.

Please check it and let me know if we can check the same from database. Do we need to use a different plugin?

[tgriep]

One thing, when you run the Oracle Query Wizard, it will escape the special characters in the data and then save the escaped command on the system.
So you have to be careful when editing the command as the escaping needs to be preserved.
One thing I see is the semi-colon at the end of the query is missing from your example and that is a special character that is escaped like this

Code: Select all

%3B

So at the end of the query, change it to the following

Code: Select all

order by snap_time desc%3B"

Another character that needs to be escaped is the $ and that is used in the query.
Find this in the query

Code: Select all

v$database

And change it to

Code: Select all

v$"$"database

Save the changes and see if the plugin works.
Those changes may fix the query which could of effected the login and created the connection issue.

Also, verify that the account information, the port and the oracle service name is correct.
And that the permissions are setup on the Oracle server for that account have enough rights to run the query.