Munin contributed plugins

#!/usr/bin/perl -w

# Plugin for monitor oracle connections.

#

# Licenced under GPL v2.

#

# Usage:

#

#       Symlink into /etc/munin/plugins/ and add the monitored

#       database to the filename. e.g.:

#

#       ln -s /usr/share/munin/plugins/oracle__connections \

#         /etc/munin/plugins/oracle_<databasename>_connections

#       This should, however, be given through autoconf and suggest.

#

#       If required, give username, password and/or oracle server

#       host through environment variables.

#

#

# Parameters:

#	autoconf

#       config   (required)

#

# Config variables:

#

#       dbhost     - Which database server to use. Defaults to

#                    'localhost'.

#       dbname     - Which database to use. Defaults to orcl

#       dbuser     - A oracle user account with read permission to

#                    the v$session view. Defaults to

#                    'oracle'. Anyway, Munin must be told which user

#                    this plugin should be run as.

#       dbpass     - The corresponding password, if

#                    applicable. Default to undef.

#

#	showusers  - If set to 1 show usernames and num. of connections.

#		     Default is not show users (0).

# Magic markers

#%# family=auto

#%# capabilities=autoconf

use strict;

use DBI;

my $dbhost = $ENV{'dbhost'} || '127.0.0.1';

my $dbname = $ENV{'dbname'} || 'orcl';

my $dbuser = $ENV{'dbuser'} || 'oracle';

my $dbport = $ENV{'dbport'} || '1521';

my $dbpass = $ENV{'dbpass'} || '';

my $showusers = $ENV{'showusers'} || '0';

# Check for DBD::Oracle

if (! eval "require DBD::Oracle;") {

     exit 1;

}

my $dsn = "DBI:Oracle:dbname=$dbname;host=$dbhost;port=$dbport;sid=$dbname";

#print "$dsn\n";

my $dbh = DBI->connect ($dsn, $dbuser,

			$dbpass,

			{RaiseError =>1}) || die "";

if (exists $ARGV[0]) {

    if ($ARGV[0] eq 'autoconf') {

	# Check for DBD::Oracle

	if (! eval "require DBD::Oracle;") {

	     print "no (DBD::Oracle not found)";

	     exit 1;

	}

        if ($dbh) {

            print "yes\n";

            exit 0;

        } else {

            print "no Unable to access Database $dbname on host $dbhost as user $dbuser.\nError returned was: ". $DBI::errstr;

            exit 1;

	}

    }

    if ($ARGV[0] eq "config") {

        my $sql_max = "select value from v\$parameter where name = 'sessions'";

        my $sth_max = $dbh->prepare($sql_max);

        $sth_max->execute();

        my ($max_connections) = $sth_max->fetchrow();

        my $warning = int ($max_connections * 0.7);

        my $critical = int ($max_connections * 0.8);

        print "graph_title Oracle active connections from $dbname\n";

        print "graph_args -l 0 --base 1000\n";

        print "graph_vlabel Connections\n";

        print "graph_category db\n";

        print "graph_info Shows active oracle connections from $dbname\n";

        print "graph_scale no\n";

	if ( $showusers ) {

		my $sql = "select username, count(username) from v\$session where username  is not null group by username";

		my $sth = $dbh->prepare($sql);

		$sth->execute();

		my $setarea = "yes";

		while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) {

	       		print "$datname.label $datname active connections\n";

       			print "$datname.info $datname active connections\n";

        		print "$datname.type GAUGE\n";

			if ($setarea eq "yes") {

        			print "$datname.draw AREA\n";

				$setarea="";

        		} else {

				print "$datname.draw STACK\n";

			}

		}

	} else {

	       	print "connections.label active connections\n";

       		print "connections.info active connections\n";

        	print "connections.type GAUGE\n";

        	print "connections.warning $warning\n";

        	print "connections.critical $critical\n";

	}

	print "total.label active connections\n";

       	print "total.info active connections\n";

        print "total.type GAUGE\n";

        print "total.warning $warning\n";

        print "total.critical $critical\n";

        print "max_connections.label Max. connections\n";

        print "max_connections.info Max. connections\n";

        print "max_connections.type GAUGE\n";

	exit 0;

    }

}

my $sql_max = "select value from v\$parameter where name = 'sessions'";

my $sth_max = $dbh->prepare($sql_max);

$sth_max->execute();

my ($max_connections) = $sth_max->fetchrow();

if ( $showusers ) {

	my $sql = "select username, count(username) from v\$session where username  is not null group by username";

	my $sth = $dbh->prepare($sql);

	$sth->execute();

	my $total = 0;

	while ( my ($datname,$curr_conn) = $sth->fetchrow_array ) {

		print "$datname.value $curr_conn\n";

		$total = $total+$curr_conn;

	}

	print "total.value $total\n";

} else {

	my $sql = "select count(username) from v\$session where username  is not null";

	my $sth = $dbh->prepare($sql);

	$sth->execute();

	my ($curr_conn) = $sth->fetchrow_array;

	print "connections.value $curr_conn\n";

}

print "max_connections.value $max_connections\n";