| Subject: |
RE: Use Zenoss to run SQL Query |
| Author: |
Jane Curry |
| Posted: |
2018-09-06 06:03 |
If it is a simple, one-off SQL query, then construct the command-line SQL command and run it through a COMMAND monitoring template.
Here is an example that I use to deliver a count of events matching an SQL query. The COMMAND template calls a little shellscript that has the SQL username and password parameterized from zProperties (these came from the ZenPacks.zenoss.MySqlMonitor ZenPack) but you could either hard-code username and password in the COMMAND template or, if you don't have that ZenPack, you might consider creating cProperties for cMySqlUsername and cMySqlPassword.
So the template looks like:
and zen42_critsev_sql.sh is:
#!/bin/sh
#set -x
# $1 is user $2 is password
user="$1"
password="$2"
# Nagios return codes
STATE_OK=0
STATE_WARNING=1
STATE_CRITICAL=2
STATE_UNKNOWN=3
#
exitstatus=$STATE_OK
# Nagios format echos information and status followed by pipe |
# followed by <var name>=<value> tuples
# Note that Zenoss datapoints must match these var names exactly
#
count=`mysql -u$user -p$password -Dzenoss_zep -h zen42.class.example.org -B -e 'select count(*) from event_summary where element_identifier="zen42.class.example.org" and severity_id=5 and status_id<2' | grep -v count`
if [ $count -eq 0 ]
then
echo "No problem count is $count | count=$count"
else
#mysql -u$user -p$password -Dzenoss_zep -h zen42.class.example.org -e 'select element_identifier, summary from event_summary where element_identifier="zen42.class.example.org" and severity_id=5' > /tmp/fred
#echo "Problem - count is $count | count=$count"
echo "Problem - count is $count `mysql -u$user -p$password -Dzenoss_zep -h zen42.class.example.org -H -e 'select element_identifier, summary from event_summary where element_identifier="zen42.class.example.org" and severity_id=5 and status_id<2'` | count=$count"
exitstatus=$STATE_WARNING
fi
exit $exitstatus
Cheers,
Jane
------------------------------
Jane Curry
Skills 1st United Kingdom
jane.curry@skills-1st.co.uk
------------------------------
