Munin contributed plugins

#!/bin/sh

#

# Plugin to graph numeric result of the specified MySQL queries.

#

# Parameters:

#

#       config   (required)

#       autoconf (optional - used by lrrd-config)

#

# Configuration example

#

# [mysql_report]

# env.names query1 query2

# env.hostname www.example.com

# env.database mysql

# env.username report

# env.password secret

# env.errorvalue 60

# env.max 120

#

# env.label_query1 Example query#1 (number of users)

# env.hostname_query1 www1.example.com

# env.draw_query1 AREA

# env.colour_query1 FF0000

# env.database_query1 mysql

# env.username_query1 user1

# env.password_query1 secret1

# env.query_query1 select count(*) from `exampledb`.`users`

# env.warning_query1 5

# env.critical_query1 8

#

# env.label_query2 Example query#2 (number of successful logins)

# env.query_query2 select count(*) from `exampledb`.`login_log`

# env.type_query2 DERIVE

# env.draw_query2 LINE2

# env.colour_query2 000080

# env.mysqlopts_query2 --defaults-extra-file=/etc/mysql/debian.cnf --port=4507

#

# env.cdefnames cdef1

# env.cdef_cdef1 result1,result2,+

# env.cdeflbl_cdef1 Users and successful logins sum

#

# $Log$

#

# Revision 1.0  2010/04/19 14:18:32 muzso@muzso.hu

# Initial version

#

# Revision 1.01 2010/7/14 oded@pageonce.com

#%# family=auto

#%# capabilities=autoconf

mysqlbin=$(which mysql)

default_errorvalue=30

default_title="Results from MySQL queries"

default_vlabel="value / sec"

default_info="This graph shows results of one or more SQL queries."

default_args="--base 1000 -l 0"

default_scale="no"

if [ "${1}" = "autoconf" ]; then

  if [ -z "${mysqlbin}" ]; then

    echo "no (missing mysql executable)"

  else

    echo "yes"

  fi

  exit 0

fi

if [ -z "${names}" ]; then

  echo "Configuration required"

  exit 1

fi

[ -n "${errorvalue}" ] || errorvalue=${default_errorvalue}

[ -n "${title}" ] || title="${default_title}"

[ -n "${vlabel}" ] || vlabel="${default_vlabel}"

[ -n "${info}" ] || info="${default_info}"

[ -n "${args}" ] || args="${default_args}"

[ -n "${scale}" ] || scale="${default_scale}"

if [ "${1}" = "config" ]; then

  cat << EOH1

graph_title ${title}

graph_args ${args}

graph_scale ${scale}

graph_vlabel ${vlabel}

graph_category db

graph_info ${info}

EOH1

  [ -n "${period}" ] && echo "graph_period ${period}"

  I=1

  for name in ${names}; do

    eval iquery='${query_'${name}'}'

    if [ -n "${iquery}" ]; then

      eval ilabel='${label_'${name}':-host${I}}'

      eval iwarning='${warning_'${name}':-${warning}}'

      eval icritical='${critical_'${name}':-${critical}}'

      eval imax='${max_'${name}':-${max}}'

      eval itype='${type_'${name}':-${type}}'

      eval idraw='${draw_'${name}':-${draw}}'

      eval icolour='${colour_'${name}':-${colour}}'

      iquery=$(echo "${iquery}" | tr '\n\r' '  ')

      cat << EOH2

result${I}.label ${ilabel}

result${I}.info Result of the query: ${iquery}

result${I}.min 0

EOH2

      [ -n "${imax}" ] && echo "result${I}.max ${imax}"

      [ -n "${itype}" ] && echo "result${I}.type ${itype}"

      [ -n "${idraw}" ] && echo "result${I}.draw ${idraw}"

      [ -n "${icolour}" ] && echo "result${I}.colour ${icolour}"

      [ -n "${iwarning}" ] && echo "result${I}.warning ${iwarning}"

      [ -n "${icritical}" ] && echo "result${I}.critical ${icritical}"

      I=$((I+1))

    fi

  done

  for cdefname in ${cdefnames} ;do

    eval icdef='${cdef_'${cdefname}'}'

    if [ -n "${icdef}" ]; then

      eval icdeflbl='${cdeflbl_'${cdefname}':-${cdeflbl}}'

      cat << EOH4

${cdefname}.cdef ${icdef}

${cdefname}.label ${icdeflbl}

EOH4

    fi

  done

  exit 0

fi

I=1

for name in ${names}; do

  eval iquery='${query_'${name}'}'

  if [ -n "${iquery}" ]; then

		eval ihostname='${hostname_'${name}':-${hostname}}'

		[ -n "${ihostname}" ] && opts="${opts} --host=${ihostname}"

		eval idatabase='${database_'${name}':-${database}}'

		eval iusername='${username_'${name}':-${username}}'

		[ -n "${iusername}" ] && opts="${opts} --user=${iusername}"

    eval ipassword='${password_'${name}':-${password}}'

    [ -n "${ipassword}" ] && opts="${opts} --password=${ipassword}"

    eval ierrorvalue='${errorvalue_'${name}':-${errorvalue}}'

    eval imysqlopts='${mysqlopts_'${name}':-${mysqlopts}}'

    iquery=$(echo "${iquery}" | tr '\n\r' '  ')

    #echo "The command to be executed: echo \"${iquery}\" | ${mysqlbin} ${imysqlopts} --batch --skip-column-names ${opts} ${idatabase} 2>&1"

    output=$(echo "${iquery}" | ${mysqlbin} ${imysqlopts} --batch --skip-column-names ${opts} ${idatabase} 2>&1)

    if [ $? -ne 0 -a ${ierrorvalue} -ne 0 ]; then

      result=${ierrorvalue}

    else

      result=$(echo "${output}" | head -n 1 | sed 's/^\([0-9]\+\).*/\1/')

    fi

    echo "result${I}.value ${result}"

    I=$((I+1))

  fi

done