#!/bin/sh
#
# Plugin to monitor AlternC accounts with high bandwidth usage.
#
# Parameters:
# 	
# 	config
# 	autoconf
#
# Configuration variables
#
#	mysqlopts    - Options to pass to mysql which should include the
#  			username, password, host and alternc database.
#
# Example of the entry in plugin-conf.d/munin-node
#	[alternc*]
#	user root
#	env.mysqlopts --defaults-file=/etc/mysql/alternc.cnf
#
# $Log$
# Revision 0.1  Sat Aug  4 15:55:27 ART 2007 sebas
# On part! Ce plugin est base sur mysql_thread.
#
#
#%# family=contrib
#%# capabilities=autoconf

MYSQLOPTS="$mysqlopts"
MYSQL="${mysql:-mysql}"

# NOTE: we use only part of the username for privacy and security reasons
LIST_USERS_QUERY="select CONCAT('membre', m.uid, '.label'), CONCAT(m.uid, '(', SUBSTRING(m.login, 1, 2), ')') stat_http as s left join membres as m on (m.uid = s.uid) where day > DATE_SUB(NOW(), INTERVAL 30 DAY) group by s.uid order by sum(size) desc limit 15"

if [ "$1" = "autoconf" ]; then
        $MYSQL --version 2>/dev/null >/dev/null
        if [ $? -eq 0 ]
        then
                $MYSQL $MYSQLOPTS -N -B -e "$LIST_USERS_QUERY" 2> /dev/null >&2
                if [ $? -eq 0 ]
                then
                        echo yes
                        exit 0
                else
                        echo "no (could not connect to mysql)"
                fi
        else
                echo "no (mysql client not found)"
        fi
        exit 1
fi

if [ "$1" = "config" ]; then
	echo 'graph_title AlternC - high bandwidth usage'
	echo 'graph_vlabel quantity'
	echo 'graph_category alternc'

	$MYSQL $MYSQLOPTS -N -B -e "$LIST_USERS_QUERY" 2>/dev/null
	exit 0
fi

# NOTE: we divice by 1024/1024/1024 so that values appear as Gigs and not as bytes.
$MYSQL $MYSQLOPTS -N -B -e "select CONCAT('membre', m.uid, '.value'), round(sum(size) / 1024 / 1024 / 1024,2) from stat_http as s left join membres as m on (m.uid = s.uid) where day > DATE_SUB(NOW(), INTERVAL 30 DAY) group by s.uid order by sum(size) desc limit 15"