Oracle SQL Query Output In Nagios

[CloudOps]

Hi All,

We want to check SQL query from nagios and return output . Can anyone guide me on this?

Thanks,

[eloyd]

Very fast and stupid check_sql_query script:

Code: Select all

#!/bin/sh
warn=""
crit=""
user="username"
pass="password"
database="database"
host="hostname"

while [ -n "$1" ]; do
  case "1" in
    -u) user="$2"; shift 2;;
    -p) pass="$2"; shift 2;;
    -d) database="$2"; shift 2;;
    -h) host="$2"; shift 2;;
    -w) warn="$2"; shift 2;;
    -c) crit="$2"; shift 2;;
    *) shift 1;;
  esac
done

[ -z "$warn" ] && echo "No warning specified." && exit 3
[ -z "$crit" ] && echo "No critical specified." && exit 3

output=`echo "SELECT COUNT(*) FROM some_table" | mysql -u${user} -p${pass} -h$host} ${database} | tail -1`

if [ $num -ge $crit ]; then
  echo "CRITICAL: Result was $output|result=$output"
  exit 2
elif [ $num -ge $warn ]; then
  echo "WARNING: Result was $output|result=$output"
  exit 1
else
  echo "OK: Result was $output|result=$output"
  exit 0
fi

Now you can call it by passing in the appropriate parameters. Change to reflect your particular SQL and stuff, but there you go. Instant check_sql_query plugin.

[mcapra]

Thanks for the free plugin @eloyd!

@CloudOps have you tried using the "Oracle Query" configuration wizard? You will need to install the Oracle Client/Plugins before doing so:
https://assets.nagios.com/downloads/nag ... ios-XI.pdf

To get to the configuration wizards in Nagios XI, hover over "Configure" from the top menu followed by the "Configuration Wizards" option.

[eloyd]

No problem. I forgot to add my "easy peasy, lemon squeezy" tag line.

[mcapra]

@CloudOps let us know if you have trouble with the wizard!

[CloudOps]

Very fast and stupid check_sql_query script:

Code: Select all

#!/bin/sh
warn=""
crit=""
user="username"
pass="password"
database="database"
host="hostname"

while [ -n "$1" ]; do
  case "1" in
    -u) user="$2"; shift 2;;
    -p) pass="$2"; shift 2;;
    -d) database="$2"; shift 2;;
    -h) host="$2"; shift 2;;
    -w) warn="$2"; shift 2;;
    -c) crit="$2"; shift 2;;
    *) shift 1;;
  esac
done

[ -z "$warn" ] && echo "No warning specified." && exit 3
[ -z "$crit" ] && echo "No critical specified." && exit 3

output=`echo "SELECT COUNT(*) FROM some_table" | mysql -u${user} -p${pass} -h$host} ${database} | tail -1`

if [ $num -ge $crit ]; then
  echo "CRITICAL: Result was $output|result=$output"
  exit 2
elif [ $num -ge $warn ]; then
  echo "WARNING: Result was $output|result=$output"
  exit 1
else
  echo "OK: Result was $output|result=$output"
  exit 0
fi

Now you can call it by passing in the appropriate parameters. Change to reflect your particular SQL and stuff, but there you go. Instant check_sql_query plugin.

Hi Eloyd,

Can you tell me the commands configuration and usage ? also it will work on oracle?

[eloyd]

That plugin I wrote uses mysql command line to execute a query, capture the output, parse it, and determine the state for Nagios's plugin return codes. You'll need to modify it to use the appropriate SQL linux client (running on the Nagios server) to connect to your database, capture the output, parse it, and figure out the proper exit codes. Sorry, but I really can't provide more help than I've already done. It was just an example of what you could do for a real quick service check.

I'm sure there's something on the Nagios Exchange for better Oracle SQL checking, but if you do end up writing your own, you will want to look at the Nagios Plugin API reference available at https://assets.nagios.com/downloads/nag ... inapi.html.

@mcapra also had a good idea a few notes back.

[rkennedy]

Thanks @eloyd!

@CloudOps - let us know if you have any further questions.

[CloudOps]

Thanks @eloyd!

@CloudOps - let us know if you have any further questions.

Hi rkennedy,

I checked oracle query wizard, but it has mentioned that output should be only integer not string. Can you help with small script from which i can get output in nagios.

[rkennedy]

Can you show us a screenshot as an example?