mysql_aggregate_

#!/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 db"
        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 db"
        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 db"
            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()