A service check groovy script to query a database for Opsview

check_sql_query.groovy

/**
 * Copying and distribution of this file, with or without modification,
 * are permitted in any medium without royalty provided the copyright
 * notice and this notice are preserved.  This file is offered as-is,
 * without any warranty.
 *
 * Custom SQL check (invoked using check_by_ssh) to ensure app server can query 
 * the database. Used with Groovy 1.5.6 on Debian Lenny against SQL Server.
 * 
 * Opsview users should use check_sql_advanced for MySQL/PostgreSQL/Oracle or 
 * consider check_sql as an alternative.
 *
 * @author Robin Bramley, Opsera Limited (c) 2010
 */

import java.sql.SQLException

// consts
final int OK = 0
final int WARNING = 1
final int CRITICAL = 2
final int UNKNOWN = 3

def retCode = UNKNOWN

try {
    // requires JDBC driver in ~nagios/.groovy/lib (or an appropriate Grab)
    // e.g. jtds-1.2.5.jar
    def sql = groovy.sql.Sql.newInstance(
        "jdbc:jtds:sqlserver://acmedb:1433/ACME",
        "acme",
        "password_goes_here",
        "net.sourceforge.jtds.jdbc.Driver")

    // check the Liquibase change log table
    def query = "select count(*) from DATABASECHANGELOG"

    // query list
    def row = sql.firstRow(query)
    if (!row) {
        println "WARNING: Nothing came back"
        retCode = WARNING
    } else {
        // we'll get an anonymous map [:9]
        if (row.collect {k,v -> v }[0] < 1) {
            println "WARNING: No results from ${query}"
            retCode = WARNING
        } else {
            println "OK: Results from ${query} = ${row}"
            retCode = OK
        }
    }
} catch (SQLException sqle) {
    println "CRITICAL: Problem: ${sqle.message}"
    retCode = CRITICAL
}

System.exit(retCode)