#!/usr/bin/env perl

use Getopt::Long qw(:config no_ignore_case);
use DBI;
use DBD::Oracle qw(:ora_session_modes);


my $help = '';
my $sid = '';			# Oracle SID
my $ora_home = '';		# Oracle HOME
my %ts_cli_thresholds = ();	# tablespaces thresholds from command line
my %nagios_exit_codes = ( 'UNKNOWN', 3, 'OK', 0, 'WARNING', 1, 'CRITICAL', 2 );
my $output_msg = '';
my $status = 'OK';

my $result = Getopt::Long::GetOptions ('help' => \$help, 'sid=s' => \$sid, 'ora_home=s' => \$ora_home, 'threshold=s' => \%ts_cli_thresholds);

if ( !$result or $help or !$sid or !$ora_home or !(scalar keys %ts_cli_thresholds) ) {&usage;}

# Split threshold options warning/critical percentage values from command line into anonymous hash in tablespaces thresholds
foreach $tablespace_name (keys %ts_cli_thresholds){
   if ($ts_cli_thresholds{$tablespace_name} =~ /(\d+\.?\d*)\/(\d+\.?\d*)/ ) {
      if ( $2 > $1 ) {
         $ts_cli_thresholds{$tablespace_name} = { 'warning' => $1, 'critical' => $2};
      }else{
         print "\nERROR: critical threshold must be greater than warning threshold\n";
         &usage;
      }
   }else{
      print "\nERROR: invalid threshold\n";
      &usage;
   }
}


# Connect to oracle
$dsn = "dbi:Oracle:";
$ENV{ORACLE_SID} = $sid;
$ENV{ORACLE_HOME} = $ora_home;
delete $ENV{TWO_TASK};

$dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA });
if ( ! defined $dbh ){
   print "$DBI::errstr\n";
   exit $nagios_exit_codes{'UNKNOWN'};
}

$dbh->{RaiseError} = 1;
$sth = $dbh->prepare(qq{select df.tablespace_name TABLESPACE_NAME, fs.free_space FREE_SPACE, df.total_space TOTAL_SPACE from (select tablespace_name, sum(bytes) total_space from dba_data_files group by tablespace_name) df, (select tablespace_name, sum(bytes) free_space from dba_free_space group by tablespace_name) fs where df.tablespace_name = fs.tablespace_name(+) and df.tablespace_name not like 'UNDO%'}) or die $dbh->errstr;


if ( $sth->execute ) {

	# Traverse all tablespaces from sql response
	while ( $sql_result_record = $sth->fetchrow_hashref()) {

		my $ts_used_space_pct = 0;
		chomp %{$sql_result_record};

		# Process tablespace if it has thresholds from command line  
		if ( $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}} ){

                        # If there is space assigned to tablespace calculate used space percentage
			if ( $sql_result_record->{'TOTAL_SPACE'} > 0 ) {
				$ts_used_space_pct = (  ($sql_result_record->{'TOTAL_SPACE'} -
                                    $sql_result_record->{'FREE_SPACE'}) / $sql_result_record->{'TOTAL_SPACE'} ) * 100;
				$ts_used_space_pct = sprintf("%.2f", $ts_used_space_pct);
                        # warn tablespace without total_space from sql query
			}else{
                             $status = 'WARNING' unless ( $status eq 'CRITICAL' );
                             $output_msg .= "[<blink><b>$sql_result_record->{'TABLESPACE_NAME'} total_space =
                                              $sql_result_record->{'TOTAL_SPACE'}<\/b><\/blink>]  ";
                             delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'warning'};
                             delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'critical'};
                             delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}};
                             next;
                        }

                        # Let's check thresholds
			if ($ts_used_space_pct >= $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'critical'}){
				$status = 'CRITICAL';
				$output_msg .= "[<blink><b>$sql_result_record->{'TABLESPACE_NAME'}:" .
                                               " $ts_used_space_pct%<\/b><\/blink>]  ";
                        }elsif($ts_used_space_pct >= $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'warning'}){
                                $status = 'WARNING' unless ( $status eq 'CRITICAL' );
				$output_msg .= "[$sql_result_record->{'TABLESPACE_NAME'}: $ts_used_space_pct%]  ";
			}else{
                                $output_msg .= "[$sql_result_record->{'TABLESPACE_NAME'}: $ts_used_space_pct%]  ";
                        }
			# Once processed, we drop command line tablespace/thresholds
			delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'warning'};
                        delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}}{'critical'};
                        delete $ts_cli_thresholds{$sql_result_record->{'TABLESPACE_NAME'}};
		} # end if
	} # end while
} else {
	$status = 'UNKNOWN';
        $sth->finish();
        $dbh->disconnect();
        print $dbh->errstr;
        exit $nagios_exit_codes{ $status };
}

$sth->finish();
$dbh->disconnect();


if ( length($output_msg) ) { $output_msg = "USED SPACE: " . $output_msg; }

# Let's check if any tablespace remains in command line, not being present in oracle dba_data_files table
if ( scalar keys %ts_cli_thresholds ) {
	$status = 'CRITICAL';
	foreach ( keys(%ts_cli_thresholds) ) {
	$output_msg = "[<blink><b>$_ is not in $sid<\/b><\/blink>]  " . $output_msg;	
	}
}

print $output_msg, "\n";
exit $nagios_exit_codes{ $status };


########################## SUBROUTINES #######################

sub usage {
   print "\nUsage:\n\t$0 --sid=<ORACLE_SID> --ora_home=<ORACLE_HOME for SID> --threshold <TABLESPACE_NAME=warning/critical> [[--threshold <TABLESPACE_NAME=warning/critical>] ... ]\n\n";
   print "\t--sid:		SID name of oracle instance\n";
   print "\t--ora_home:	ORACLE_HOME for SID\n";
   print "\t--threshold:	TABLESPACE_NAME=warning/critical percentage thresholds for used space (range [0.1 .. 100])\n\n";
   exit $nagios_exit_codes{'UNKNOWN'};
}

