Generating Basic Bacula Backup Email Summary Reports

A request was made on the Bacula mailing list for a way to get daily and weekly backup reports. This got me to thinking that such a daily email would be useful.

So, off I went to write a simple bash shell script to generate these reports.

Keep in mind that this is a pretty simple report and it works for our needs. You may wish to modify it to include HTML formatting, additional fields such as StartTime and EndTime, total GB for all jobs, etc.

The script expects to see one command line parameter which is the number of hours to report on. The following would generate a report going back 24 hours:

$ baculabackupreport.sh 24

The email would something look like this:

Subject: MyServerName Bacula backup report for the past 24 hours

Backup History for the past 24 hours
--------------------------------------

JobId	Name		Type	Level	    GB
-----	----		----	-----	 --------
20444 	NewbyFull 	B 	D 	  2.43 GB
20445 	SpeedyFull 	B 	D 	 78.99 GB
20446 	VirtualVMs 	B 	F 	 107.27 GB
20447 	Music   	B 	D 	 54.47 GB
20448 	Admin     	B 	D 	  0.01 GB
20449 	Voip      	B 	D 	  6.88 GB
20450 	Satch     	B 	D 	  1.34 GB
20451 	kubuntu   	B 	D 	  0.27 GB
20452 	Zimbra    	B 	F 	 16.33 GB
20453 	Helpdesk  	B 	F 	  7.39 GB
20454 	Newby_MustHave 	B 	F 	  8.26 GB
20458 	Catalog   	B 	F 	  1.88 GB

This script may be run from the command line, or from a cron job.

Hope this helps somebody somewhere.

Comments welcome!

*EDIT* - 20140115 I revisited this post and implemented some of the requests that were made in the comments. I also added several more useful columns to the report generated. See script #2 "baculabackupreportV2.sh" attached below.

Here is what the new report looks like:

Backup History for the past 24 hours
--------------------------------------

JobId        Name           Start Time            Stop Time       Level  Status   Files       Bytes
-----   --------------  -------------------  -------------------  -----  ------  --------  -----------
23893   admin           2014-01-14 20:30:09  2014-01-14 20:30:14      I       T        30      0.01 GB
23894   dns1            2014-01-14 20:30:09  2014-01-14 20:31:12      I       T        34      0.08 GB
23895   dns2            2014-01-14 20:30:09  2014-01-14 20:30:24      I       T        46      0.03 GB
23896   proxmox-a       2014-01-14 20:30:09  2014-01-14 20:30:16      I       T        92      0.03 GB
23897   proxmox-b       2014-01-14 20:30:09  2014-01-14 20:30:34      I       T        85      0.01 GB
23898   rpmp            2014-01-14 20:30:10  2014-01-14 20:30:44      I       T       121      0.02 GB
23899   NewbyFull       2014-01-14 20:30:25  2014-01-14 20:52:58      I       T       909     22.90 GB
23900   SpeedyFull      2014-01-14 20:30:19  2014-01-14 20:31:16      I       T       230      1.04 GB
23901   SpeedyVMs       2014-01-14 20:30:26  2014-01-14 20:30:29      I       T         0      0.00 GB
23902   SpeedyMusic     2014-01-14 20:30:32  2014-01-14 20:30:33      I       T         0      0.00 GB
23903   Voip            2014-01-14 20:30:40  2014-01-14 20:32:28      I       T       641      1.38 GB
23904   Satch           2014-01-14 20:30:37  2014-01-14 20:34:53      F       E         0      0.00 GB
23905   Helpdesk        2014-01-14 20:30:51  2014-01-14 20:31:33      I       T       278      0.01 GB
23907   jabber          2014-01-15 02:45:00  2014-01-15 02:54:57      F       T    282661      2.36 GB
23908   Zimbra8         2014-01-15 03:03:08  2014-01-15 04:13:28      F       T   1163965     21.56 GB
23909   Catalog         2014-01-15 04:25:00  2014-01-15 04:49:54      F       T         7     22.86 GB

*EDIT* - 20150704 One more revisit to this script. Added the ability to report on either MySQL or Postgresql databases. See script #3 "baculabackupreport-20150704.sh" below.

AttachmentSize
baculabackupreport-20150704.sh4.05 KB
baculabackupreportV2.sh2.42 KB
baculabackupreport.sh1.58 KB

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Changing subject, depending on the JobStatus

I need change subject, depending on the JobStatus


                x=a
                x=$(echo "SELECT JobStatus \
                FROM Job \
                WHERE Type='B' \
                AND (JobStatus='f' \
                OR JobStatus='E'   \
                OR JobStatus='e'   \
                AND RealEndTime >= CURRENT_TIMESTAMP(2) - cast('${hist} HOUR' as INTERVAL) \
                ORDER BY JobId;" \
                | ${dbbin} -U ${dbuser} ${dbpass} -0t \
                | sed -e 's/|//g' -e '/^$/d' )


                query=$(echo "SELECT JobId, Name, StartTime, EndTime, Level, JobStatus, JobFiles, JobBytes \
                FROM Job \
                WHERE Type='B' \
                AND RealEndTime >= CURRENT_TIMESTAMP(2) - cast('${hist} HOUR' as INTERVAL) \
                ORDER BY JobId;" \
                | ${dbbin} -U ${dbuser} ${dbpass} -0t \
                | sed -e 's/|//g' -e '/^$/d' )



reference=""
if [ "$x" ==  "$reference" ]
then
SUBJECT="Bacula $server summary rapport."
else
SUBJECT="Bacula $server summary rapport.[Backup FAILED]"
fi


So I add Conditionals IF to script.

Didn't work with MariaDB but I got it working..

I tried to get the script to work with MariaDB but it wouldn't return any results. I made a couple of changes to the SQL query and it is working great now.

Here is what I changed the mysql portion to...


case ${dbtype} in
mysql )
query=$(mysql -u ${dbuser} -p${dbpass} -s -N -e "SELECT JobId, Name, StartTime, EndTime, Level, JobStatus, JobFiles, JobBytes \
FROM ${db}.Job \
WHERE Type = 'B' \
AND RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) \
ORDER BY JobID;")
;;

Thanks for the feedback

Thanks for the feedback....

One thing though, in this instance, I would just add another case choice "mariadb )" so that the "mysql )" case still works with official MySQL databases.

Thanks again for posting!

Bill

Good call...

Yeah, I was in a hurry to get it working but went back and added it as a choice. Feel free to add it to your script if you want for others using MariaDB. I can confirm it works with MariaDB 5.5.44 and Bacula 7.0.5 on Centos 7.2.1511.

Thanks for the script!

HTML Modified to Include restricted reporting

We have developers who are keenly interested in knowing their work is backed up successfully. In addition, we provide a restricted console that allows them to run full backups of their environments from their local machines. To ensure reports are tailored to each developer, we simply build Jobs specific to each machine and include the RunAfterJob command and pass in the variables (ie RunAfterJob = "/path/to/baculabackupreport.sh 24 email@example.com Backup-devmachine1")

The HTML script is the only script that actually looked correct in my email client; it was an easy decision to modify the script as follows:

#!/bin/bash
admin="backup-jocky@example.com"
mybin="/usr/bin/mysql"
bcbin="/usr/sbin/bconsole"
bcconfig="/etc/bacula/bconsole.conf"
db="bacula"
dbuser="-ubacula"
# Uncomment and set db password if one is used
dbpass="-pOBFUSCATION"
server="dbserver.example.com"


# --------------------------------------------------
# Nothing should need to be modified below this line
# --------------------------------------------------

hist=${1}
rcpt=${2}
client=${3}
if [ -z ${hist} ] || [ -z ${rcpt} ] || [ -z ${client} ]; then
        echo "USAGE:"
        echo "backupreport.sh {Time frame in hours} {email report to} {Backup Job Name}"
	echo "example: backupreport.sh 24 me@example.com Backup-dev3"
        exit
fi

subject="Content-Type: text/html;
Subject: Backup report for job '${3}' covering the past ${1} hours"

header="Backup history for the past ${1} hours
Backup History for the past ${1} hours
--------------------------------------

JobId        Name                  Start Time            Stop Time       Level  Status   Files       Bytes
-----   ---------------------  -------------------  -------------------  -----  ------  --------  -----------
"

msg=`echo "SELECT JobId, Name, StartTime, EndTime, Level, JobStatus, JobFiles, JobBytes FROM Job WHERE Name ='${3}' AND Type = 'B' AND RealEndTime >=  DATE_ADD(NOW(), INTERVAL -${hist} HOUR) ORDER BY JobID;" \
| ${mybin} ${dbuser} ${dbpass} ${db} \
| sed '/^JobId/d' \
| awk '{ printf("%-7s %-22s %s %-9s %s %-9s %5s %7s %9d %9.2f GB\n", $1, $2, $3, $4, $5, $6, $7, $8, $9, $10/(1024*1024*1024));}'`
footer=""

echo "${subject} ${header}${msg}${footer}" | /usr/sbin/sendmail -F "Backup Report" -f ${admin} ${rcpt}

This original work is a major overhaul from the example report scripts in the Bacula project. Bill, I'd encourage you to submit for inclusion the the source code!

Thanks for contributing your enhancements

Thanks for sharing!

If you, or anyone who has submitted replies/enhancments would like to be properly credited, please send me an email and let me know. We have disabled account creation due to the number of bogus attempts by spam-bots etc.

Happy New Year!

Bill

HTML version

I've update the script with html tags, so you can receive the messages on email client without native support for fixed font size (gmail)

#!/bin/bash

admin="someone@example.com"
mybin="/usr/bin/mysql"
bcbin="/usr/sbin/bconsole"
bcconfig="/etc/bacula/bconsole.conf"
db="bacula"
dbuser="-u bacula"
# Uncomment and set db password if one is used
dbpass="-psomepassword"
server="dbserver.example.com"

# --------------------------------------------------
# Nothing should need to be modified below this line
# --------------------------------------------------

hist=${1}
if [ -z ${hist} ]; then
        echo "USE:"
        echo "baculabackupreport.sh "
        exit
fi

subject="Content-Type: text/html;
Subject: $server Bacula backup report for the past ${1} hours"

header="
<html><head><title>Backup history for the past ${1} hours</title></head><body style=\"font-size:12px\"><pre>
Backup History for the past ${1} hours
--------------------------------------

JobId        Name                  Start Time            Stop Time       Level  Status   Files       Bytes
-----   ---------------------  -------------------  -------------------  -----  ------  --------  -----------
"

msg=`echo "SELECT JobId, Name, StartTime, EndTime, Level, JobStatus, JobFiles, JobBytes \
                FROM Job \
                WHERE Type='B' \
                AND RealEndTime >=  DATE_ADD(NOW(), INTERVAL -${hist} HOUR) \
                ORDER BY JobId;" \
| ${mybin} ${dbuser} ${dbpass} ${db} \
| sed '/^JobId/d' \
| awk '{ printf("%-7s %-22s %s %-9s %s %-9s %5s %7s %9d %9.2f GB\n", $1, $2, $3, $4, $5, $6, $7, $8, $9, $10/(1024*1024*1024));}'`
footer="</pre></body></html>"
echo "${subject} ${header}${msg}${footer}" | /usr/sbin/sendmail -f ${admin} ${admin}

Bye!
mr.cesco

Modified for Postgres & MySql

We use Postgresql, so I've modified the script to allow either PostgreSql or MySql:


#!/bin/bash
#
# baculabackupreport.sh
#
# waa - 20130428 - Generate basic Bacula backup report
# ----------------------------------------------------
#
# ----------------------
# William A. Arlofski
# Reverse Polarity, LLC
# 860-824-2433 Office
# helpdesk@revpol.com
# http://www.revpol.com/
# ----------------------
#
# History
# -------
# 20130428 - Initial release
# Generate and email basic Bacula backup reports
# 1st command line parameter is expected to be a
# number of hours. No real error checking is done
# ----------------------------------------------------------
# 20130429 - Modified for PostgreSql
# Modified by Jonathan Bayer, Achieve 3000, to add ability
# to go against a PostgreSql database instead of a MySql
# database.

admin="admin@example.com"

#
# Select the database here
# Put in the cmd to invoke the database
#
#SQL="mysql"
SQL="psql"

mybin="/usr/bin/$SQL"
bcbin="/usr/sbin/bconsole"
bcconfig="/etc/bacula/bconsole.conf"
db="bacula_db"
dbuser="bacula"
# Uncomment and set db password if one is used
dbpass="password"
server="MyServerName"

# --------------------------------------------------
# Nothing should need to be modified below this line
# --------------------------------------------------

if [ "$SQL" = "psql" ]; then
echo "localhost:5432:$db:$dbuser:$dbpass" >.pgpass
dbuser="-U ${dbuser}"
dbpass=""
else
dbpass="-d${dbpass}"
dbuser="-u ${dbuser}"
fi

hist=${1}
if [ -z ${hist} ]; then
echo "USE:"
echo "baculabackupreport.sh "
exit
fi

subject="Subject: $server Bacula backup report for the past ${1} hours"
header="
Backup History for the past ${1} hours
--------------------------------------

JobId Name Type Level GB
----- ---- ---- ----- --------
"

if [ "$SQL" = "mysql" ]; then
sql="SELECT JobId, Name, Type, Level, JobBytes \
FROM Job \
WHERE Type='B' \
AND JobStatus='T' \
AND RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) \
ORDER BY JobId;"
fi

if [ "$SQL" = "psql" ]; then
jobs_since_seconds=$((hist * 3600))
sql="SELECT JobId, Name, Type, Level, JobBytes \
FROM Job \
WHERE Type='B' \
AND JobStatus='T' \
AND (date_part('epoch',RealEndTime) > date_part('epoch', now()) - ($jobs_since_seconds)) \
ORDER BY JobId;"
fi

msg=`echo "$sql" \
| ${mybin} ${dbuser} ${dbpass} ${db} \
| sed '/^JobId/d' \
| awk '{ printf("%7s %-18s %s %s %s %5.2f GB\n", $1, \
"\t"$2, "\t"$3, "\t"$4, "\t", $5/(1024*1024*1024));}'`
echo "${subject} ${header}${msg}" | sendmail -f ${admin} ${admin}

[ "$SQL" = "psql" ] && rm .pgpass

Thanks...

Thanks for that.

I was in a rush to get something out and had not considered adding in Postgresql support.

Include failed jobs?

Great stuff, however could you include the failed backup jobs as well? I think those are even more important than the ones that finished successfully.

Cheers, Uwe

Including failed jobs

Hi Uwe... Good Idea! I had meant to make a mention of the types of jobs to include and that I was only including the "Completely successful" jobs..

You may easily include the rest by changing the line:

AND JobStatus='T' \

to

AND (JobStatus='T' \
OR JobStatus='E'   \
OR JobStatus='e'   \
OR JObStatus='f'   \
OR JobStatus='A')  \

Keep in mind that this will not include any jobs Running, BLOCKED, waiting for a Client, waiting on storage etc. If you want any or all of those you may just add a new "OR" line into the script.

Alternately, you may include ALL jobs regardless of their status by removing the "OR JobStatus='T' " line altogether.

Hope this helps.

Post new comment

  • Web page addresses and e-mail addresses turn into links automatically.
  • Allowed HTML tags: <a> <em> <b> <i> <u> <strong> <cite> <code> <pre> <ul> <ol> <li> <dl> <dt> <dd>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
E
p
2
V
V
3
Enter the code without spaces and pay attention to upper/lower case.