Sqlplus and Web-GUI

This forum is intended for the discussion of Nagios plugin development. Feature requests, patches, bug fixes, and all types of development-related discussions are welcome!

NOTE: The SourceForge.net nagiosplug-devel mailing list has been deprecated in favor of this forum in order to expedite support and provide additional features not available on the old mailing list.

Sqlplus and Web-GUI

Postby whitest » Tue Jul 10, 2018 9:01 am

Hi everyone!
Problem: Nagios web-gui don't see plugin output from sqlplus, but everything works fine in command line.

I try to use plugin check_sql_query (https://exchange.nagios.org/directory/Plugins/Databases/Oracle/check_sql_query/details) to check blocking SQL sessions in my Oracle 11gR2 database. I modified the plugin to handle sql result (OK,CRITICAL,UNKNOWN).
Code:
Code: Select all
#! /bin/ksh
# check_sql_query
# nagios plugin to execute a specific sql query
# author: Sergei Haramundanis 08-Aug-2006
#
# usage: check_sql_query access_file query_file

if [ "${1}" = "" -o "${1}" = "--help" ]; then
    echo "check_sql_query 1.0"
    echo ""
    echo "nagios plugin to execute a specific sql query"
    echo ""
    echo "This nagios plugin comes with ABSOLUTELY NO WARRANTY."
    echo "You may redistribute copies of this plugin under the terms of the GNU General Public License"
    echo "as long as the original author, edit history and description information remain in place."
    echo ""
    echo "usage: check_sql_query access_file query_file"
    echo "usage: check_sql_query --help"
    echo "usage: check_sql_query --version"
    exit ${STATE_OK}
fi

if [ ${1} == "--version" ]; then
    echo "check_sql_query 1.0"
    echo "This nagios plugin comes with ABSOLUTELY NO WARRANTY."
    echo "You may redistribute copies of this plugin under the terms of the GNU General Public License"
    echo "as long as the original author, edit history and description information remain in place."
    exit ${STATE_OK}
fi

if [ $# -lt 2 ]; then
    echo "[CRITICAL] insufficient arguments"
    exit ${STATE_CRITICAL}
fi

ACCESS_FILE=${1}
QUERY_FILE=${2}

#echo "ACCESS_FILE=\"${ACCESS_FILE}\""
#echo "QUERY_FILE=\"${QUERY_FILE}\""

SCRIPTPATH=`echo $0 | /bin/sed -e 's,[\\/][^\\/][^\\/]*$,,'`
. ${SCRIPTPATH}/utils.sh # sets correct STATE_* return values

#export ORACLE_HOME=/usr/lib/oracle/11.2/client
#export PATH=${ORACLE_HOME}/bin:$PATH
#export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
#export TNS_ADMIN=/mnt/tns # directory of correct tnsnames.ora

if [ ! -f ${ACCESS_FILE} ]; then
    echo "[CRITICAL] unable to locate access_file ${ACCESS_FILE} from `pwd`"
    exit ${STATE_CRITICAL}
fi

if [ ! -r ${ACCESS_FILE} ]; then
    echo "[CRITICAL] unable to read access_file ${ACCESS_FILE}"
    exit ${STATE_CRITICAL}
fi

if [ `grep "USERNAME " ${ACCESS_FILE} | wc -l` -eq 0 ]; then
    echo "[CRITICAL] unable to locate USERNAME in ${ACCESS_FILE}"
    exit ${STATE_CRITICAL}
fi

if [ `grep "PASSWORD " ${ACCESS_FILE} | wc -l` -eq 0 ]; then
    echo "[CRITICAL] unable to locate PASSWORD in ${ACCESS_FILE}"
    exit ${STATE_CRITICAL}
fi

if [ `grep "CONNECTION_STRING " ${ACCESS_FILE} | wc -l` -eq 0 ]; then
    echo "[CRITICAL] unable to locate CONNECTION_STRING ${ACCESS_FILE}"
    exit ${STATE_CRITICAL}
fi

if [ ! -f ${QUERY_FILE} ]; then
    echo "[CRITICAL] unable to locate query_file ${QUERY_FILE} from `pwd`"
    exit ${STATE_CRITICAL}
fi

if [ ! -r ${QUERY_FILE} ]; then
    echo "[CRITICAL] unable to read query_file ${QUERY_FILE}"
    exit ${STATE_CRITICAL}
fi

#if [ `grep "SQL_QUERY " ${QUERY_FILE} | wc -l` -eq 0 ]; then
#    echo "[CRITICAL] unable to locate SQL_QUERY in ${QUERY_FILE}"
#    exit ${STATE_CRITICAL}
#fi

USERNAME=$(grep "^USERNAME" ${ACCESS_FILE}|awk '{print $2}')
PASSWORD=$(grep "^PASSWORD" ${ACCESS_FILE}|awk '{print $2}')
CONNECTION_STRING=$(grep "^CONNECTION_STRING" ${ACCESS_FILE}|awk '{print $2}')
##echo "USERNAME: $USERNAME" >> /tmp/nagsql.txt
##echo "PASSWORD: $PASSWORD" >> /tmp/nagsql.txt
##echo "CONNECTION_STRING: $CONNECTION_STRING" >> /tmp/nagsql.txt
SQL_QUERY=`cat ${QUERY_FILE}`

#{ while read record;do
#    echo "record=\"${record}\""
#    WORD_COUNT=`echo $record | grep "^SQL_QUERY" | wc -w | sed s/\ //g`
#    if [ ${WORD_COUNT} -ne 0 ]; then
#        SQL_QUERY=`echo $record | sed s/SQL_QUERY\ //g`
#        echo "SQL_QUERY=\"${SQL_QUERY}\""
#    fi
#done } < ${QUERY_FILE}

START_TIME=`date +%H%M%S`
##echo "START TIME: ${START_TIME}" >> /tmp/nagsql.txt

##echo "SQL QUERY: ${SQL_QUERY}" >> /tmp/nagsql.txt



# execute query
DB_RESULT=""
DB_RESULT=`sqlplus -s <<EOT
$USERNAME/$PASSWORD@$CONNECTION_STRING
set pagesize 9999
set lines 4096
set head off
set pages 0
set echo off
set feedback off
${SQL_QUERY}
exit
EOT
`
RETRESULT=$?

END_TIME=`date +%H%M%S`

ERRCNT=`echo ${DB_RESULT} | grep ORA- | wc -l`

#if [ ${ERRCNT} -ne 0  -o  ${RETRESULT} -ne 0 ] ; then
#    let ELAPSED_TIME=${END_TIME}-${START_TIME}
#    if [ ${ERRCNT} -gt 0 ]; then
#        ORA_ERROR=`echo ${DB_RESULT} | grep "ORA-"`
#        echo "[CRITICAL: sql query execution failed RETRESULT=\"${RETRESULT}\" ORA_ERROR=\"${ORA_ERROR}\" | elapsedTime=${ELAPSED_TIME}secs"
#    else
#        echo "[CRITICAL: sql query execution failed RETRESULT=\"${RETRESULT}\" DB_RESULT=\"${DB_RESULT}\" | elapsedTime=${ELAPSED_TIME}secs"
#    fi
#    exit $STATE_CRITICAL
#fi

# echo "${DB_RESULT}"

# show resultset
let col_count=0
let rec_count=0
RECORD=""
for col_value in ${DB_RESULT}; do
    #echo "col_value=\"${col_value}\""
    let col_count=col_count+1
    RECORD=`echo ${RECORD} ${col_value}`
    if [ col_count -eq 3 ]; then
        let rec_count=rec_count+1
        #echo "RECORD=\"${RECORD}\""

        # extract return value and datetime
        set -A COLARRAY `echo ${RECORD}`
        REC_COL0=${COLARRAY[0]}
        REC_COL1=${COLARRAY[1]}
        REC_COL2=${COLARRAY[2]}
        #echo "[${rec_count}] REC_COL0=\"${REC_COL0}\""
        #echo "[${rec_count}] REC_COL1=\"${REC_COL1}\""
        #echo "[${rec_count}] REC_COL2=\"${REC_COL2}\""

        # initialize values for next record
        let col_count=0
        RECORD=""
    fi
done   

#echo "${DB_RESULT}"
#echo "${DB_RESULT:0:2}"
#echo "DB RESULT: ${DB_RESULT}" >> /tmp/nagsql.txt

if [[ ${DB_RESULT:0:2} == "OK" ]]; then
   echo "$DB_RESULT"
   exit $STATE_OK
      elif [[ ${DB_RESULT:0:2} != "OK" ]]; then
         echo "CRITICAL: $DB_RESULT"
         exit $STATE_CRITICAL

else
   echo "UNKNOWN: Can't make a check: $DB_RESULT"
   exit $STATE_UNKNOWN
fi


It doesn't work under system database account as well.
My access file:
$ cat system_orabi11g
USERNAME system
PASSWORD myps
CONNECTION_STRING (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.111.7)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orabi11g)))


My Query file (test):
$cat sql_test.sql
SELECT trim('ERROR 123') from dual;


My request from command line (works):
[nagios@nagios01 ~]$ /usr/local/nagios/libexec/check_sql_blockedsessions2 /usr/local/nagios/libexec/sql/system_orabi11g /usr/local/nagios/libexec/sql/sql_test.sql
CRITICAL: ERROR 123


If I change query file to this:
$cat sql_test.sql
SELECT trim('OK 321') from dual;


Then I receive OK in command line (works):
[nagios@nagios01 ~]$ /usr/local/nagios/libexec/check_sql_blockedsessions2 /usr/local/nagios/libexec/sql/system_orabi11g /usr/local/nagios/libexec/sql/sql_test.sql
OK 123


Settings in Nagios:
define command {
command_name check_blockedsessions2
command_line /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin $USER1$/check_sql_blockedsessions2 $USER1$/sql/$ARG1$ $USER1$/sql/$ARG2$
}

define service{
use cod-service
host_name bs-dbs1-cod
service_description Blocked Sessions TEST
check_command check_blockedsessions2!system_orabi11g!sql_test.sql
}


Screenshot from Web GUI (not works):
Image

Permissions:
ls -l /usr/lib/oracle/11.2/client64/
drwxr-xr-x 2 root root 4096 Feb 27 2016 bin
drwxr-xr-x 2 root root 4096 Feb 27 2016 lib
drwxr-xr-x 3 root root 4096 Feb 27 2016 network

ls -l /usr/lib/oracle/11.2/client64/bin/
-rwxr-xr-x 1 root root 8104 Aug 24 2013 adrci
-rwxr-xr-x 1 root root 31920 Aug 24 2013 genezi
-rwxr-xr-x 1 root root 4872 Aug 24 2013 sqlplus

ls -l /usr/lib/oracle/11.2/client64/network/admin/
-rw-r--r-- 1 root root 1495 Jun 7 21:54 tnsnames.ora

ls -l /usr/local/nagios/libexec/check_sql_blockedsessions2
-rwxr-xr-x 1 nagios nagcmd 6793 Jul 10 16:03 /usr/local/nagios/libexec/check_sql_blockedsessions2

ls -l /usr/local/nagios/libexec/sql/sql_test.sql
-rw-r--r-- 1 nagios nagcmd 32 Jul 10 16:49 /usr/local/nagios/libexec/sql/sql_test.sql
Attachments
nagios_sqlplus.jpg
whitest
 
Posts: 107
Joined: Tue Dec 30, 2014 8:16 am

Re: Sqlplus and Web-GUI

Postby scottwilkerson » Tue Jul 10, 2018 3:46 pm

Can you run the command from the CLI the same way you do in Nagios with this at the beginning

Code: Select all
/usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin


Then, also, if that works, I would add the following the end of the command 2>&1 this way you should get the error output in nagios to see what it is erroring
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
User avatar
scottwilkerson
DevOps Engineer
 
Posts: 11693
Joined: Tue Nov 15, 2011 3:11 pm
Location: Nagios Enterprises

Re: Sqlplus and Web-GUI

Postby whitest » Wed Jul 11, 2018 1:18 am

scottwilkerson wrote:Can you run the command from the CLI the same way you do in Nagios with this at the beginning

Code: Select all
/usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin


Then, also, if that works, I would add the following the end of the command 2>&1 this way you should get the error output in nagios to see what it is erroring


Thank you for your response, scottwilkerson.

Actually I executed the command in command-line in the same way as it defined in command.cfg:
Code: Select all
[nagios@nagios01 ~]$  /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin /usr/local/nagios/libexec/check_sql_blockedsessions2 /usr/local/nagios/libexec/sql/system_orabi11g /usr/local/nagios/libexec/sql/sql_test.sql
OK 123


I've made change of command definition as you advised:
Code: Select all
define command {
   command_name  check_blockedsessions2
   command_line  /usr/bin/env LD_LIBRARY_PATH=/usr/lib/oracle/11.2/client64/lib ORACLE_HOME=/usr/lib/oracle/11.2/client64 TNS_ADMIN=/usr/lib/oracle/11.2/client64/network/admin $USER1$/check_sql_blockedsessions2 $USER1$/sql/$ARG1$ $USER1$/sql/$ARG2$ 2>&1
}


Now I can see more information in Web GUI:
/usr/local/nagios/libexec/check_sql_blockedsessions2[150]: sqlplus: not found [No such file or directory]


Then I defined variables ORACLE_HOME,PATH,TNS_ADMIN and LD_LIBRARY_PATH in the script check_sql_blockedsessions2:
Code: Select all
export ORACLE_HOME=/usr/lib/oracle/11.2/client64/
export PATH=${ORACLE_HOME}/bin:$PATH
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=${ORACLE_HOME}/network/admin


And now everything working fine!
Thank you for your help!
whitest
 
Posts: 107
Joined: Tue Dec 30, 2014 8:16 am

Re: Sqlplus and Web-GUI

Postby whitest » Wed Jul 11, 2018 1:45 am

Finally, now I have two solutions to check blocking sessions in Oracle database.

SQL query:
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2 UNION ALL SELECT 'OK. No blocking sessions found.' "blocking_status" FROM dual WHERE NOT EXISTS (SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2);


First solution. Through check_oracle_health plugin in sql mode with url-encoded sql query:
[nagios@nagios01 libexec]$ ./check_oracle_health -connect orabi11g --user nagios --password mypass --mode sql --name "SELECT%20s1.username%20%7C%7C%20%27%40%27%20%7C%7C%20s1.machine%20%7C%7C%20%27%20(%20SID%3D%27%20%7C%7C%20s1.sid%20%7C%7C%20%27%20)%20is%20blocking%20%27%20%7C%7C%20s2.username%20%7C%7C%20%27%40%27%20%7C%7C%20s2.machine%20%7C%7C%20%27%20(%20SID%3D%27%20%7C%7C%20s2.sid%20%7C%7C%20%27%20)%20%27%20AS%20blocking_status%20FROM%20v%24lock%20l1%2C%20v%24session%20s1%2C%20v%24lock%20l2%2C%20v%24session%20s2%20WHERE%20s1.sid%3Dl1.sid%20AND%20s2.sid%3Dl2.sid%20AND%20l1.BLOCK%3D1%20AND%20l2.request%20%3E%200%20AND%20l1.id1%20%3D%20l2.id1%20AND%20l2.id2%20%3D%20l2.id2%20UNION%20ALL%20SELECT%20%20%27OK.%20No%20blocking%20sessions%20found.%27%20%22blocking_status%22%20FROM%20dual%20WHERE%20NOT%20EXISTS%20(SELECT%20s1.username%20%7C%7C%20%27%40%27%20%7C%7C%20s1.machine%20%7C%7C%20%27%20(%20SID%3D%27%20%7C%7C%20s1.sid%20%7C%7C%20%27%20)%20is%20blocking%20%27%20%7C%7C%20s2.username%20%7C%7C%20%27%40%27%20%7C%7C%20s2.machine%20%7C%7C%20%27%20(%20SID%3D%27%20%7C%7C%20s2.sid%20%7C%7C%20%27%20)%20%27%20AS%20blocking_status%20FROM%20v%24lock%20l1%2C%20v%24session%20s1%2C%20v%24lock%20l2%2C%20v%24session%20s2%20WHERE%20s1.sid%3Dl1.sid%20AND%20s2.sid%3Dl2.sid%20AND%20l1.BLOCK%3D1%20AND%20l2.request%20%3E%200%20AND%20l1.id1%20%3D%20l2.id1%20AND%20l2.id2%20%3D%20l2.id2)" --name2 "OK. No blocking sessions found." --regexp --method sqlplus
OK - output OK. No blocking sessions found. matches pattern OK. No blocking sessions found.


Second solution. Through check_sql_query script described in the first post:
[nagios@nagios01 libexec]$ ./check_sql_blockedsessions2 /usr/local/nagios/libexec/sql/nagios_orabi11g /usr/local/nagios/libexec/sql/sql_blockedsessions2.sql
OK. No blocking sessions found.
[nagios@nagios01 libexec]$
[nagios@nagios01 libexec]$cat /usr/local/nagios/libexec/sql/sql_blockedsessions2.sql
SET PAGES 0;
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2 UNION ALL SELECT 'OK. No blocking sessions found.' "blocking_status" FROM dual WHERE NOT EXISTS (SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2);
[nagios@nagios01 libexec]$


Advantage of the second solution in more beauty output and ability to execute even PL/SQL scripts! For example:
[nagios@nagios01 libexec]$ cat /usr/local/nagios/libexec/sql/sql_blockedsessions_new.sql
set serveroutput on;
SET PAGES 0;
set feedback off;
declare
res varchar2(2000);
begin
SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status into res FROM v$lock l1, v$session s1, v$lock l2, v$session s2 WHERE s1.sid=l1.sid AND s2.sid=l2.sid AND l1.BLOCK=1 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
if sql%found then
dbms_output.put_line(res);
end if;
exception
when no_data_found then
dbms_output.put_line('OK. No blocking sessions found.');
when others then
dbms_output.put_line('Another error! '||sqlcode||' '||sqlerrm);
raise;
end;
/
[nagios@nagios01 libexec]$


You can close the thread. Tnx!
whitest
 
Posts: 107
Joined: Tue Dec 30, 2014 8:16 am

Re: Sqlplus and Web-GUI

Postby scottwilkerson » Wed Jul 11, 2018 8:32 am

Excellent, glad to help.

Locking
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
User avatar
scottwilkerson
DevOps Engineer
 
Posts: 11693
Joined: Tue Nov 15, 2011 3:11 pm
Location: Nagios Enterprises


Return to Nagios Plugin Development

Who is online

Users browsing this forum: No registered users and 3 guests