#!/usr/bin/perl
#
# $Id: report.pl,v 1.23 2014/11/10 15:39:56 root Exp $
# $Locker:  $
#
# A bacula job report generator.
# It require MySQL 4.1.x or later
#
# Original Code:
# Jonas Björklund
# If you have any comments question feel free to contact me, jb@soe.se
#
# Modifications by John Lockard: jlockard@umich.edu
#

my $version="1.3";

use DBI;
use Getopt::Std;
use Email::Sender::Simple qw(sendmail);
use Email::Simple;
use Email::Simple::Creator;

use strict;

my $MAIL;
my $bytesANDfiles;
my $count_fail;
my $count_ok;
my $count_running;
my $count_waiting;
my $count_total;
my $counts;
my $hours;
my $interval;
my $job_start;
my $job_end;
my $jobbytes;
my $jobfiles;
my $jobstatus;
my $kbs;
my $level;
my $minutes;
my $name;
my $order;
my $poolid;
my $poolname;
my $seconds;
my $start;
my $start_range;
my $text;
my $time;
my $total_bytes;
my $totalbytes;
my $totalfiles;
my $when;
my $where;


sub main::HELP_MESSAGE {
print << 'EndHelp';

Usage: report.pl [OPTIONS]
  OPTIONS:
  -e   Valid email address to send output
  -s   How to sort ouput
       S = [S]tatus
       R = [R]untime
       T = [T]ime job started
       N = [N]ame
       Valid options: S, R, T, N, SR, ST, SN
       Default is ST
  -w   What time period to run job for
       T = [T]oday
       Y = [Y]esterday
       W = past [W]eek
       M = past [M]onth
       Valid Options: T, Y, W, M
       Default is T
  -c   Use calendar date rather than previous
       24 hours from current time
       (only affects -wT option)
  -t   Output to terminal rather than emailing
  -h   This help message

  SAMPLE:
    report.pl -e address@example.com -sSN -wM -c
  DEFAULT:
    report.pl -sST -wT -e backups@example.com

EndHelp
exit 1;
}

sub main::VERSION_MESSAGE {
    print "report.pl version $version\n";
}

$Getopt::Std::STANDARD_HELP_VERSION = 1;  # Use standard-conforming behavior

my %opts = ();
if ( !getopts('e:w:s:cth', \%opts ) || $opts{'h'} ) {
	main::VERSION_MESSAGE();
	main::HELP_MESSAGE();
 }

my $db_host	= "localhost";
my $database	= "bacula";
my $db_username	= "bacula";
my $db_password	= 'xxxxxxxxxxxxxxx';
my $email_from	= 'backups@sample.com';
my $email_to	= $email_from;
my $when_v	= "T";		# Default is "TODAY"
my $sort_v	= "ST";		# Default is Status then Time job started
my $calendar	= 0;		# Default is to base off current time
my $terminal	= 0;		# Default is to email output
my $email_command	= "|/usr/lib/sendmail -f$email_from -t";
my $sep = "";
my $sort = "";

if ($opts{'e'}) { $email_to =    $opts{'e'}; }
if ($opts{'w'}) { $when_v   = uc $opts{'w'}; }
if ($opts{'s'}) { $sort_v   = uc $opts{'s'}; }
if ($opts{'c'}) { $calendar = uc $opts{'c'}; }
if ($opts{'t'}) { $terminal = uc $opts{'t'}; }

if (!$when_v =~ /[TYWM]/ || $when_v =~ /T/) {
	$when = "TODAY";
	$interval = "INTERVAL -1 DAY";
} elsif ($when_v =~ /Y/ ) {
	$calendar = 1;
	$when = "YESTERDAY";
	$interval = "INTERVAL -1 DAY";
} elsif ($when_v =~ /W/ ) {
	$calendar = 1;
	$when = "WEEK";
	$interval = "INTERVAL -1 WEEK";
} elsif ($when_v =~ /M/ ) {
	$calendar = 1;
	$when = "MONTH";
	$interval = "INTERVAL -1 MONTH";
}
if ($calendar = 1) {
	$start_range = "DATE_ADD(CURRENT_DATE(), " . $interval . ")";
	$job_start   = "DATE_FORMAT(StartTime, '%Y-%m-%d')";
	$job_end     = "DATE_FORMAT(EndTime, '%Y-%m-%d')";
} else {
	$start_range = "DATE_ADD(NOW(), " . $interval . ")";
	$job_start   = "StartTime";
	$job_end     = "EndTime";
}

#if ($calendar == 1 || $when eq "YESTERDAY") {
if ($when eq "YESTERDAY") {
	$where = $start_range . " = " . $job_start or $start_range . "=" . $job_end;
print "$where\n\n";
} else {
	$where = $start_range . " <= " . $job_start or $start_range . "<=" . $job_end;
}

$order = "";
if ($sort_v =~ /[SRTN]/ ) {
	if ($sort_v =~ /S/) {
		$order .= "JobStatus";
		$sep = ",";
	}
	if ($sort_v =~ /R/) {
		$order .= $sep . "Time DESC";
	} elsif ($sort_v =~ /T/) {
		$order .= $sep . "StartTime DESC";
	} elsif ($sort_v =~ /N/) {
		$order .= $sep . "Name";
	}
} else {
	$order .= "JobStatus,Time DESC";
}
	
my $sqlquery = "SELECT JobStatus,Name,Level,JobBytes,JobFiles,DATE_FORMAT(StartTime, '%m.%d %H:%i') AS Start,TIMEDIFF(EndTime,StartTime) AS Time,PoolId
	FROM Job
	WHERE $where OR JobStatus = 'R'
	ORDER BY $order";

my $dbh = DBI->connect("DBI:mysql:database=$database:host=$db_host", $db_username,$db_password) or die;
        
my $sth = $dbh->prepare("$sqlquery"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
while(($jobstatus,$name,$level,$jobbytes,$jobfiles,$start,$time,$poolid) = $sth->fetchrow_array()) {
	my $sth2 = $dbh->prepare("SELECT Name FROM Pool WHERE PoolId = $poolid"); $sth2->execute() or die "Can't execute SQL statement : $dbh->errstr";
	($poolname) = $sth2->fetchrow_array();
	($hours,$minutes,$seconds) = split(":", $time);
	$seconds = sprintf("%.1f", $seconds + ($minutes * 60) + ($hours * 60 * 60));
	$time = sprintf("%.1f", ($seconds + ($minutes * 60) + ($hours * 60 * 60)) / 60);
	$bytesANDfiles = sprintf "%7.0f/%d", $jobbytes/1024/1024,$jobfiles;
	$kbs = 0;
	if ($jobbytes != 0 && $seconds != 0) {
		if ($jobbytes/$seconds > 1024) {
			$kbs = ($jobbytes/$seconds)/1024;
		} else {
			$kbs = 0;
		}
	}
	
	$text .= sprintf "%s %18.18s %1s %16s %12s %6sm %6.0f %18.18s\n", $jobstatus,$name,$level,$bytesANDfiles,$start,$time,$kbs,$poolname;
	$totalfiles = $totalfiles + $jobfiles;
	$totalbytes = $totalbytes + $jobbytes;
}
$totalbytes = sprintf("%.1f",$totalbytes / 1024 / 1024 / 1024);

my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE $where"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
($count_total) = $sth->fetchrow_array();
my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE JobStatus = 'R'"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
($count_running) = $sth->fetchrow_array();
my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE JobStatus = 'C' or JobStatus = 'F' or JobStatus = 'M'  or JobStatus = 'S' or JobStatus = 'c' or JobStatus = 'd' or JobStatus = 'j' or JobStatus = 'm' or JobStatus = 'p' or JobStatus = 's' or JobStatus = 't'"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
($count_waiting) = $sth->fetchrow_array();
my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE $where AND JobStatus = 'T'"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
($count_ok) = $sth->fetchrow_array();
$count_fail = $count_total - $count_ok - $count_running;
if ($count_total-$count_running != 0) {
	$counts = sprintf("%.1f", 100- (($count_fail/($count_total-$count_running))*100)); 
} else {
	$counts = 0;
}


if ($terminal == 1) {
	open(MAIL,"|cat");
} else {
	open(MAIL,"|/usr/lib/sendmail -t");
}
print MAIL "From: Bacula-Backups <$email_from>\n";
print MAIL "To: $email_to\n";
print MAIL "Subject: Bacula: [ServerName] Report: $when - $counts% OK - Total $count_total jobs, $count_running running, $count_fail failed\n";
if ($terminal != 1) {
	print MAIL "Content-Type: text/html; charset=ISO-8859-1\n\n"
		 . "<html><head></head><body><pre>\n";
}
print MAIL "Total $count_total jobs - $count_ok jobs are OK\n";
print MAIL "Total $totalbytes GB / $totalfiles files\n";
print MAIL "      $count_waiting jobs are waiting\n";
print MAIL "\n";

print MAIL "Status       JobName Lvl   MBytes/Files        Start    Time   KB/s               Pool\n";
print MAIL "======================================================================================\n";
print MAIL $text;

print MAIL "======================================================================================\n";
print MAIL <<EOF;

Status codes:

  T 	Terminated normally
  A 	Canceled by the user
  B 	Blocked
  C 	Created but not yet running
  D 	Verify Differences
  E 	Terminated in Error
  F 	Waiting on the File daemon
  M 	Waiting for a Mount
  R 	Running
  S 	Waiting on the Storage daemon
  c 	Waiting for Client resource
  d 	Waiting for Maximum jobs
  e 	Non-fatal error
  f 	Fatal error
  j 	Waiting for Job resource
  m 	Waiting for a new Volume to be mounted
  p 	Waiting for higher priority job to finish
  s 	Waiting for Storage resource
  t 	Waiting for Start Time
	
EOF
if ($terminal != 1) {
	print MAIL "</pre></body></html>";
}
close(MAIL);