I've created a report that we use to e-mail us of when a system has
repeated failures--the interval we chose was 10 days but it could easily be
changed to suit your needs. I'm sure that a more complex query could be
created but I simplified the task and split it into two queries...
First I query out stuff that's never had a successful backup, then I do a
separate query looking at what I attempted to back up that day... Attached
is the perl program... Hopefully this little perl script will be helpful
to you or anybody else.
I can see that this won't exactly solve your concern of eliminating records
from the resultset that aren't "current". However, once the
no-longer-active clients' backups have fully expired and you run the bacula
'dbcheck' tool they will be purged from the client list in the db so should
be eliminated from the report.
Bob
From: Matthew Seaman <msea...@squiz.co.uk>
Hi there,
We have a variable population of client machines being backed up
by bacula. What I'd like to do is build a query for the bacula DB
that will detect eg. if any clients haven't had a full backup within the
last week. (Yes -- I know there are configuration options to
automatically promote incrementals etc. to fulls in that situation:
we're using them.) We'll then hook this up to our Nagios so the Ops
team gets alerted.
So I have come up with this query:
SELECT clientid, name, max(endtime) FROM job
WHERE level = 'F' AND type = 'B' AND jobstatus = 'T'
GROUP BY clientid, name
HAVING max(endtime) < now() - interval '7 day'
ORDER BY name
(We're using Postgresql)
This does pretty much what I want, except that the output includes job
records from clients that have been decommissioned and removed from
bacula-dir.conf. Now, for the life of me, I can't see anything in the
DB that indicates whether a client backup job is active or not. Is it
just me being blind or am I going to have to parse that out of the
bacula config files?
Cheers,
Matthew
-- Matthew Seaman Systems Administrator E msea...@squiz.co.uk
#!/usr/bin/perl -w
# Usage: To get today's report, run with no parameters
# To get a report from yesterday's backups, execute this with the
parameter 'yesterday' (w/o quotes)
#
# Original author statement:
## only works with mysql currently, should work with postgres
## (c) 2007 Falk Stern (falk.st...@akquinet.de)
## akquinet System Integration GmbH - http://www.akquinet.de/
## published under GPLv2
##
## November 5, 2010: Greatly adapted in 2009 and 2010 by Bob Hetzel
b...@case.edu to produce
## listing of failed backups from the last 10 days.
#
use DBI;
use DBD::mysql;
use Data::Dumper;
use Date::Calc qw(Today Add_Delta_Days Day_of_Week_to_Text Day_of_Week
Month_to_Text);
#use strict;
my $user = "bacula";
my $password = "";
my $database = "bacula";
my $dbhost = "localhost";
my $dsn = "DBI:mysql:database=$database;host=$dbhost;port=3306";
my $dbh = DBI->connect($dsn, $user, $password);
my $sth;
my $reportday = $ARGV[0];
my (undef,undef,undef,$mday,$mon,$year,$wday,undef,undef) = localtime;
($year,$mon,$mday) = Today();
if ($reportday eq 'yesterday') {
($year,$mon,$mday) = Add_Delta_Days($year,$mon,$mday,-1);
$wday=Day_of_Week($year,$mon,$mday);
}
#$year = $year - 1900;
#print "Year = $year \n";
#print "Month = $mon \n";
#print "Day of month = $mday \n";
#print "Weekday = $wday \n";
#print "nowish = $nowish\n";
my @days = ('Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday',
'Saturday');
my @months = ('January', 'February', 'March', 'April', 'May', 'June', 'July',
'August', 'September', 'October', 'November', 'December');
#$year += 1900;
#$mon++;
$mon = sprintf("%02d",$mon);
#$mon = 11;
#print "MDAY = $mday \n";
$mday = sprintf("%02d",$mday);
#print "MDAY = $mday \n";
#$mday = 20;
my %jobstates = (
"A" => "canceled by admin",
"B" => "blocked",
"C" => "created, but not running",
"c" => "waiting for client resource",
"D" => "verify differences",
"d" => "waiting for maximum jobs",
"E" => "term. in error",
"e" => "term. with non-fatal error",
"f" => "term. with fatal error",
"F" => "waiting on File Daemon",
"j" => "waiting for job resource",
"M" => "waiting for mount",
"m" => "waiting for new media",
"p" => "waiting for higher priority jobs to finish",
"R" => "running",
"S" => "scan",
"s" => "waiting for storage resource",
"T" => "OK",
"t" => "waiting for start time"
);
my %levels = (
"F" => "Full",
"D" => "Differential",
"C" => "Verify from catalog",
"V" => "Verify init db",
"O" => "Verify volume to catalog",
"d" => "Verify disk to catalog",
"A" => "Verify data on volume",
"B" => "Base job",
"I" => "Incremental"
);
my ($jid,$cname,$jsize,$jobdur,$jlev,$jstatus,$jrate,$jst);
print "\nBacula Failures report for $days[$wday] $months[$mon-1] $mday,
$year\n";
print "\n The following computers haven't been backed up in the last 10
days:\n\n";
print "\n";
format STDOUT_TOP =
Client Last Good Backup
------------------ ----------------
.
format STDOUT =
@<< @<<<<<<<<<<<<<<<<< @<<<<<<<<<<<<<<<
$cn,$cname, $jst
.
# get job totals
my $report_query_allbad = "select Name from (select Name,sum(if (JobStatus =
'T',1,0)) as TotSuccesses from Job group by Name) SuccessTable
where TotSuccesses = 0 and Name in
(select name from Job where StartTime
like \"$year-$mon-$mday\%\" and jobstatus <>'T')";
my $report_query_somegood="select Name,LastGoodDate from
(select Name,max(EndTime) as LastGoodDate from Job where
JobStatus='T' group by Name) LastGoodTable
where LastGoodTable.LastGoodDate < (date_sub(now(),
interval '10' day)) and Name in
(select name from Job where StartTime like
\"$year-$mon-$mday\%\" and jobstatus <>'T') order by LastGoodDate,Name";
$sth = $dbh->prepare($report_query_allbad);
$sth->execute;
$cn=0;
while ($row = $sth->fetchrow_hashref) {
$cname = $row->{"Name"};
$jst = "NEVER";
$cn++;
write;
}
$sth = $dbh->prepare($report_query_somegood);
$sth->execute;
while ($row = $sth->fetchrow_hashref) {
$cname = $row->{"Name"};
$jst = $row->{"LastGoodDate"};
$cn++;
write;
}
------------------------------------------------------------------------------
The Next 800 Companies to Lead America's Growth: New Video Whitepaper
David G. Thomson, author of the best-selling book "Blueprint to a
Billion" shares his insights and actions to help propel your
business during the next growth cycle. Listen Now!
http://p.sf.net/sfu/SAP-dev2dev
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
