#! /usr/bin/ksh
# check_db_record_count
# nagios plugin to check if a query returns a specific count based on a sql query within a specific timeframe excluding weekends and holidays
# author: Sergei Haramundanis 24-Oct-2006
# updated 08-Mar-2007 version 2.0 to include support for reporting remedial action
# updated 24-Apr-2007 version 2.1 to exclude running on weekends and holidays
#
# usage: check_db_record_count access_file check_from_time check_to_time holiday_table "holiday_criteria" query_file max_count
#
# Description:
#
# This plugin will check if a query exceeds a specific count (max_count) at a specific timeframe specified by check_from_time and check_to_time
# (in HHMM format) by executing the specified sql query in the query_file
#
# The check will also only be executed within the check_from_time and check_to_time specified excluding weekends and holidays
#
# This plugin requires oracle sqlplus (see definition of ORACLE_HOME, PATH and LD_LIBRARY_PATH further on in this script, you may need to
# change them)
#
# all fields are required
#
# contents of access_file must contain database connection information in the following format:
#
# USERNAME username
# PASSWORD password
# CONNECTION_STRING connection_string
#
# contents of query_file must contain sql query information in the following format:
#
# SQL_QUERY specific_sql_query
# REMEDIAL_ACTION text description of remedial action
#
# these are to be used by sqlplus to login to the database and execute the appropriate sql query
#
# Output:
#
# During any run of the plugin, the value returned will be as follows:
#
# if the current time is within the specified timeframe, it will execute the specific sql query
#
#     if max_count is exceeded it will return a CRITICAL state with the message:
#
#         [CRITICAL] detected n records (max_count # exceeded)
#
#     if max_count is not exceeded it will return an OK state with the message:
# 
#         [OK] detected n record(s) (max_count # not exceeded)
#
# if the current time is outside the specified timeframe it will return an OK state with the message:
#
#     [OK] current time outside of monitoring timeframe check_from_time and check_to_time
#
# if current day is either Sat or Sun it will return an OK state with the message:
#
#     [OK] current day is on weekend (Sat)
#
# if current date matches a record within the holiday_table with the holiday_criteria (i.e. the resulting query returns row count > 0) it will return an OK state with the message:
#
#     [OK] current date found in holiday_table with criteria "holiday_criteria"
#

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

if [ "${1}" = "" -o "${1}" = "--help" ]; then
    echo "check_db_record_count 2.1"
    echo ""
    echo "nagios plugin to check if a query returns a specific count based on a sql query within a specific timeframe excluding weekends and holidays"
    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_db_record_count access_file check_from_time check_to_time holiday_table \"holiday_criteria\" query_file max_count"
    echo "usage: check_db_record --help"
    echo "usage: check_db_record --version"
    exit ${STATE_OK}
fi

if [ ${1} == "--version" ]; then
    echo "check_db_record_count 2.1"
    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 7 ]; then
    echo "[CRITICAL] insufficient arguments"
    exit ${STATE_CRITICAL}
fi

ACCESS_FILE=${1}
CHECK_FROM_TIME=${2}
CHECK_TO_TIME=${3}
HOLIDAY_TABLE=${4}
HOLIDAY_CRITERIA=${5}
QUERY_FILE=${6}
MAX_COUNT=${7}

#echo "ACCESS_FILE=\"${ACCESS_FILE}\""
#echo "CHECK_FROM_TIME=\"${CHECK_FROM_TIME}\""
#echo "CHECK_TO_TIME=\"${CHECK_TO_TIME}\""
#echo "HOLIDAY_TABLE=\"${HOLIDAY_TABLE}\""
#echo "HOLIDAY_CRITERIA=\"${HOLIDAY_CRITERIA}\""
#echo "QUERY_FILE=\"${QUERY_FILE}\""
#echo "MAX_COUNT=\"${MAX_COUNT}\""

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

export ORACLE_HOME=/app/oracle/product/10.2.0
export PATH=${ORACLE_HOME}/bin:$PATH
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib:$LD_LIBRARY_PATH
export TNS_ADMIN=/clareonProd/sched/conf # 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

if [ `grep "REMEDIAL_ACTION " ${QUERY_FILE} | wc -l` -eq 0 ]; then
    echo "[CRITICAL] unable to locate REMEDIAL_ACTION 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}')

{ 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

    # retrieve SQL_QUERY from QUERY_FILE
    let index=`echo ${record} | awk '{print(index($0, var_substr))}' var_substr="SQL_QUERY"`
    if [ ${index} -gt 0 ] ; then
        let index=index+10
        SQL_QUERY=`echo ${record} | awk '{print(substr($0, var_index))}' var_index=${index}`
    fi

    # retrieve REMEDIAL_ACTION from QUERY_FILE
    let index=`echo ${record} | awk '{print(index($0, var_substr))}' var_substr="REMEDIAL_ACTION"`
    if [ ${index} -gt 0 ] ; then
        let index=index+16
        REMEDIAL_ACTION=`echo ${record} | awk '{print(substr($0, var_index))}' var_index=${index}`
    fi
done } < ${QUERY_FILE}

#echo "SQL_QUERY=\"${SQL_QUERY}\""
#echo "REMEDIAL_ACTION=\"${REMEDIAL_ACTION}\""

# build holiday where clause
HOLIDAY_WHERE_CLAUSE=${HOLIDAY_CRITERIA}

# retrieve current date and time
CURRENT_DATE=`date +%Y%m%d`
CURRENT_TIME=`date +%H%M`
CURRENT_DATETIME=`date +%Y%m%d%H%M`
CHECK_FROM_DATETIME=`echo ${CURRENT_DATE}${CHECK_FROM_TIME}`
CHECK_TO_DATETIME=`echo ${CURRENT_DATE}${CHECK_TO_TIME}`

#echo "CURRENT_DATE=\"${CURRENT_DATE}\""
#echo "CURRENT_TIME=\"${CURRENT_TIME}\""
#echo "CURRENT_DATETIME=\"${CURRENT_DATETIME}\""
#echo "CHECK_FROM_DATETIME=\"${CHECK_FROM_DATETIME}\""
#echo "CHECK_TO_DATETIME=\"${CHECK_TO_DATETIME}\""

# determine if current day is on weekend (Sat or Sun)
CURRENT_DAY=`date | awk '{print(substr($1,0,3))}'`

if [ "${CURRENT_DAY}" = "Sat" -o "${CURRENT_DAY}" = "Sun" ]; then
    END_TIME=`date +%H%M%S`
    let ELAPSED_TIME=${END_TIME}-${START_TIME}
    echo "[OK] current day is on weekend (${CURRENT_DAY}) | elapsedTimeSecs=${ELAPSED_TIME}"
    exit ${STATE_OK}
fi

# execute holiday query
HOLIDAY_RESULT=""
HOLIDAY_RESULT=`sqlplus -s <<EOT
$USERNAME/$PASSWORD@$CONNECTION_STRING
set pagesize 9999
set lines 4096
set head off
set echo off 
set feedback off
select count(*) from ${HOLIDAY_TABLE} where
    ${HOLIDAY_WHERE_CLAUSE};
exit
EOT
`
RETRESULT=$?
ERRCNT=`echo ${HOLIDAY_RESULT} | grep ORA- | wc -l`

if [ ${ERRCNT} -ne 0  -o  ${RETRESULT} -ne 0 ] ; then
    echo "SQL HOLIDAY SELECT FAILED HOLIDAY_RESULT=\"${HOLIDAY_RESULT}\""
    exit ${STATE_CRITICAL}
fi

#echo "HOLIDAY_RESULT=\"${HOLIDAY_RESULT}\""

rec_count=`echo ${HOLIDAY_RESULT} | sed /^$/d` # remove all blank lines

#echo "rec_count=\"${rec_count}\""

if [ ${rec_count} -gt 0 ]; then
    END_TIME=`date +%H%M%S`
    let ELAPSED_TIME=${END_TIME}-${START_TIME}
    echo "[OK] current date found in holiday_table with criteria "${HOLIDAY_CRITERIA}" | elapsedTimeSecs=${ELAPSED_TIME}"
    exit ${STATE_OK}
fi

if [ ${CURRENT_DATETIME} -ge ${CHECK_FROM_DATETIME} -a ${CURRENT_DATETIME} -le ${CHECK_TO_DATETIME} ] ; then
    # execute query
    DB_RESULT=""
    DB_RESULT=`sqlplus -s <<EOT
    $USERNAME/$PASSWORD@$CONNECTION_STRING
    set pagesize 9999
    set lines 4096
    set head off
    set echo off 
    set feedback off
    ${SQL_QUERY};
    exit
    EOT
    `
    RETRESULT=$?
    ERRCNT=`echo ${DB_RESULT} | grep ORA- | wc -l`

    if [ ${ERRCNT} -ne 0  -o  ${RETRESULT} -ne 0 ] ; then
        echo "SQL SELECT FAILED DB_RESULT=\"${DB_RESULT}\""
        exit ${STATE_CRITICAL}
    fi

    #echo "DB_RESULT=\"${DB_RESULT}\""

    rec_count=`echo ${DB_RESULT} | sed /^$/d` # remove all blank lines

    #echo "rec_count=\"${rec_count}\""

    # perform validations
    
    if [ ${rec_count} -gt ${MAX_COUNT} ] ; then
        END_TIME=`date +%H%M%S`
        let ELAPSED_TIME=${END_TIME}-${START_TIME}
        echo "[CRITICAL] detected ${rec_count} record(s) (max_count ${MAX_COUNT} exceeded) ; ${REMEDIAL_ACTION} | elapsedTimeSecs=${ELAPSED_TIME}"
        exit ${STATE_CRITICAL}
    fi
    
    if [ ${rec_count} -le ${MAX_COUNT} ] ; then
        END_TIME=`date +%H%M%S`
        let ELAPSED_TIME=${END_TIME}-${START_TIME}
        echo "[OK] detected ${rec_count} record(s) (max_count ${MAX_COUNT} not exceeded) | elapsedTimeSecs=${ELAPSED_TIME}"
        exit ${STATE_OK}
    fi
else
    END_TIME=`date +%H%M%S`
    let ELAPSED_TIME=${END_TIME}-${START_TIME}
    echo "[OK] current time outside of monitoring timeframe ${CHECK_FROM_TIME} and ${CHECK_TO_TIME} | elapsedTimeSecs=${ELAPSED_TIME}"
    exit ${STATE_OK}
fi
    
# shouldn't get here, but if we do return CRITICAL with reason unknown
END_TIME=`date +%H%M%S`
let ELAPSED_TIME=${END_TIME}-${START_TIME}
echo "[CRITICAL] exiting with reason unknown, check shell script execution | elapsedTimeSecs=${ELAPSED_TIME}"
exit ${STATE_CRITICAL}