#!/usr/bin/perl
use POSIX qw(strftime);
use DBI;

sub exit_code
{
    if( $_[0] > $EXIT_CODE)
    {
        $EXIT_CODE = $_[0];
    }
}

my $path = '/home/nagios/monitor/nagios/libexec/dbsession/';
if( scalar(@ARGV) != 3)
{
    print "check_oracle_session <ORACLE_SID> <USER> <PASSWORD>\n";
    exit 1;
}
my $oracle_sid = uc($ARGV[0]);
my $oracle_user = $ARGV[1];
my $oracle_pwd = $ARGV[2];


my $my_sql = sprintf <<'SQL';
  SELECT T.MACHINE,
         T.OSUSER,
         T.USERNAME,
         T.PROGRAM,
         COUNT (*)
    FROM V$SESSION T
GROUP BY MACHINE,
         OSUSER,
         USERNAME,
         PROGRAM
SQL
my $dbh = DBI->connect("dbi:Oracle:$oracle_sid","$oracle_user","$oracle_pwd");
my $sth = $dbh->prepare($my_sql);
unless( -f "$path/${oracle_sid}.cfg")
{
    $sth->execute();
    open(FH,">$path/${oracle_sid}.cfg");
    while(my @line = $sth->fetchrow_array())
    {
        foreach (@line)
        {
            s/\s+//g;
        }

        if(scalar(@line) != 5 || $line[2] eq '' )
        {
            next;
        }
        print FH "@line\n";
    }
    close(FH);
}

$sth->execute();
my %SQL_LINES ;
while(my @line = $sth->fetchrow_array())
{

    foreach (@line)
    {
        s/\s+//g;
    }

    if(scalar(@line) != 5 || $line[2] eq '' )
    {
        next;
    }

    my $tmp = "$line[0]=$line[1]=$line[2]=$line[3]";
    $tmp =~ s/\s+//g;
    $SQL_LINES{$tmp} = $line[4];
}

my %CFG_LINES ;
open(FH,"$path/${oracle_sid}.cfg");

my %exfh;
open(EXFH,"$path/${oracle_sid}.cfg.ex");
foreach (<EXFH>)
{
    chomp;
    my @fields = split(/\s+/);
    my $tmp = "$fields[0]=$fields[1]=$fields[2]=$fields[3]";
    $tmp =~ s/\s+//g;
    $exfh{$tmp} = $fields[4];
}
close(EXFH);

$EXIT_CODE = 0;
my @warn_text;
foreach (<FH>)
{
    chomp;
    my @fields = split(/\s+/);
    my $tmp = "$fields[0]=$fields[1]=$fields[2]=$fields[3]";
    $tmp =~ s/\s+//g;
    $CFG_LINES{$tmp} = $fields[4];

    my $my_f1 = $CFG_LINES{$tmp} + 0;

    if(defined($exfh{$tmp}))
    {
        $my_f1 = $exfh{$tmp} + 0;
    }

    my $my_s2 = $SQL_LINES{$tmp} + 0; 

    #print "f1 $my_f1   f2 $my_s2 ff $fields[4]\n";
    if($my_f1 != $my_s2)
    {
        my $warn_flag;
        
        if($my_f1 < $my_s2)
        {
            #print $my_s2 - $my_f1."\n";
            my $n = 5;
            if($fields[3] =~ /java|JDBC/i)
            {
                $n = 20;
            }
            if($my_s2 - $my_f1 < $n)
            {
                $warn_flag='****';
                exit_code(0);
            }
            else
            {
                $warn_flag='WARNING';
                exit_code(1);
            }
        }
        else
        {
            $warn_flag='CRITICAL';
            exit_code(2);
        }
        #print "$warn_flag: HOST->$fields[0]  OS_USER->$fields[1] DB_USER->$fields[2] PRO->$fields[3] Normal[$my_f1] != $my_s2\n";
        push @warn_text, "$warn_flag: HOST->$fields[0]  OS_USER->$fields[1] DB_USER->$fields[2] PRO->$fields[3] Normal[$my_f1] != $my_s2\n";
    }
}

$sth->finish();

$dbh->disconnect;

foreach ( qw(CRITICAL WARNING ****) )
{
    my $t = $_;
    print grep /^\Q$t/, @warn_text;
}
if($EXIT_CODE == 0)
{
    print "$oracle_sid OK!\n";
}
#print "exit_code $EXIT_CODE\n";
print "------Check End.\n";
exit $EXIT_CODE;