3 # A bacula job report generator.
4 # It require MySQL 4.1.x or later
6 # If you have any comments question feel free to contact me, jb@soe.se
13 $db_host = "localhost";
15 $db_username = "bacula";
16 $db_password = "bacula";
18 $from = "backup\@example.net";
22 print "\n report.pl email@hostname.com (TODAY|YESTERDAY|WEEK|MONTH)\n\n";
27 if ($when eq "MONTH") {
28 $where = "StartTime > DATE_FORMAT(now() - INTERVAL 1 MONTH, '%Y-%m-%d')";
29 $order = "ORDER BY StartTime DESC";
30 } elsif ($when eq "WEEK") {
31 $where = "StartTime > DATE_FORMAT(now() - INTERVAL 7 DAY, '%Y-%m-%d')";
32 $order = "ORDER BY StartTime DESC";
33 } elsif ($when eq "YESTERDAY") {
34 $where = "StartTime > DATE_FORMAT(now() - INTERVAL 1 DAY, '%Y-%m-%d') AND StartTime < DATE_FORMAT(now(), '%Y-%m-%d')";
35 $order = "ORDER BY JobStatus,Time DESC";
38 $where = "StartTime > curdate()";
39 $order = "ORDER BY JobStatus,Time DESC";
42 $sqlquery = "SELECT JobStatus,Name,Level,JobBytes,JobFiles,DATE_FORMAT(StartTime, '%Y-%m-%d %H:%i') AS Start, TIMEDIFF(EndTime,StartTime) AS Time,PoolId
47 $dbh = DBI->connect("DBI:mysql:database=$database:host=$db_host", $db_username,$db_password) or die;
49 my $sth = $dbh->prepare("$sqlquery"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
50 while(($jobstatus,$name,$level,$jobbytes,$jobfiles,$start,$time,$poolid) = $sth->fetchrow_array()) {
51 my $sth2 = $dbh->prepare("SELECT Name FROM Pool WHERE PoolId = $poolid"); $sth2->execute() or die "Can't execute SQL statement : $dbh->errstr";
52 ($poolname) = $sth2->fetchrow_array();
53 ($hours,$minutes,$seconds) = split(":", $time);
54 $seconds = sprintf("%.1f", $seconds + ($minutes * 60) + ($hours * 60 * 60));
55 $time = sprintf("%.1f", ($seconds + ($minutes * 60) + ($hours * 60 * 60)) / 60);
56 $bytesANDfiles = sprintf "%7.0f/%d", $jobbytes/1024/1024,$jobfiles;
59 $kbs = ($jobbytes/$seconds)/1024;
62 $text .= sprintf "%s %18.18s %1s %14s %16s %5sm %4.0f %9.9s\n", $jobstatus,$name,$level,$bytesANDfiles,$start,$time,$kbs,$poolname;
63 $totalfiles = $totalfiles + $jobfiles;
64 $totalbytes = $totalbytes + $jobbytes;
66 $totalbytes = sprintf("%.1f",$totalbytes / 1024 / 1024 / 1024);
68 my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE $where"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
69 ($count_total) = $sth->fetchrow_array();
70 my $sth = $dbh->prepare("SELECT count(*) FROM Job WHERE $where AND JobStatus = 'T'"); $sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
71 ($count_ok) = $sth->fetchrow_array();
72 $count_fail = $count_total - $count_ok;
73 $counts = sprintf("%.1f", 100- (($count_fail/$count_total)*100));
76 open(MAIL,"|/usr/lib/sendmail -f$from -t");
77 print MAIL "From: $from\n";
78 print MAIL "To: $email\n";
79 print MAIL "Subject: Backup ($when) $counts% OK - Total $count_total jobs, $count_fail failed\n";
81 print MAIL "Total $count_total jobs - $count_ok jobs are OK.\n";
82 print MAIL "Total $totalbytes GB / $totalfiles files\n";
85 print MAIL "Status JobName Lvl MBytes/Files Start Time KB/s Pool\n";
86 print MAIL "============================================================================\n";
89 print MAIL "============================================================================\n";
97 A Canceled by the user
98 C Created but not yet running
104 F Waiting on the File daemon
105 S Waiting on the Storage daemon
106 m Waiting for a new Volume to be mounted
107 M Waiting for a Mount
108 s Waiting for Storage resource
109 j Waiting for Job resource
110 c Waiting for Client resource
111 d Wating for Maximum jobs
112 t Waiting for Start Time
113 p Waiting for higher priority job to finish