Munin contributed plugins

#!/usr/bin/env python

# -*- coding: utf-8 -*-

# vim: set fileencoding=utf-8

#

# Munin plugin to show Mysql COUNT(*) results for multiple values

#

# Copyright Igor Borodikhin

#

# License : GPLv3

#

# parsed environment variables:

# host: hostname or ip-address of Mysql server (default - localhost)

# port: port number of Mysql server (default - 3306)

# user: username to access Mysql server (default - empty)

# password: password of Mysql user (default - empty)

# database: Mysql database name (default - empty)

# table: Mysql table name (no default, raises exception)

# field: field name, used in GROUP BY statement (default - empty, no group by)

# where: optional where condition (without "where", default - empty)

# only: optional; "max" or "min" to indicate that only the largest or smallest value should be graphed

#

# This plugin shows graphs of Mysql COUNT(*) results.

#

# ## Requirements

# This plugin requires pythons MySQLdb module which can be installed via easy_install.

#

# ## Installation

# Copy file to directory /usr/share/munin/pligins/ and create symbolic links for each table you wish to monitor.

# For example, if you wish to monitor how many users Mysql has per host create this symlink:

#

#     ln -s /usr/share/munin/plugins/mysql_aggregate_ /etc/munin/plugins/mysql_aggregate_user

#

# And specify some options in munin-node.conf:

#

#     [mysql_aggregate_*]

#     env.host 10.216.0.141

#     env.port 3306

#     env.user root

#     env.password vErYsEcReT

#     env.database mysql

#     env.table user

#     env.field Host

#     env.label Mysql users

#     env.vlabel users

#

#%# capabilities=autoconf

#%# family=contrib

import os, sys, MySQLdb, MySQLdb.cursors

progName = sys.argv[0]

# Parse environment variables

# Mysql host

if "host" in os.environ and os.environ["host"] != None:

    server = os.environ["host"]

else:

    server =  "localhost"

# Mysql port

if "port" in os.environ and os.environ["port"] != None:

    try:

        port = int(os.environ["port"])

    except ValueError:

        port = 3306

else:

    port = 3306

# Mysql username

if "user" in os.environ and os.environ["user"] != None:

    login = os.environ["user"]

else:

    login = ""

# Mysql password

if "password" in os.environ and os.environ["password"] != None:

    passw = os.environ["password"]

else:

    passw = ""

# Mysql database

if "database" in os.environ and os.environ["database"] != None:

    db = os.environ["database"]

else:

    db = ""

# Mysql table name

if "table" in os.environ and os.environ["table"] != None:

    table = os.environ["table"]

else:

    raise Exception("You should provide 'env.table' in configuration file")

# Mysql group by field

if "field" in os.environ and os.environ["field"] != None:

    groupBy = "GROUP BY %s" % os.environ["field"]

    field = "%s, " % os.environ["field"]

else:

    groupBy = ""

    field = ""

if "only" in os.environ and os.environ["only"] != None:

    if not field:

        raise Exception("You should provide 'env.field' in configuration file")

    only = os.environ["only"]

    if only == "max":

        dir = "DESC"

    elif only == "min":

        dir = "ASC"

    else:

        raise Exception("env.only should be 'max' or 'min'; found %s") % (only, )

    order_by = "ORDER BY COUNT(*) %s LIMIT 1" % (dir, )

else:

    order_by = ""

# Mysql where condition

if "where" in os.environ and os.environ["where"] != None:

    where = "WHERE %s" % os.environ["where"]

else:

    where = ""

# Mysql connection handler

conn = None

# Query to get field values (used only when graphing several values)

valuesQuery = "SELECT DISTINCT %s 1 FROM %s %s" % (field, table, where)

# Query to get graph data

aggregateQuery = "SELECT %sCOUNT(*) FROM %s %s %s %s" % (field, table, where, groupBy, order_by)

# Connect to mysql

try:

    conn = MySQLdb.connect(host=server, user=login, passwd=passw, db=db)

    cursor = conn.cursor()

except MySQLdb.Error, e:

    print "Error %d: %s" % (e.args[0], e.args[1])

    sys.exit(1)

single_value = field == "" or order_by != ""

# init values tuple

if not single_value:

    values = {}

    cursor.execute(valuesQuery)

    results = cursor.fetchall()

    for result in results:

        values[result[0]] = 0

if len(sys.argv) == 2 and sys.argv[1] == "autoconf":

    print "yes"

elif len(sys.argv) == 2 and sys.argv[1] == "config":

    if "label" in os.environ:

        label = os.environ["label"]

    else:

        label = "Aggregate - %s" % table

    if "vlabel" in os.environ:

        vlabel = os.environ["vlabel"]

    else:

        vlabel = "count(*)"

    if single_value:

        print "graph mysql_aggregate_%s" % table

        print "graph_title %s" % label

        print "graph_vlabel %s" % vlabel

        print "graph_category mysql"

        print ""

        if "only" in os.environ:

            print "values_count.label %s" % (os.environ["only"], )

        else:

            print "values_count.label count"

    else:

        print "multigraph mysql_aggregate_%s" % table

        print "graph_title %s" % label

        print "graph_vlabel %s" % vlabel

        print "graph_category mysql"

        print ""

        for key in values.keys():

            print "%s_count.label %s" % (key.replace(".", "_"), key.replace(".", "_"))

        for key in values.keys():

            print ""

            print "multigraph mysql_aggregate_%s.%s" % (table, key.replace(".", "_"))

            print "graph_title %s, value %s" % (label, key.replace(".", "_"))

            print "graph_vlabel %s" % vlabel

            print "graph_category mysql"

            print ""

            print "%s_count.label %s" % (key.replace(".", "_"), key)

            print ""

else:

    try:

        cursor.execute(aggregateQuery)

        if single_value:

            result = cursor.fetchone()

            count = 0

            if field:

                ind = 1

            else:

                ind = 0

            if result[ind]:

                count = count + result[ind]

            print "values_count.value %s" % count

        else:

            results = cursor.fetchall()

            for result in results:

                values[result[0]] = result[1]

            print "multigraph mysql_aggregate_%s" % table

            for key in values.keys():

                print "%s_count.value %s" % (key.replace(".", "_"), values[key])

            for key in values.keys():

                print ""

                print "multigraph mysql_aggregate_%s.%s" % (table, key.replace(".", "_"))

                print "%s_count.value %s" % (key.replace(".", "_"), values[key])

    except MySQLdb.Error, e:

        print "Error %d: %s" % (e.args[0], e.args[1])

if conn:

    conn.close()