check_db_record

nagios plugin to check if a database record has been created today based on specific criteria
author: Sergei Haramundanis 18-May-2006

usage: check_db_record access_file min_time max_time db_table time_column return_column "criteria_1" "criteria_2" "criteria_3" "criteria_4"

Description:

This plugin will check if a database record has been created today within a timeframe (inclusive) specified by min_time and max_time
(in HHMM format) by comparing the current time with the returned value of the time_column specified, and matching the criteria specified
(used to build the where clause)

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)

criteria_1 is required
criteria_2, criteria_3 and criteria_4 are optional

contents of access_file must contain database connection information in the following format:

USERNAME username
PASSWORD password
CONNECTION_STRING connection_string

this is used by sqlplus to login to the database and execute the appropriate sql query based on criteria specified

Output:

During any run of the plugin, it will check db_table for the existence of a record matching the specified criteria

if none exist and max_time is exceeded it will return a CRITICAL state with the message:

[CRITICAL] 0 records created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 (not created by max_time of max_time)

if one exists and time_column is less than min_time it will return a CRITICAL state with the message:

[CRITICAL] detected record with return_column="value" created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 (created prior to min_time of min_time)

if more than one exist it will return a CRITICAL state with the message:

[CRITICAL] detected n records created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 (too many records)

if one exists and time_column is between min_time and max_time it will return an OK state with the message:

[OK] detected 1 record created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 between min_time and max_time return_column="value" (passed criteria)

if one exists and time_column is greater than max_time it will return a CRITICAL state with the message:

[CRITICAL] detected 1 record created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 return_column="value" (created outside of timeframe min_time min_time and max_time max_time)

if none exist and max_time is not exceeded it will return an OK state with the message:

[OK] 0 records created today in db_table with criteria_1 criteria_2 criteria_3 criteria_4 (max_time of max_time not exceeded)

10-Nov-2006 version 1.1 update includes:
1. ORACLE_HOME definition for oracle 10.2.0
2. TNS_ADMIN oracle environment variable for directory of tnsnames.ora
3. returning elapsedTimeSecs performance data