Custom Error reporting email in bacula

So the other day, I was tasked with creating a custom email alert for a select number of bacula clients that failed to back up. Out of 150 clients backing up, 10 of them were extremely important. So instead of admin's having to sift through the failed client backups of laptops that were not plugged in that day, I created a custom mysql query against the bacula server.

First thing I did was create a tab delimited file called clients.txt inside of my custom scripts folder (/etc/bacula/scripts). Inside of the clients.txt file is the following:

clientname-fd Joe Blow
clientname2-fd Jane Doe
clientname3-fd John Doe

Note that the clientname and the username HAS to be seperated by a TAB. If you put a space in after -fd and then tabbed, the script will not work.

Next, create a new script called bacula-query.sh (make it executable) in the /etc/bacula/scripts/ directory and paste the following into it (remove the line numbers, as I will be using line #'s to describe what the script does):

bacula-query.sh

1. #!/bin/bash


2. while IFS=$'\t' read -r clients name; do

3. echo $name

4. mysql -u bacula bacula -t -B -e "SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,Job.JobStatus as Status FROM Client,Job,JobMedia,Media WHERE Client.Name='${clients}' AND Client.ClientId=Job.ClientId AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.StartTime >= (CURDATE()) ORDER BY Job.StartTime;"

5. echo "T=Success E=Error R=Running t=waiting on start time"

6. echo -e \\n
7. done < /etc/bacula/scripts/clients.txt | mailx -s "Bacula Backup WATCH list" admin-acct@yourcompany.com


Line Details:
1. Call Bash
2. run a while statement against the tab delimited file called clients.txt, column 1 is clients, column 2 is name.
3. echo the name of the person
4. run a mysql query against the job id, client name, start time, bytes, search for backups of the current day
5. echo the codes for: success, error, running and waiting
6. echo a blank line
7. end the while statement, read clients.txt and send an email with the subject of Bacula Backup WATCH list to an email address.

The output (email) looks like this:

Joe Blow T=Success E=Error R=Running t=waiting on start time

Jane Doe
+-------+----------------+-------+---------------------+----------+------------+--------+
| JobId | Client | Level | StartTime | JobFiles | JobBytes | Status | +-------+----------------+-------+---------------------+----------+------------+--------+
| 6908 | clientname2-fd | I | 2009-11-03 17:17:23 | 625 | 6138170925 | T | +-------+----------------+-------+---------------------+----------+------------+--------+ T=Success E=Error R=Running t=waiting on start time

(**NOTE** Blogger cuts off part of the line on the above output...running this will actually output the table into a cleaner format than blogger formatted this)

As you can see, the first client (Joe Blow) has no data, thus did not backup. the 2nd client (Jane Doe) successfully backed up and output of the backup is shown in the email.

From there, I created a cronjob that calls the script every night mon-fri at 10pm (when client backups should be done)

0 22 * * 1,2,3,4,5 /etc/bacula/scripts/bacula-query.sh #Email Bacula WATCH list

There you go, automated emails on specific backups of clients as a WATCH list of important clients.