Checking for the most recent database update with Python and Nagios

Hi Everyone,

Another update to kick-start this blog! I've found another script which I worked on a little over a year ago. This time its purpose was to check that we had a constant stream of new records appearing in a database table. The scripts output was designed to feed our Nagios monitoring system, but it will work just as well as a standalone or called from another script.

For those that don't know, Nagios works with exit codes and standard output from the checks it runs. It's also easy to make new, custom checks. For this to be considered a 're-usable nagios check' I should have made the script accept arguments to fill the variables I've hard coded at the top. It could also parse them to check what's been passed in is suitable. I don't have time tonight, but if someone asks nicely....

Anyway I hope you will see that writing these kind of checks can be very simple.

#!/usr/bin/python
'''
@author: HRH

A Nagios Check to ensure recent database records
'''

import sys
import datetime
import MySQLdb

# Script Variables
db_host    = "127.0.0.1"
db_user    = "root"
db_pass    = "password"
db_name    = "mydatabase"
db_table   = "mytable"
warn_secs  = 3600
error_secs = 7200
date_field = 2 # What column number contains the date we want to check (Zero indexed) 

# Make sure we can connect, if not something is wrong.
try:
    db = MySQLdb.connect(db_host, db_user, db_pass, db_name)
    db_c = db.cursor()
except:
    print "Unable to connect to database."
    sys.exit(2)

# Try and run the query for the latest data
try:    
    query = "SELECT * FROM `%s`.`%s` ORDER BY id DESC LIMIT 1 " % (db_name,db_table)
    db_c.execute(query)
    result = db_c.fetchall()[0]
except:
    print "Query Failed to select latest record from `%s`.`%s`" % (db_name,db_table)
    sys.exit(2)

latest_cdr  = result[date_field]
time_now    = datetime.datetime.now()
time_diff   = time_now - latest_cdr

# Check how long since last record and now 
if time_diff.seconds < warn_secs:
    print "New records within %d seconds" % warn_secs
    sys.exit(0)
elif time_diff.seconds < error_secs:
    print "Between %d and %d seconds since last record in DB" % (warn_secs,error_secs)
    sys.exit(1)
else:
    print "Over %d seconds since last record in DB" % error_secs
    sys.exit(2)