#!/usr/local/bin/perl -w
#======================================================================
# Program: Program to retrieve data from an Oracle database
#          and send performance data in text format to an email 
#          address.
#
# Author:  Ron Bueler
#
# Date:    April 22, 2001
#
# Revision History:
#
#
#======================================================================
use strict;
use diagnostics;
use DBI;				# Oracle DBI module
 
$ENV{ORACLE_HOME}='/ora01/app/oracle/product/8.1.6';
$ENV{LD_LIBRARY_PATH}='/ora01/app/oracle/product/8.1.6/lib';

select (STDOUT); $|=1;
 
my ($dbh)       = "";
my ($sth)       = "";
my ($sql)       = "";
my ($rv)        = "";
my ($host)      = "pm02rg";
my ($subject)   = "";
my ($duration)  = "1";
my ($now)       = ` /sbin/date +"%Y-%m-%d %H:%M" `;
my ($yesterday) = "";
my (@row)       = ();
my ($name)      = "joe";
my ($time)      = "now";
my ($hr)        = "99";
my ($user)      = 99;
my ($sys)       = 99;
my ($util)      = 99;
my ($idle)      = 99;
my ($report_file) = "/var/smart/cgi-bin/report.txt";

{
    #==================================================
    # Take care of some house-keeping stuff
    #--------------------------------------------------

    chomp ($now);
    $yesterday = &calc_yesterday ($now);

    #==================================================
    # First, connect to the database and get the data
    #--------------------------------------------------

    $dbh = DBI->connect("dbi:Oracle:smart.rst.cw.net", "webdb", "webdb" )
        || die "Can't connect to Oracle database: $DBI::errstr\n";
 
    $sql = <<ENDSQL;
        SELECT host, 
               TO_CHAR(dtg, 'yyyy-mm-dd') as day, 
               TO_CHAR(dtg, 'hh24')       as hour,
               ROUND(AVG(usr),2)          as usr,
               ROUND(AVG(system),2)       as sys,
               ROUND(AVG(utilization),2)  as util,
               ROUND(AVG(idle),2)         as idle
        FROM   smart.cpus
        WHERE  host = '$host'
          AND  TO_CHAR(dtg, 'yyyy-mm-dd hh24:mi') >= '$yesterday'
        GROUP BY host, 
                 TO_CHAR(dtg, 'yyyy-mm-dd'), 
                 TO_CHAR(dtg, 'hh24')
ENDSQL

    $sth = $dbh->prepare ($sql) 
        || die "Can't prepare statement: $DBI::errstr\n";

    $rv = $sth->execute
        || die "Can execute statement: $DBI::errstr\n";

    open (REPORT, "> $report_file") || 
        die "Cannot open temp file";

    $subject = "Average hourly CPU utilization for " . $host;

    printf REPORT ("\n    %s\n\n", $subject);

    printf REPORT ("Host    DTG         Hr   User   Sys    Util   Idle\n");
    printf REPORT ("------  ----------  --  -----  -----  -----  -----\n");

    while (@row=$sth->fetchrow_array) {

        ($name, $time, $hr, $user, $sys, $util, $idle) = @row;

        printf REPORT ("%6s  %10s  %2s %6.2f %6.2f %6.2f %6.2f\n", 
                       $name, $time, $hr, $user, $sys, $util, $idle);
    }

    close (REPORT);

    $sth->finish() ||
        die "\nCouldn't finish statement: " . $sth->errstr;


    system ("mailx -s '$subject' joe.hall\@cw.com                < $report_file");
    system ("mailx -s '$subject' ronald.bueler\@schriever.af.mil < $report_file");

    $sth->finish
        || warn "Finish statement failed: $DBI::errstr\n";

    $dbh->disconnect 
        || warn "Disconnection failed: $DBI::errstr\n";
 
    exit(0);
}

#======================================================================
#  Calculates yesterday's date
#----------------------------------------------------------------------

sub calc_yesterday {
    my ($today)   = @_;
    my ($year)    = substr ($today,  0, 4);
    my ($month)   = substr ($today,  5, 2);
    my ($day)     = substr ($today,  8, 2);
    my ($hours)   = substr ($today, 11, 2);
    my ($minutes) = substr ($today, 14, 2);

    if ($day ne "01") {

        $day = $day - 1;

    } else {

        if ($month eq "01") {

            $year  = $year - 1;
            $month = 12;
            $day   = 31;

        } elsif (($month eq "02") ||
                 ($month eq "04") ||
                 ($month eq "06") ||
                 ($month eq "08") ||
                 ($month eq "09") ||
                 ($month eq "11")) {

            $month = $month - 1;
            $day   = 31;

        } elsif (($month eq "05") ||
                 ($month eq "07") ||
                 ($month eq "10") ||
                 ($month eq "12")) {

            $month = $month - 1;
            $day   = 30;

        } elsif (($month eq "03") && ($year % 4 == 0)) {

            $month = $month - 1;
            $day   = 29;

        } elsif (($month eq "03") && ($year % 4 != 0)) {

            $month = $month - 1;
            $day   = 28;

        } else {

            printf ("WARNING: Coding error\n\n");
        }
    }

    if (length($day) < 2) {
        $day = "0" . $day;
    }

    if (length($month) < 2) {
        $month = "0" . $month;
    }

    if (length($hours) < 2) {
        $hours = "0" . $hours;
    }

    if (length($minutes) < 2) {
        $minutes = "0" . $minutes;
    }

    return $year . "-" . $month . "-" . $day . " " . $hours . ":" . $minutes;
}
#======================================================================
# end of host_cput_text.cgi
#======================================================================