#!/usr/bin/perl
# check_sybase
# A Nagios plugin that connects to a Sybase database and checks free space.
#
# Tell nagios v3 not to use the Embedded Perl Interpreter (ePN):
# nagios: -epn
# If you are running nagios v2, you can disable ePN by prefixing your 
# check_sybase command with "perl ", e.g. change:
#  command_line    $USER1$/check_sybase ...
# to:
#  command_line    perl $USER1$/check_sybase ...
#
# Copyright 2004-2007 NetMan Network Management and IT Services GmbH
# Authors(s): Simon Bellwood
# Portions Copyright 2001 Michael Peppler.
# License: GPLv2
#
# Bugs and feedback to simon.bellwood@NOSPAM.net-man.at
# Latest version available from:
#    http://www.net-man.at/software/check_sybase-LATEST.zip
#
# Revision history:
# 0.1  SB  01-OCT-2004  Initial version.
# 0.2  SB  08-NOV-2004  Initial release.
# 0.3  SB  13-JAN-2005  Fixed lib path, improved timeouts.
# 0.4  SB  26-JAN-2005  Added loginTimeout.
# 0.5  SB  04-FEB-2005  Fixed dates in history. Oops.
# 0.6  SB  29-MAR-2005  Added --explain option.
# 0.7  SB  08-APR-2005  Added initial performance data support.
# 0.8  SB  17-JUL-2005  Added support for NUMLOGINS checktype.
#                       Added --fallback option.
# 0.9  SB  12-JUL-2006  Added --no-mixed-devices-warning to 
#                       suppress new warning about mixed devices.
# 1.0  SB  20-DEC-2006  Added charset support.
# 1.1  SB  29-AUG-2007  Disabled use of Embedded Perl Interpreter.
#                       Clarified license to match Nagios (GPL->GPLv2)
my $VERSION = "1.1";

#use warnings;
use strict;
use DBI;
use Getopt::Long;
use lib qw( /var/local/opmon/libexec/ );
#use lib qw( /usr/lib/nagios/plugins/ /usr/local/nagios/libexec/ );
use utils qw(%ERRORS &print_revision &support &usage $TIMEOUT);


my $PROGNAME = "check_sybase";
my $DEFAULT_CHECKTYPE = "FREESPACE";
my $DEFAULT_WARNING   = "25";
my $DEFAULT_CRITICAL  = "10";
my $DEFAULT_TIMEOUT   = "30";
my $DEFAULT_CHARSET   = "iso_1";
my $DEFAULT_PORT      = "5000";

my ($user, $pass, $dbport, $dbsvr, $dbname, $config, $checktype, $charset, $explain, 
    $fallback, $no_mixed_devs_warn, $warn, $crit, $timeout, $help, $version);

my $options_okay = GetOptions(
	"U|user=s"	=> \$user,
	"P|pass:s"	=> \$pass, # ":" means optional
	"X|port:s"	=> \$dbport,
	"S|dbsvr=s"	=> \$dbsvr,
	"D|dbname=s"	=> \$dbname,
	"config=s"	=> \$config,
	"checktype=s"	=> \$checktype,
	"charset=s"	=> \$charset,
	"explain"	=> \$explain,
	"fallback"	=> \$fallback,
	"no-mixed-devices-warning"	=> \$no_mixed_devs_warn,
	"w|warning=i"	=> \$warn,
	"c|critical=i"	=> \$crit,
	"t|timeout=i"	=> \$timeout,
	"h|help"	=> \$help,
	"V|version"	=> \$version
);


if (! $options_okay) # Bad option passed
{
	&help;
	&nunk("Bad command line option passed!");
}

# Use defaults, if needed
$dbport = $dbport || $DEFAULT_PORT;
$warn = $warn || $DEFAULT_WARNING;
$crit = $crit || $DEFAULT_CRITICAL;
$checktype = $checktype || $DEFAULT_CHECKTYPE;
$timeout = $timeout || $TIMEOUT || $DEFAULT_TIMEOUT;
$charset = $charset || $DEFAULT_CHARSET;
$no_mixed_devs_warn = $no_mixed_devs_warn || 0;

if ($help)
{
	&help;
	&nok;
}

if ($version)
{
	print_revision($PROGNAME,"\$Revision: $VERSION \$");
	&nok;
}

if ($config) # Read any of "user", "pass", "dbsvr", "dbname" from config file
{
	&read_config;
}

# Some more descriptive syntax checks
my $syntax_error;
$syntax_error .= "No dbsvr given! " unless $dbsvr;
$syntax_error .= "No dbname given! "
	if (!$dbname && $checktype ne "NUMLOGINS");
$syntax_error .= "NUMLOGINS checktype cannot take a dbname! "
	if ($dbname && $checktype eq "NUMLOGINS");
$syntax_error .= "No user given! " unless $user;
$syntax_error .= "Bad checktype given!"
	unless $checktype =~ m/^(CONNECT|FREESPACE|NUMLOGINS)$/;
&nunk($syntax_error) if $syntax_error;


# Just in case of problems, let's not hang Nagios
$SIG{'ALRM'} = \&alarm;
alarm($timeout);

# Decide on what we are checking
if ($checktype eq "CONNECT")
{
	&connect;
}
elsif ($checktype eq "FREESPACE")
{
	&check_space;
}
elsif ($checktype eq "NUMLOGINS")
{
	&check_numlogins;
	$dbname = "master";
}

my $dbh;
my $is_connected;
sub connect
{
	$dbh = DBI->connect("dbi:Sybase:host=$dbsvr;database=$dbname;port=$dbport".
	               "charset=$charset;timeout=$timeout;".
		       "loginTimeout=$timeout", $user, $pass)
		or &ncrit("Could not connect to '$dbname' on '$dbsvr'");
	
	$is_connected++;

	# Report success for a check of type CONNECT
	&nok("Connect okay") if ($checktype eq "CONNECT");
}

sub disconnect
{
	$dbh->disconnect if $is_connected;
	$is_connected = 0;
}

sub alarm
{
	# Our check didn't finish in the required time.

	# If --fallback was given, and we could connect, then return an OK
	if ($fallback && $is_connected)
	{
		&nok("Connect okay (fellback to CONNECT check)");
	}

	# Otherwise return a timeout error
	&nunk("Timeout: no response from dbsvr $dbsvr within $timeout seconds");
}

sub check_space
{
	&connect;

	# Most of this sub based on Michael Peppler's check-space.pl
	# For debugging purposes, more values are collected than needed.

	$dbh->{syb_do_proc_status} = 1;

	my $dbinfo;

	# First check space in the database
	my $sth = $dbh->prepare("sp_spaceused")
		or &nunk("Failed to call sp_spaceused on '$dbsvr'");
	$sth->execute
		or &nunk("Failed to call sp_spaceused on '$dbsvr'");
	do {
		while (my $d = $sth->fetch)
		{
			if ($d->[0] =~ /$dbname/)
			{
				# Grab "database_size"
				$d->[1] =~ s/[^\d.]//g;
				$dbinfo->{size} = $d->[1];
			}
			else
			{
				# Reserved, data, index, unused
				foreach (@$d)
				{
					s/\D//g;
				}

				# Grab "reserved", "data", "index"
				$dbinfo->{reserved} = $d->[0] / 1024;
				$dbinfo->{data} = $d->[1] / 1024;
				$dbinfo->{index} = $d->[2] / 1024;
				$dbinfo->{unused} = $d->[3] / 1024;
			}
		}
	} while($sth->{syb_more_results});

	&explain("db size:  ".$dbinfo->{size});
	&explain("reserved: ".$dbinfo->{reserved});
	&explain(" data:    ".$dbinfo->{data});
	&explain(" index:   ".$dbinfo->{index});
	&explain(" unused:  ".$dbinfo->{unused});

	# Get the actual device usage from sp_helpdb to get the free log space
	$sth = $dbh->prepare("sp_helpdb $dbname")
		or &nunk("Failed to call sp_helpdb $dbname on '$dbsvr'");
	$sth->execute
		or &nunk("Failed to call sp_helpdb $dbname on '$dbsvr'");
	do {
		while (my $d = $sth->fetch)
		{
			# Look for "usage" column with value "log only"
			if($d->[2] && $d->[2] =~ /log only/)
			{
				# Grab "size", add it to our log size
				$d->[1] =~ s/[^\d\.]//g;
				$dbinfo->{log} += $d->[1];
			}

			# Look for "device fragments" column with "log only"
			# followed by a number.
			if($d->[0] =~ /log only .* (\d+)/)
			{
				$dbinfo->{logfree} = $1 / 1024;
			}

			# Check for mixed data and log devices
			if($d->[2] =~ /data and log/ && $dbname ne "master")
			{
				$sth->finish();
				&nwarn("You have mixed data and log devices. ".
					"Space caculation is likely to be ".
					"inaccurate. You can either disable ".
					"this warning with the switch ".
					"--no-mixed-devices-warning, or you ".
					"can fix your devices. E-mail the ".
					"author if you need help doing this.")
				unless $no_mixed_devs_warn;
			}
		}
	} while ($sth->{syb_more_results});

	&explain("log: ".$dbinfo->{log});
	&explain("logfree: ".$dbinfo->{logfree});

	# Subtract the log size from the database size
	$dbinfo->{realsize} = $dbinfo->{size} - $dbinfo->{log};
	&explain("realsize (i.e. size - log) = ".$dbinfo->{realsize});

	# The "reserved" space is free for use by the table that freed it, so 
	# it is not truly free space. To be safe, our calculation ignores it.
	my $free = ($dbinfo->{realsize} - $dbinfo->{reserved})/$dbinfo->{realsize};
	$free = sprintf("%.2f", $free*100);

	&explain("(realsize-reserved)/realsize = $free%");
	&explain("For safety, this calculation assumes no log space reuse. ".
	"Because of this, you may get negative values.");


	if ($free < $crit)
	{
		&ncrit("Free space is $free%! (critical threshold is $crit%)".
			"|free_space=$free%");
	}

	if ($free < $warn)
	{
		&nwarn("Free space is $free%! (warning threshold is $warn%)".
			"|free_space=$free%");
	}


	&nok("Free space within thresholds ($free% free)".
		"|free_space=$free%");
}

sub read_config
{
	open (CONFIG, "<$config")
		or &nunk("Failed to open config file '$config': $!");
	while (<CONFIG>)
	{
		chomp;
		next if m/^#/; # skip comments
		next if m/^$/; # skip blanks

		# Each case-insensitive argument can be followed by an optional
		# colon, then must be followed by whitespace and the value.
		# Options in the config file override those given on the 
		# command line, but don't rely on this!

		if    (m/USER:?\s+(\S+)/i)
		{
			$user = $1;
		}
		elsif (m/PASS:?\s+(\S+)/i)
		{
			$pass = $1;
		}
		elsif (m/DBSVR:?\s+(\S+)/i)
		{
			$dbsvr = $1;
		}
		elsif (m/DBNAME:?\s+(\S+)/i)
		{
			$dbname = $1;
		}
		else
		{
			&nunk("Invalid line $. in config file '$config'");
		}
	}
	close (CONFIG);
}

sub check_numlogins
{
	&connect;

	my $count = 0;
	my $sth = $dbh->prepare("select count(*) - 1 from ".
		"master..sysprocesses where suid != 0")
	        or &nunk("Failed to prepare logins query on '$dbsvr'");
	$sth->execute
	        or &nunk("Failed to execute logins query on '$dbsvr'");
	while (my $d = $sth->fetch)
	{
	        $count = int($d->[0]) if (int($d->[0]));
	}

	if ($count >= $crit)
	{
	        &ncrit("Number of logins is $count! (critical threshold ".
			"is $crit)|logins=$count");
	}
	elsif ($count >= $warn)
	{
	        &nwarn("Number of logins is $count! (warning threshold ".
			"is $warn)|logins=$count");
	}

	&nok("Number of logins within thresholds ($count logins)".
	        "|logins=$count");
}

sub help
{
	print <<_HELP_;
Usage: $PROGNAME OPTIONS
A Nagios plugin that connects to a Sybase database and checks free space.

Mandatory arguments to long options are mandatory for short options too.
  -U, --user		Username to connect to database.
  -P, --pass		Password to connect to database.
  -S, --dbsvr		Database server (as in the interfaces file).
  -D, --dbname		Database name to check.
  --config=FILE		Config file (see SECURITY below)
  --checktype=TYPE	Type of check to run (see TYPEs below)
  --charset=CHARSET	Character set of server (default $DEFAULT_CHARSET).
  --explain		Explains how we calculated the free space.
  --fallback		Fallback to a CONNECT check if a non-CONNECT check
  			takes too long.
  --no-mixed-devices-warning	Suppress the warning about mixed data/log
				devices.
  -w, --warning		Warning threshold (default $DEFAULT_WARNING).
  -c, --critical	Critical threshold (default $DEFAULT_CRITICAL).
  -t, --timeout		Timeout value, in seconds (default $DEFAULT_TIMEOUT).
  -h, --help		This help message
  -V, --version		Version information ($VERSION)

Examples:
	$PROGNAME -U sa -P secret -S bigbox -D orders
	$PROGNAME --config=/secure/nagios-sybase.cfg --checktype=CONNECT

TYPEs
 There are three types of checks you can run:
 --checktype=CONNECT
    Checks just the connection to the database.
 --checktype=FREESPACE
    (Default) Checks both the connection to the database and the free space.
    With this check type, both warning and critical thresholds are in percent.
    Warning defaults to 25% free space, critical defaults to 10% free space.
 --checktype=NUMLOGINS
    Checks the number of logins connected to the database server.
    With this check type, the warning and critical thresholds are integers.

SECURITY - Using a config file
 Since a "ps ax" will reveal your database username and password, you can 
 instead specify them in a config file. Pass the config file with --config.
 The format of the file is:
   USER     value
   PASS     value
 You can also specify a DBSVR and DBNAME in the file. Comments (#) and blank
 lines are ignored. Use whitespace to separate argument and value. The file
 should be readable by as few people as possible.
_HELP_

}

sub explain
{
	return unless $explain;

	my $msg = shift;
	print "$msg\n";
}



# Some wrappers..

# Returns code 0, OK
sub nok
{
	my $msg = shift;
	print "OK: $msg\n" if $msg;

	&disconnect;
	exit $ERRORS{OK};
}

# Returns code 1, Warning
sub nwarn
{
	my $msg = shift;
	print "WARNING: $msg\n";

	&disconnect;
	exit $ERRORS{WARNING};
}

# Returns code 2, Critical
sub ncrit
{
	my $msg = shift;
	print "CRITICAL: $msg\n";

	&disconnect;
	exit $ERRORS{CRITICAL};
}

# Returns code 3, Unknown
sub nunk
{
	my $msg = shift;
	print "ERROR: $msg\n";

	&disconnect;
	exit $ERRORS{UNKNOWN};
}