Sqlplus and Web-GUI
Posted: 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/P ... ry/details) to check blocking SQL sessions in my Oracle 11gR2 database. I modified the plugin to handle sql result (OK,CRITICAL,UNKNOWN).
Code:
It doesn't work under system database account as well.
My access file:
Permissions:
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/P ... ry/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
My access file:
My Query file (test):$ 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 request from command line (works):$cat sql_test.sql
SELECT trim('ERROR 123') from dual;
If I change query file to this:[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
Then I receive OK in command line (works):$cat sql_test.sql
SELECT trim('OK 321') from dual;
Settings in Nagios:[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
Screenshot from Web GUI (not works):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
}
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