sybase_space

#!@@PERL@@ -w
# -*- perl -*-

=head1 NAME

sybase_space - Plugin to monitor sybase database space usage

=head1 CONFIGURATION

You need to add the user to all the databases you want monitored.

Configuration variables:

 SYBASE      - Sybase home
 SYBASE_USER - Username
 SYBASE_PASS - Password
 SYBASE_HOST - Host

=head1 MAGIC MARKERS

 #%# family=manual
 #%# capabilities=

=head1 AUTHOR

Copyright (C) 2003-2004 Jimmy Olsen

=head1 LICENSE

This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; version 2 dated June, 1991.

This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
General Public License for more details.

You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
02110-1301 USA.

=cut

use strict;
use DBD::Sybase;
use DBI;

my $user = $ENV{SYBASE_USER} || "monitor";
my $pass = $ENV{SYBASE_PASS} || "monitor";
my $host = $ENV{SYBASE_HOST} || "localhost";
my $db   = $ENV{SYBASE_DB}   || "master";

$ENV{SYBASE} = $ENV{SYBASE}  || "/usr/local/sybase";

my $dbh = DBI->connect ("dbi:Sybase:$db;host=$host", $user, $pass);

if ($ARGV[0] eq "autoconf")
{
    if (!$dbh)
    {
	print "no (Could not connect to database.)\n";
	exit 0;
    }
    print "yes\n";
    exit 0;
}

if (!$dbh)
{
	die "Could not run DBI::connect\n";
}

my $databases = &list_dbs ($dbh);

if (defined $ARGV[0] and $ARGV[0] =~ /^config$/)
{
	print "host_name sybase-i.fileflow.com\n";
	print "graph_title Sybase database space usage\n";
	print "graph_args -u 100 -l 0\n";
	print "graph_vlabel %\n";
	print "graph_category sybase\n";

	foreach my $db (keys %{$databases})
	{
		print "$db.label $db\n";
		print "$db.type GAUGE\n";
		print "$db.warning 85\n";
		print "$db.critical 95\n";
	}
	exit 0;
}

my $db_info;

foreach my $db (keys %{$databases})
{
	$db_info = &space_db ($dbh, $db, $db_info);
}

foreach my $db (keys %{$db_info})
{
	#print "$db $db_info->{$db}->{used} / $db_info->{$db}->{total} = ", $db_info->{$db}->{used}*100/$db_info->{$db}->{total}, ".\n";
	print "$db.value ", $db_info->{$db}->{used}*100/$db_info->{$db}->{total}, "\n";
}

1;

sub list_dbs
{
	my $h   = shift;
	my $dbs = undef;

	if (! $h->do ("use master"))
	{
		die "Error: could not \"use master\"...\n";
	}
	my $sth = $dbh->prepare ("select name from sysdatabases");
	my $rv  = $sth->execute;
	if (! $rv)
	{
		die "Error: could not run \"select name from sysdatabases\"...\n";
	}

	$dbs = $sth->fetchall_hashref ("name");

	return $dbs;
}

sub space_db
{
	my $h   = shift;
	my $db  = shift;
	my $dbs = shift;

	if (! $h->do ("use $db"))
	{
		die "Error: could not \"use $db\"...\n";
	}
	my $sth = $dbh->prepare ("sp_spaceused");
	my $rv  = $sth->execute;

	if (! $rv)
	{
		die "Error: could not use \"sp_spaceused\"...\n";
	}

	do {
		while (my $d = $sth->fetchrow_arrayref)
		{
			#print join ('|',@{$d}), "...\n";
			if ($d->[0] =~ /^$db$/)
			{
				$dbs->{$db}->{total} = &bytes ($d->[1]);
			}
			elsif (!$d->[0])
			{
				next;
			}
			else
			{
				$dbs->{$db}->{used} = &bytes ($d->[0]);
			}
		}
	} while ($sth->{syb_more_results});

	return $dbs;
}

sub bytes
{
	my $val = shift;

	if ($val =~ /^\s*([\d\.]+)\s*kb\s*$/i)
	{
		$val = $1*1024;
	}
	elsif ($val =~ /^\s*([\d\.]+)\s*mb\s*$/i)
	{
		$val = $1*1024*1024;
	}
	elsif ($val =~ /^\s*([\d\.]+)\s*gb\s*$/i)
	{
		$val = $1*1024*1024*1024;
	}
	return $val;
}


# vim:syntax=perl