check_log_usage

#! /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 log usage to prevent log full conditions when there
# are long transactions.
#
# The user used to execute this script should has access to the directory where
# the log files are found.
#
# TODO: Accept percentage for warn and crit
#
# This script was based on the plugins written by Felipe Alkain de Souza.
# http://www.dbatodba.com/db2/scripts-db2/nagios-plugins
#
# In order to be used in Nagios, you have to configure the following.
#
# # 'check_log_usage' command definition
# define command {
#         command_name    check_log_usage
#         command_line    $USER1$/check_by_ssh -H $HOSTADDRESS$ -l nagios -C "scripts/check_log_usage -i '$ARG1$' -d '$ARG2$'"
# }
# define service{
#         host_name               db2server
#         service_description     Log usage
#         check_command           check_log_usage!/home/db2inst1!sample
#         use                     generic-service
# }
#
# Parameters:
# -c | --critical : Critical threshold in quantity of files.
# -d | --database : Database name.
# -f | --files : Counts the quantity of files.
# -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.
# -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.
# -w | --warning : Warning threshold in quantity of files.
#
# 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.1 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.1 2015-10-15
}
# Function to show the help
function print_usage {
  /bin/cat <<__EOT
Usage: ${1} { -i instanceHomeDirectory -d databaseName [-c][-f][-w][-K] | -h
| -V } [-T][-v]
__EOT
}

function print_help {
  print_revision
  print_usage ${1}
  # Max 80 chars width.
  /bin/cat <<__EOT
-------------------------------------------------------------------------------
This script checks for long lock waits in the database.
-c | --critical INTEGER
   Quantity or percentage of secondary log files to consider as critical.
   Default 50%.
-d | --database STRING
   Database name.
-f | --files
   Counts the quantity of allocated files for the logs. This is the basic
   calculation based on the quantity of primary and secondary files, but it
   does not take into account how much of primary files are being used.
-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
-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.
-w | --warning INTEGER
   Quantity or percentage of secondary log files to consider as warning.
   Default 1.

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:fhi:KTvVw: --long critical:,database:,files,help,instance:,mk,trace,verbose,version,warning: \
               -n ${APPL_NAME} -- "${@}")
elif [[ "${OS:0:3}" == "AIX" ]] ; then
  set -- $(getopt c:d:fhi:KTvVw: ${*})
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
  FILES=false
  INSTANCE_HOME=
  DATABASE_NAME=
  WARNING_THRES=1
  CRITICAL_THRES=50
  if [[ "${OS:0:5}" == "Linux" ]] ; then
    while true; do
      case "${1}" in
        -c | --critical ) CRITICAL_THRES=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -d | --database ) DATABASE_NAME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -f | --files ) FILES=true ; shift ;;
        -h | --help ) HELP=true ; shift ;;
        -i | --instance ) INSTANCE_HOME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -K | --mk ) CHECK_MK=true ; shift ;;
        -T | --trace ) TRACE=true ; shift ;;
        -v | --verbose ) VERBOSE=$(( ${VERBOSE} + 1 )) ; shift ;;
        -V | --version ) VERSION=true ; shift ;;
        -w | --warning ) WARNING_THRES=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -- ) 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) ; shift 2 ;;
        -d) DATABASE_NAME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -f) FILES=true ; shift ;;
        -h) HELP=true ; shift ;;
        -i) INSTANCE_HOME=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        -K) CHECK_MK=true ; shift ;;
        -T) TRACE=true ; shift ;;
        -v) VERBOSE=$(( ${VERBOSE} + 1 )) ; shift ;;
        -V) VERSION=true ; shift ;;
        -w) WARNING_THRES=$(echo ${2} | cut -d' ' -f1) ; shift 2 ;;
        * ) 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}, warning:${WARNING_THRES}, critical:${CRITICAL_THRES}"
ECHO="${ECHO}, check_mk:${CHECK_MK}, files:${FILES}"

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
    && ( ${WARNING_THRES} == "" || ${CRITICAL_THRES} == ""
    || ! ${WARNING_THRES} =~ ^[0-9]+$ || ! ${CRITICAL_THRES} =~ ^[0-9]+$
    || ${WARNING_THRES} -le 0 || ${CRITICAL_THRES} -le 0
    || ${WARNING_THRES} -ge ${CRITICAL_THRES} ) ]] ; then
  print_usage ${APPL_NAME}
  echo "Warning threshold should be less than critical threshold."
  echo "Threshold should be greater than 0."
  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_DB_CFG="db2 get db cfg for ${DATABASE_NAME}"
  if [[ ${VERBOSE} -ge 2 ]] ; then
    echo "COMMAND: ${COMMAND_DB_CFG}"
  fi
  COMMAND_OUTPUT=$(${COMMAND_DB_CFG}) 
  while IFS= read -r LINE ; do
    LOG_PRI_GREP=$(echo ${LINE} | grep LOGPRIMARY)
    if [[ ${LOG_PRI} == "" || ${LOG_PRI_GREP} != "" ]] ; then
      LOG_PRI=$(echo ${LOG_PRI_GREP} | awk -F"= " '{print $2}' | tail -1)
    fi
    LOG_SEC_GREP=$(echo ${LINE} | grep LOGSECOND)
    if [[ ${LOG_SEC} == "" || ${LOG_SEC_GREP} != "" ]] ; then
      LOG_SEC=$(echo ${LOG_SEC_GREP} | awk -F"= " '{print $2}' | tail -1)
    fi
    LOG_SIZ_GREP=$(echo ${LINE} | grep LOGFILSIZ)
    if [[ ${LOG_SIZ} == "" || ${LOG_SIZ_GREP} != "" ]] ; then
      LOG_SIZ=$(echo ${LOG_SIZ_GREP} | awk -F"= " '{print $2}' | tail -1)
    fi
    LOG_PATH_GREP=$(echo ${LINE} | grep "Path to log files")
    if [[ ${LOG_PATH} == "" || ${LOG_PATH_GREP} != "" ]] ; then
      LOG_PATH=$(echo ${LOG_PATH_GREP} | awk -F"= " '{print $2}' | tail -1)
    fi
  done < <( printf '%s\n' "${COMMAND_OUTPUT}" | grep -i log )
  LOG_TOTAL=$(echo "${LOG_PRI}+${LOG_SEC}" | bc)
  SIZ_PRI=$((${LOG_SIZ}*${LOG_PRI}))
  SIZ_SEC=$((${LOG_SIZ}*${LOG_SEC}))
  SIZ_TOT=$((${LOG_SIZ}*${LOG_TOTAL}))

  if [[ ${VERBOSE} -ge 3 ]] ; then
    echo "RESULT LOG_PRI:'${LOG_PRI}'"
    echo "RESULT LOG_SEC:'${LOG_SEC}'"
    echo "RESULT LOG_SIZ:'${LOG_SIZ}'"
    echo "RESULT LOG_TOTAL:'${LOG_TOTAL}'"
    echo "RESULT LOG_PATH:'${LOG_PATH}'"
    echo "RESULT SIZ_PRI:'${SIZ_PRI}'"
    echo "RESULT SIZ_SEC:'${SIZ_SEC}'"
    echo "RESULT SIZ_TOT:'${SIZ_TOT}'"
  fi

  LOG_USED=$(ls -l ${LOG_PATH}*LOG 2> /dev/null | wc -l)
  LOG_WARN=$(echo "${LOG_PRI}+${WARNING_THRES}" | bc)
  LOG_CRIT=$(echo "${LOG_PRI}+${CRITICAL_THRES}" | bc)

  if [[ ${VERBOSE} -ge 3 ]] ; then
    echo "RESULT LOG_USED:'${LOG_USED}'"
    echo "RESULT LOG_WARN:'${LOG_WARN}'"
    echo "RESULT LOG_CRIT:'${LOG_CRIT}'"
  fi

  COMMAND_HADR="db2pd -db ${DATABASE_NAME} -hadr"
  if [[ ${VERBOSE} -ge 2 ]] ; then
    echo "COMMAND: ${COMMAND_HADR}"
  fi
  # Check that the command is not executed in the Standby server.
  RESULT_HARD=$(${COMMAND_HADR} | awk '/Standby/ && !/Active/ {print $1}')

  if [[ ${VERBOSE} -ge 3 ]] ; then
    echo "RESULT HADR:'${RESULT_HARD}'"
  fi

  if [[ ${RESULT_HARD} == "Standby" ]] ; then
    HADR=true
  else
    COMMAND_KBYTES="db2 -x select TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB, TOTAL_LOG_USED_TOP_KB from SYSIBMADM.LOG_UTILIZATION "
    if [[ ${VERBOSE} -ge 2 ]] ; then
      echo "COMMAND: ${COMMAND_KBYTES}"
    fi
    KBYTES=$(db2 connect to ${DATABASE_NAME} > /dev/null ; ${COMMAND_KBYTES} ; db2 connect reset > /dev/null)
    if [[ ${VERBOSE} -ge 3 ]] ; then
      echo "RESULT:'${KBYTES}'"
    fi
    ERROR_SELECT=$(echo ${KBYTES} | grep SQL0551N)
    if [[ ${ERROR_SELECT} == "" ]] ; then
      USED=$(echo ${KBYTES} | awk '{print $1}')
      AVAILABLE=$(echo ${KBYTES} | awk '{print $2}')
      TOP=$(echo ${KBYTES} | awk '{print $3}')
    else
      OUTPUT="Error in select."
      USED=0
    fi
  fi

  if [[ ${HADR} == true ]] ; then
    OUTPUT="HADR is using the whole space for logs."
    RETURN=${OK}
  else
    if [[ ${FILES} == true ]] ; then
      TYPE="Qty"
      if [[ ${LOG_USED} -eq 0 ]] ; then
        OUTPUT="Error calculating the quantity of logs."
        RETURN=${UNKNOWN}
      elif [[ ${LOG_USED} -le ${LOG_WARN} ]] ; then
        OUTPUT="The transaction log utilization is OK."
        RETURN=${OK}
      elif [[ ${LOG_USED} -gt ${LOG_WARN} && ${LOG_USED} -lt ${LOG_CRIT} ]] ; then
        OUTPUT="Please check database transaction workload (${LOG_USED} files)."
        RETURN=${WARNING}
      else
        COMMAND_LOG_ID="db2diag -readfile -gi msg:=ADM1823E"
        # The AppID has two dots in the db2diag.log file, thus they are removed
        # (i.e. It appears 2380.. and it becomes 2380)
        LOG_ID=$(${COMMAND_LOG_ID} | grep "\"." | sort | uniq | awk -F\" '{print $2}' | sed 's/\.\.//')
        LOG_COUNT=$(printf '%s\n' "${LOG_ID}" | wc -l)

        if [[ ${VERBOSE} -ge 2 ]] ; then
          echo "COMMAND: ${COMMAND_LOG_ID}"
        fi
        if [[ ${VERBOSE} -ge 3 ]] ; then
          echo "RESULT LOG_ID:'${LOG_ID}'"
          echo "RESULT LOG_COUNT:'${LOG_COUNT}'"
        fi

        if [[ ${LOG_COUNT} -ne 0 ]] ; then
          OUTPUT="There was occurrence of transaction log full due to application handle: ${LOG_ID}"
        else
          OUTPUT="Workload is reaching the limit (${LOG_USED} files)."
        fi
        RETURN=${CRITICAL}
      fi
      PERFORMANCE="'Log_files_used'=${LOG_USED};${LOG_WARN};${LOG_CRIT};;${LOG_TOTAL}"
      PERF_MK="${PERFORMANCE}"
    else
      TYPE="KB"
      WARN_VAL=$((${SIZ_PRI}+${SIZ_SEC}/2))
      if [[ ${OUTPUT} != "" ]] ; then
        RETURN=${UNKNOWN}
      elif [[ ${USED} -le ${SIZ_PRI} ]] ; then
        OUTPUT="The transaction log utilization is OK."
        RETURN=${OK}
      elif [[ ${USED} -le ${WARN_VAL} ]] ; then
        OUTPUT="Please check database transaction workload (${LOG_USED} files)."
        RETURN=${WARNING}
      else
        OUTPUT="Workload is reaching the limit (${USED})."
        RETURN=${CRITICAL}
      fi
      PERFORMANCE="'Log_qty_used'=${USED};${SIZ_PRI};${WARN_VAL};0;${SIZ_TOT}"
      LONG_OUTPUT="The top for the moment has been ${TOP}."
      LONG_PERFORMANCE="'Max_used'=${TOP}"
      PERF_MK="${PERFORMANCE}|${LONG_PERFORMANCE}"
    fi
  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} logUsage${TYPE}-${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}