#!/usr/bin/perl 
#Author:
#        Otavio Honorio (otavio.honorio@opservices.com.br)
#	revisão feita por Grace Waka (grace.waka@opservices.com.br)
# 	em 27/11/2013 às 14h
use strict;
use Getopt::Long;
use POSIX;
use File::Basename;
use DBD::Oracle qw(:ora_session_modes);
use Switch;
# Setting environment
$ENV{"ORACLE_HOME"}="/usr/lib/oracle/11.1/client";
$ENV{"LD_LIBRARY_PATH"}="/usr/lib/oracle/11.1/client/lib";
$ENV{"TNS_ADMIN"}="/usr/lib/oracle/11.1/client/network/admin";
$ENV{"USER"}="opuser";
$ENV{"HOME"}="/home/opuser";
# Global variables
our $name = basename($0, ".pl");
our $path = "/usr/local/opmon/libexec";
our ($opt_help, $opt_verbose, $opt_warn, $opt_crit, $perf);
our $opt_secs = 0;
our ($opt_user, $opt_pass, $opt_instance, $opt_command);

sub main {

     getoption();
	switch ($opt_command) {
                case "jobs-interrompidos" {
			my $result = oracle($opt_instance,$opt_user,$opt_pass,"select count(*) from dba_jobs where BROKEN='Y'");
			my $code = metric($result);
			print "$result Jobs Interrompidos | $opt_command=$result;$opt_warn;$opt_crit;0;\n";
			exit($code);
                }

                case "sessoes-em-lock" {
			my $result;			
			if($opt_secs){
				$result = oracle($opt_instance,$opt_user,$opt_pass,"select count( * ) from v\$session where blocking_session is not null and seconds_in_wait >180");
			}
			else{
				$result = oracle($opt_instance,$opt_user,$opt_pass,"select count( * ) from v\$session where blocking_session is not null and seconds_in_wait >$opt_secs");
			}
			my $code = metric($result);
			print "$result Sessoes em lock | $opt_command=$result;$opt_warn;$opt_crit;0;\n";
			exit($code);
                }

                case "numero-de-transacoes" {
			my $result = oracle($opt_instance,$opt_user,$opt_pass,"SELECT sum(A.REPGROUP + B.MVGROUP + C.MV + D.MVLOG + E.TEMPLATE) total FROM (SELECT COUNT(G.GNAME) REPGROUP FROM DBA_REPGROUP G, DBA_REPSITES S WHERE G.MASTER = 'Y' AND S.MASTER = 'Y' AND G.GNAME = S.GNAME AND S.MY_DBLINK = 'Y') A, (SELECT COUNT(*) MVGROUP FROM DBA_REGISTERED_MVIEW_GROUPS) B, (SELECT COUNT(*) MV FROM DBA_REGISTERED_MVIEWS) C, (SELECT COUNT(*) MVLOG FROM (SELECT 1 FROM DBA_MVIEW_LOGS GROUP BY LOG_OWNER, LOG_TABLE)) D, (SELECT COUNT(*) TEMPLATE FROM DBA_REPCAT_REFRESH_TEMPLATES) E");

			my $result2 = oracle($opt_instance,$opt_user,$opt_pass,"select value from v\$parameter where name = 'transactions'");
			my $code = metric($result);
			my $perc_result = ((100 * $result) / $result2);
			print "$perc_result\n";
			print "$result Numero de Transacoes | $opt_command=$result;$opt_warn;$opt_crit;0;\n";
			exit($code);
                }
        	else { print_error("Unknown Object. Use --help"); }
        }
}
#--------------------------------------------------------------------------------------
sub metric {
	my $value = shift;
	if ($value >= $opt_crit) {
		return 2
	}elsif ($value >= $opt_warn) {
		return 1
	}elsif ($value < $opt_warn) {
		return 0
	}else{
		return 3
	}
}
#--------------------------------------------------------------------------------------
sub imetric {
	my $value = shift;
	if ($value <= $opt_warn) {
		return 2
	}elsif ($value <= $opt_crit) {
		return 1
	}elsif ($value > $opt_crit) {
		return 0
	}else{
		return 3
	}
}
#--------------------------------------------------------------------------------------
sub oracle {
	my $orainstance = shift;
	my $username = shift;
	my $password = shift;
	my $sql = shift;
	my $data_source = "dbi:Oracle:$orainstance";

	my $dbh = DBI->connect($data_source,$username,$password,{PrintError=>0});
	unless ($dbh) {
        	print "Counld not connect $DBI::errstr\n";
	        exit(1);
	}

	my $cursor = $dbh->prepare($sql);
	if (defined $cursor) {
		$cursor->execute;
		my $result = $cursor->fetchrow;
		return $result
	}
}
#--------------------------------------------------------------------------------------
sub getoption  {
        Getopt::Long::Configure('bundling');
        GetOptions(
            'critical=f'              => \$opt_crit,
            'help'                  => \$opt_help,
            'verbose=i'               => \$opt_verbose,
            'warning=f'               => \$opt_warn,
            'mode=s'               => \$opt_command,
	    'connect=s'		=> \$opt_instance,
	    'user=s'		=> \$opt_user,
	    'password=s'		=> \$opt_pass,
	    'seconds=i'		=> \$opt_secs,
        );
        if($opt_help){
                printUsage();
                exit(1);
        }
        if ((!$opt_warn) or (!$opt_crit) or (!$opt_instance) or (!$opt_user) or (!$opt_pass) or (!$opt_command)){
                printUsage();
                exit(1);
        }
}
#--------------------------------------------------------------------------------------
sub logger {                                                                                                                                                                                                                                 
        my $msg = shift (@_);                                                                                                                                                                                                                
        my $log = "$path/$name.log";                                                                                                                                                                                                         
        my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst)=localtime(time);                                                                                                                                                            
        $wday++;                                                                                                                                                                                                                             
        $yday++;                                                                                                                                                                                                                             
        $mon++;                                                                                                                                                                                                                              
        $year+=1900;                                                                                                                                                                                                                         
        $isdst++;                                                                                                                                                                                                                            
        open(LOG, ">>$log");                                                                                                                                                                                                                 
        printf LOG ("%02i/%02i/%i - %02i:%02i:%02i => %s\n",$mday,$mon,$year,$hour,$min,$sec,$msg);                                                                                                                                          
        close(LOG);                                                                                                                                                                                                                          
}                                                                                                                                                                                                                                            
#--------------------------------------------------------------------------------------                                                                                                                                                      
sub printUsage {                                                                                                                                                                                                                             
       print <<EOB
Usage: $name.pl [OPTION]...

	Options:
	     --help
		show help
	    --connect
       		the connect string
	    --user
	        the oracle user
	    --password
	        the oracle user's password
	    --warning
	        the warning range
	    --critical
	        the critical range
	    --mode
	        the mode of the plugin. select one of the following keywords:
	        jobs-interrompidos
	        sessoes-em-lock
		numero-de-transacoes
	   --seconds
                the timeout for waiting execution

EOB
}
#--------------------------------------------------------------------------------------
sub print_error {
	my $msg = shift;
	print "$msg\n";
	exit(3);
}
#--------------------------------------------------------------------------------------
&main;