check_bacula.pl

I have this working well with Postgres. A couple minor changes from the previous suggested changes:

$sql_query = "SELECT SUM(JobErrors) AS errors, COUNT(*) AS count, Job.JobId, Job.JobStatus, Log.LogText FROM Job LEFT JOIN Log on Job.JobId = Log.JobId WHERE (Name='$opt_job') AND (JobStatus='T') AND (EndTime IS NOT NULL) AND ((EndTime >= '$date_stop')) GROUP BY Job.JobId, Job.JobStatus, Log.LogText;";

(EndTime >= as opposed to =)

When there isn't a match, you don't get an array of 0's or nulls, but just an empty array.

So I check for that:

if ($#job_stats == -1)
{
@job_stats = (0,0,0,0)
}

This is the easiest fix; the rest of the code expects that array to exist, so I just set it to zeroes.

---

Hello bockfot2000, in my case: The Log Tables are empty. I tried bacula 5.2.6 and 7.0.3 on Debian Wheezy.

i applied you changes, but i get this:

Use of uninitialized value $count in numeric lt (oncatenation (.) or string at /usr/lib/nagios/plugins/check_bacula.pl line 229.
Bacula CRITICAL: Found successful jobs

---

Below is a patch to make the script working with a PostgreSQL backend, and a quick-fix for instances where query returns 0E0:

+ my $dsn = "DBI:Pg:database=$sqlDB;host=localhost";
+ $sql_query = "SELECT SUM(JobErrors) AS errors, COUNT(*) AS count, Job.JobId, Job.JobStatus, Log.LogText
+ FROM Job LEFT JOIN Log on Job.JobId = Log.JobId
+ WHERE (Name='$opt_job') AND (JobStatus='T') AND (EndTime IS NOT NULL)
+ AND ((EndTime = '$date_stop'))
+ GROUP BY Job.JobId, Job.JobStatus, Log.LogText;";
- my @job_stats = sql_exec();
+ my $sth = $dbh->prepare($sql_query);
+ my $rv = $sth->execute() or die "Error executing query: '$sql_query': $DBI::errstrn";
+ if ($rv fetchrow_array();
+ $errors = $job_stats[0];
+ $count = $job_stats[1];
+ $jobid = $job_stats[2];
+ $joblog = $job_stats[3];
+ }

---

On our bacula Version
Version: 5.0.0 (26 January 2010)

in the mysql Database each line of the log messages is stored in a new row. This leads to confusion with the sql_query in check_bacula.pl, as it is joined with the Log table of the bacula database, resulting in returning a wrong number of successful jobs.

Here is the patch file we created:

check_bacula.patch
--------------------------------------------------
***************
*** 181,187 ****
}

$date_start = get_now();
! $sql_query = "SELECT SUM(JobErrors) AS 'errors', COUNT(*) AS 'count', Job.JobId, Job.JobStatus, Log.LogText FROM Job LEFT JOIN Log on Job.JobId = Log.JobId WHERE (Name='$opt_job') AND (JobStatus='T') AND (EndTime '') AND ((EndTime = '$date_stop'));";
my @job_stats = sql_exec();
$errors = $job_stats[0];
$count = $job_stats[1];
--- 181,205 ----
}

$date_start = get_now();
! $sql_query = "
! SELECT
! SUM(JobErrors) AS 'errors',
! COUNT(*) AS 'count',
! j.JobId,
! j.JobStatus,
! l.LogText
! FROM
! Job j LEFT JOIN Log l on j.JobId = l.JobId and l.LogId = (
! select max(logid)
! from bacula.Log l2
! where l2.jobid = j.jobid
! )
! WHERE
! (Name='$opt_job')
! AND (JobStatus='T')
! AND (EndTime '')
! AND (EndTime = '$date_stop'); ";
my @job_stats = sql_exec();
$errors = $job_stats[0];
$count = $job_stats[1];
--------------------------------------------------

---

Hey,
thanks for the plugin. I'm tryingto use this with postgres. I installed perl-DBD-Pg.x86_64 package and changed the dsn to be Pg. It complains about bad password. There is currently no password as configured by bacula itself. Any idea how to circumvent this? the postgres -w command is used for no password. I'm just unsure how this translates to being fired off from the perl script.


Any help would be excellent!
[email protected]

---