check_database_size

#! /usr/bin/env bash

###############################################################################
# Monitor DB2 with Nagios
# Copyright 2013,2014,2015 Andres Gomez Casanova
# https://github.com/angoca/monitor-db2-with-nagios
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
# http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
###############################################################################

# This script checks the database size. Retrieves a critical alarm when the
# size is bigger than the given size in critical, or retrieves a warning
# alarm when the size percentage is bigger than the provided, comparing with
# the allocated size.
#
# The user that executes this script needs some special permission in the
# tables associated with storage management.
#
# db2 grant execute on procedure sysproc.GET_DBSIZE_INFO to user nagios
# db2 grant execute on package NULLID.SYSSH200 to user nagios
# db2 grant select,update on table SYSTOOLS.STMG_DBSIZE_INFO to user nagios
#
# Be careful when using with a standby database in an HADR environment. The
# values should be updated in the primary database. The standby should only
# retrieve these values.
#
# # 'check_database_size' command definition
# define command {
#         command_name    check_database_size
#         command_line    $USER1$/check_by_ssh -H $HOSTADDRESS$ -l nagios -t 20 -C "scripts/check_database_size -i '$ARG1$' -d '$ARG2$' -r 720"
# }
# define service{
#         host_name               db2sever
#         service_description     Database size
#         check_command           check_database_size!/home/db2inst1!sample
#         use                     generic-service
# }
#
# When using NRPE:
#
# In the database server:
#
# command[check_database_size]=/home/nagios/scripts/check_database_size -i /home/db2inst1 -d sample -r 720
#
# Then modify the path according to your script location, instance home dir,
# database, refresh rate and thresholds.
#
# In the Nagios server:
#
# define service{
#         host_name               db2server
#         service_description     Database size
#         check_command           check_nrpe_1arg!check_database_size
#         use                     generic-service
# }
#
# Parameters:
# -c | --critical : Database size considered as critical.
# -d | --database : Database name.
# -h | --help : Show the help of this script.
# -i | --instance : Home directory of the instance. Usually it is
#   /home/db2inst1.
# -K | --mk : Change the output for Check_MK.
# -p | --percentage : Use percentage (size : allocated).
# -r | --refresh : Cache minutes before refresh (Stored procedure).
# -T | --trace : Trace mode. Writes output in /tmp.
# -v | --verbose : Execute the program in verbose mode.
# -V | --version : Show the current version of this script.
#
# In AIX, the long name options are not supported.
#
# The exit codes are the standard for Nagios.
#
# 0 The plugin was able to check the service and it appeared to be functioning
#   properly.
# 1 The plugin was able to check the service, but it appeared to be above some
#   "warning" threshold or did not appear to be working properly.
# 2 The plugin detected that either the service was not running or it was above
#   some "critical" threshold.
# 3 Invalid command line arguments were supplied to the plugin or low-level
#   failures internal to the plugin (such as unable to fork, or open a tcp
#   socket) that prevent it from performing the specified operation.
#   Higher-level errors (such as name resolution errors, socket timeouts, etc)
#   are outside of the control of plugins and should generally NOT be reported
#   as UNKNOWN states.
#
# Author: Andres Gomez Casanova
# Version: v1.2 2015-10-15

# Flag for debugging.
#set -xv

# Locale to print messages in English. Prevent language problems.
export LANG=en_US

# Version of this script.
function print_revision {
  echo Andres Gomez Casanova - AngocA
  echo v1.2 2015-10-15
}
# Function to show the help
function print_usage {
  /bin/cat <<__EOT
Usage: ${1} { -i instanceHomeDirectory -d databaseName [-c][-p][-r][-K] | -h
| -V } [-T][-v]
__EOT
}

function print_help {
  print_revision
  print_usage ${1}
  # Max 80 chars width.
  /bin/cat <<__EOT
-------------------------------------------------------------------------------
Check the database usage size according with the allocated size. It triggers
alarms if the size is bigger that the provided one (critical) or if it bigger 
that the provided percentage.
-c | --critical INTEGER
   Database usage size to be consider as critical. Value in bytes.
   Default unlimited (-1)
-d | --database STRING
   Database name.
-h | --help
   Shows the current documentation.
-i | --instance STRING
   Instance home directory. It is usually /home/db2inst1.
-K | --mk
   Changes the output to be compatible with Check_MK.
-p | --percentage INTEGER
   Use percentage (size : allocated). Value between 1 and 99.
-r | --refresh INTEGER
   Quantity of minutes before refresh cache values in the database.
   Used to call the stored procedure GET_DBSIZE_INFO.
-T | --trace
   Trace mode: writes date and output in /tmp.
-v | --verbose
   Executes the script in verbose mode (multiple times).
-V | --version
   Shows the current version of this script.

In AIX, the long name options are not supported.
__EOT
}

# Variable to control the flow execution. Prevent Spaghetti code.
CONTINUE=true

# Nagios return codes
OK=0
WARNING=1
CRITICAL=2
UNKNOWN=3
# This is the returned code.
RETURN=${UNKNOWN}

# Nagios Output
# Text output 80 chars | Optional Perf Data Line 1
# Long text Line 1
# Long text Line 2 | Optional Perf Data Line 2
# Optional Perf Data Line 3
OUTPUT=
PERFORMANCE=
LONG_OUTPUT=
LONG_PERFORMANCE=
PERF_MK="-"

APPL_NAME=$(basename ${0})

echo "$(date +"%Y-%m-%d-%H.%M.%S") $$ Started ${APPL_NAME} $@" >> /tmp/${APPL_NAME}.log

# Checks the lock file does not exist.
# The lock file is the way the command was called with its parameters
# without spaces.
COPY_ARGS=("${@}") 
LOCK_FILE=
for VALUE in "${COPY_ARGS[@]}" ; do
  LOCK_FILE="${LOCK_FILE}${VALUE}"
done
LOCK_FILE=${LOCK_FILE//\//}
LOCK_FILE=${LOCK_FILE//\\/}
LOCK_FILE=${LOCK_FILE//\:/}
LOCK_FILE=${LOCK_FILE//\*/}
LOCK_FILE=${LOCK_FILE//\|/}
LOCK_FILE="/tmp/${APPL_NAME}${LOCK_FILE}.lock"
if [[ ! -r ${LOCK_FILE} ]] ; then
  echo $$ > ${LOCK_FILE}
  LOCKED=true
else
  # If it exist, then check if the process is running.
  EXIST=$(ps -ef | grep $(cat ${LOCK_FILE}) | grep ${APPL_NAME})
  # If process is not running, delete it.
  if [[ ${EXIST} == "" ]] ; then
    rm ${LOCK_FILE}
    if [[ ! -r ${LOCK_FILE} ]] ; then
      echo $$ > ${LOCK_FILE}
      LOCKED=true
    else
      OUTPUT="The lock file cannot be replaced: ${LOCK_FILE}"
      CONTINUE=false
      RETURN=${UNKNOWN}
    fi
  else
    OUTPUT="An instance of the script with the same parameters is already running."
    CONTINUE=false
    RETURN=${UNKNOWN}
  fi
fi

if [[ ${#} -eq 0 ]] ; then
  print_usage ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

# Checks the operating system. geopt works different in AIX than in Linux.
OS=$(uname)

if [[ "${OS:0:5}" == "Linux" ]] ; then
  # The following requires GNU getopt. See the following discussion.
  # http://stackoverflow.com/questions/402377

  TEMP=$(getopt -o c:d:hi:Kp:r:TvV --long critical:,database:,help,instance:,mk,percentage:,refresh:,trace,verbose,version \
               -n ${APPL_NAME} -- "${@}")
elif [[ "${OS:0:3}" == "AIX" ]] ; then
  set -- $(getopt c:d:hi:Kp:r:TvV ${*})
elif [[ "${OS:0:6}" == "Darwin" || "${OS:0:5}" == "SunOS" || "${OS:0:5}" == "HP-UX" ]] ; then
  echo "This plugin is not yet supported in your platform."
  echo "Please create a ticket in GitHub if you want to enable your current platform."
  echo "https://github.com/angoca/monitor-db2-with-nagios/issues"
  RETURN=${UNKNOWN}
  CONTINUE=false
elif [[ "${OS:0:6}" == "CYGWIN" ]] ; then
  echo "This plugin is not supported in Cygwin"
  RETURN=${UNKNOWN}
  CONTINUE=false
else
  echo "The platform is unknown: ${OS}"
  echo "Please create a ticket in GitHub: https://github.com/angoca/monitor-db2-with-nagios/issues"
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${?} -ne 0 ]] ; then
  print_usage ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true ]] ; then
  if [[ "${OS}" == "Linux" ]] ; then
    # Note the quotes around ${TEMP}: they are essential!
    eval set -- "${TEMP}"
  fi
  HELP=false
  VERSION=false
  CHECK_MK=false
  # Verbosity level
  VERBOSE=0
  # Trace activated
  TRACE=false
  LOG=/tmp/${APPL_NAME}.log
  INSTANCE_HOME=
  DATABASE_NAME=
  CRITICAL_THRES=-1
  CRITICAL_SET=false
  PERCENTAGE=-1
  PERCENTAGE_SET=false
  REFRESH=-1
  if [[ "${OS:0:5}" == "Linux" ]] ; then
    while true; do
      case "${1}" in
        -c | --critical ) CRITICAL_THRES=$(echo ${2} | cut -d' ' -f1) ; CRITICAL_SET=true ; shift 2 ;;
        -d | --database ) DATABASE_NAME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -h | --help ) HELP=true ; shift ;;
        -i | --instance ) INSTANCE_HOME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -K | --mk ) CHECK_MK=true ; shift ;;
        -p | --percentage ) PERCENTAGE=$(echo ${2} | cut -d' ' -f1) ; PERCENTAGE_SET=true ; shift 2 ;;
        -r | --refresh ) REFRESH=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -T | --trace ) TRACE=true ; shift ;;
        -v | --verbose ) VERBOSE=$(( ${VERBOSE} + 1 )) ; shift ;;
        -V | --version ) VERSION=true ; shift ;;
        -- ) shift ; break ;;
        * ) break ;;
      esac
    done
  elif [[ "${OS:0:3}" = "AIX" ]] ; then
    while [[ $1 != -- ]] ; do
      case "${1}" in
        -c) CRITICAL_THRES=$(echo ${2} | cut -d' ' -f1) ; CRITICAL_SET=true ; shift 2 ;;
        -d) DATABASE_NAME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -h) HELP=true ; shift ;;
        -i) INSTANCE_HOME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -K) CHECK_MK=true ; shift ;;
        -p) PERCENTAGE=$(echo ${2} | cut -d' ' -f1) ; PERCENTAGE_SET=true ; shift 2 ;;
        -r) REFRESH=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -T) TRACE=true ; shift ;;
        -v) VERBOSE=$(( ${VERBOSE} + 1 )) ; shift ;;
        -V) VERSION=true ; shift ;;
        * ) break ;;
      esac
    done
  fi
fi

if [[ ${TRACE} == true ]] ; then
  echo ">>>>>" >> ${LOG}
  date >> ${LOG}
  echo "Instance at ${INSTANCE_HOME}" >> ${LOG}
  echo "PID ${$}" >> ${LOG}
fi

ECHO="help:${HELP}, version:${VERSION}, verbose:${VERBOSE}"
ECHO="${ECHO}, directory:${INSTANCE_HOME}, database:${DATABASE_NAME}"
ECHO="${ECHO}, percentage:${PERCENTAGE}, percentage_set:${PERCENTAGE_SET}"
ECHO="${ECHO}, check_mk:${CHECK_MK}"
ECHO="${ECHO}, critical:${CRITICAL_THRES}, critical_set:${CRITICAL_SET}"
ECHO="${ECHO}, refresh:${REFRESH} "

if [[ ${VERBOSE} -ge 2 ]] ; then
  echo ${ECHO}
fi

if [[ ${TRACE} == true ]] ; then
  echo "PARAMS:${ECHO}" >> ${LOG}
fi

if [[ ${CONTINUE} == true && ${HELP} == true ]] ; then
  print_help ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true && ${VERSION} == true ]] ; then
  print_revision ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true && ${INSTANCE_HOME} == "" ]] ; then
  print_usage ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true && ${DATABASE_NAME} == "" ]] ; then
  print_usage ${APPL_NAME}
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true
    && ${CRITICAL_SET} == true && ( ${CRITICAL_THRES} == ""
    || ! ${CRITICAL_THRES} =~ ^[0-9]+$
    || ${CRITICAL_THRES} -lt 0 ) ]] ; then
  print_usage ${APPL_NAME}
  echo "Threshold should be greater than 0."
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} ==  true && ${PERCENTAGE_SET} == true && ( ${PERCENTAGE} == "" 
    || ! ${PERCENTAGE} =~ ^[0-9]+$ 
    || ${PERCENTAGE} -lt 0 || ${PERCENTAGE} -ge 100 ) ]] ; then
  print_usage ${APPL_NAME}
  echo "Percentage should be between 1 and 99."
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true && ! ${REFRESH} =~ ^[-0-9]+$ || ${REFRESH} -lt -1 ]] ; then
  print_usage ${APPL_NAME}
  echo "Refresh cache should be -1 for default (30 minutes), 0 to force"
  echo "calculation, or a quantity of second (positive value)."
  RETURN=${UNKNOWN}
  CONTINUE=false
fi

if [[ ${CONTINUE} == true ]] ; then
  if [[ -d ${INSTANCE_HOME} && -e ${INSTANCE_HOME}/sqllib/db2profile ]] ; then
    # Load the DB2 profile.
    . ${INSTANCE_HOME}/sqllib/db2profile
    INSTANCE_NAME=$(db2 get instance | awk '/instance/ {print $7}')
  else
    OUTPUT="Instance directory is invalid."
    RETURN=${UNKNOWN}
    CONTINUE=false
  fi
fi

if [[ ${CONTINUE} == true ]] ; then
  COMMAND_DATABASE="db2 list db directory"
  if [[ ${VERBOSE} -ge 2 ]] ; then
    echo "COMMAND: ${COMMAND_DATABASE}"
  fi
  DATABASE=$(${COMMAND_DATABASE})
  if [[ ${TRACE} == true ]] ; then
    echo "RESULT:'${DATABASE}'" >> ${LOG}
  fi
  DATABASE=$(printf '%s\n' "${DATABASE}" | awk '/Database alias/ {print $4}' | grep -iw ${DATABASE_NAME})
  if [[ ${VERBOSE} -ge 3 ]] ; then
    echo "RESULT:'${DATABASE}'"
  fi
  if [[ ${DATABASE} == "" ]] ; then
    OUTPUT="The database ${DATABASE_NAME} is not catalogued."
    RETURN=${UNKNOWN}
    CONTINUE=false
  fi
fi

if [[ ${CONTINUE} == true ]] ; then
  COMMAND_DBSIZE="db2 CALL GET_DBSIZE_INFO(?, ?, ?, ${REFRESH})"
  if [[ ${VERBOSE} -ge 2 ]] ; then
    echo "COMMAND: ${COMMAND_DBSIZE}"
  fi
  DBSIZE=$(db2 connect to ${DATABASE_NAME} > /dev/null ; ${COMMAND_DBSIZE} ; db2 connect reset > /dev/null)
  if [[ ${VERBOSE} -ge 3 ]] ; then
    echo "RESULT:'${DBSIZE}'"
  fi
  # Check for errors
  CONN_ERROR=$(echo ${DBSIZE} | grep SQL1024)
  HADR_ERROR=$(echo ${DBSIZE} | grep SQL1773)
  PRIVILEGES_ERROR=$(echo ${DBSIZE} | grep SQL0551)
  BIND_ERROR=$(echo ${DBSIZE} | grep SQL0443)
  WORKLOAD_ERROR=$(echo ${DBSIZE} | grep SQL4707)
  UNKNOWN_ERROR=$(echo ${DBSIZE} | grep SQL)
  if [[ ${CONN_ERROR} == "error" ]] ; then
    OUTPUT="Connection problem."
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${HADR_ERROR} != "" ]] ; then
    OUTPUT="Trying to update calculation in Standby database."
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${PRIVILEGES_ERROR} != "" ]] ; then
    OUTPUT="User does not have enough privileges."
    LONG_OUTPUT="${DBSIZE}"
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${BIND_ERROR} != "" ]] ; then
    OUTPUT="User has to do a bind in the DB or not enough privileges."
    LONG_OUTPUT="${DBSIZE}"
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${WORKLOAD_ERROR} != "" ]] ; then
    OUTPUT="User cannot execute this workload."
    LONG_OUTPUT="${DBSIZE}"
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${UNKNOWN_ERROR} != "" ]] ; then
    OUTPUT="Unknown problem while executing the SP CALL GET_DBSIZE_INFO(?, ?, ?, ${REFRESH})"
    LONG_OUTPUT="Re-execute the script with multiple verbosity mode."
    RETURN=${UNKNOWN}
    CONTINUE=false
  elif [[ ${DBSIZE} == "" ]] ; then
    OUTPUT="Empty output, check the command"
    RETURN=${UNKNOWN}
    CONTINUE=false
  else
    INDEX=0
    ALLOCATED=
    SIZE=
    while IFS= read LINE ; do
      if [[ ${INDEX} -eq 2 ]] ; then
        SIZE=$(echo ${LINE} | cut -d' ' -f4)
      elif [[ ${INDEX} -eq 3 ]] ; then
        ALLOCATED=$(echo ${LINE} | cut -d' ' -f4)
      fi
      INDEX=$((${INDEX}+1))
    done < <( printf '%s\n' "${DBSIZE}" | grep Value)
    
    PERCENT=$(echo "${SIZE} * 100 / ${ALLOCATED}" | bc)
    if [[ ${VERBOSE} -ge 3 ]] ; then
      echo "PERCENT:'${PERCENT}'"
    fi
    if [[ ${CRITICAL_SET} == true && ${CRITICAL_THRES} -ne 0 && ${SIZE} -gt ${CRITICAL_THRES} ]] ; then
      OUTPUT="Database size surpassed the provided limit (${SIZE} > ${CRITICAL_THRES}). "
      RETURN=${CRITICAL}
    elif [[ ${PERCENTAGE_SET} == true && ${PERCENTAGE} -ne 0 && ${PERCENT} -ge ${PERCENTAGE} ]] ; then
      OUTPUT="Database size is reaching the limit (${PERCENT}). "
      RETURN=${WARNING}
    else
      OUTPUT="OK. "
      RETURN=${OK}
    fi
    OUTPUT="${OUTPUT}Size is ${SIZE}B allocated in ${ALLOCATED}B" 
    
    # Performance data
    PERFORMANCE="'Database_size'=${SIZE}B"
    LONG_PERFORMANCE="'Database_allocation'=${ALLOCATED}B"
    PERF_MK="${PERFORMANCE}|${LONG_PERFORMANCE}"
  fi
fi

# Prints the output.
if [[ ${OUTPUT} == "" ]] ; then
  OUTPUT="Note: The test was not executed."
fi
# Builds the output.
if [[ ${CHECK_MK} == true ]] ; then
  echo "${RETURN} databaseSize-${INSTANCE_NAME}-${DATABASE_NAME} ${PERF_MK} ${OUTPUT}"
else
  echo -e "${OUTPUT}|${PERFORMANCE}\n${LONG_OUTPUT}|${LONG_PERFORMANCE}"
fi
# Returns the error code.
if [[ ${VERBOSE} -ge 2 ]] ; then
  echo "Return code: ${RETURN}"
fi
if [[ ${TRACE} == true ]] ; then
  echo -e "OUTPUT:${OUTPUT}\nPERF:${PERFORMANCE}\nLONG_OUT:${LONG_OUTPUT}\nLONGPERF:${LONG_PERFORMANCE}\nRET_CODE:${RETURN}" >> ${LOG}
  date >> ${LOG}
  echo -e "<<<<<\n" >> ${LOG}
fi

if [[ ${LOCKED} == true && -r ${LOCK_FILE} ]] ; then
  rm ${LOCK_FILE}
fi

echo "$(date +"%Y-%m-%d-%H.%M.%S") $$ Ended ${APPL_NAME} ${COPY_ARGS[@]}" >> /tmp/${APPL_NAME}.log

exit ${RETURN}