Generating Basic Bacula Backup Email Summary Reports (Original/Deprecated)

Update: 20170408 - A lot of work has been done to this script over the past four years. What started out as a "simple bash shell script" now has a lot of new options. Please use the latest version.

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  --=Still Running=--      F       R         0         0 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.

*EDIT* - 20170225 Added the ability for the subject of the email report to show "All Jobs OK" or, to show "x Jobs FAILED". New version attached below as "baculabackupreport-20170225.sh".

*EDIT* - 20170303 Fixed false "failed" report, and display issue when job(s) were currently running. New version attached below as
"baculabackupreport-20170303.sh".

*EDIT* - 20170406 Major modifications including changing single character codes for Job Type, Level and Status to actual words, added the ability to sort on any field in ASC or DESC order, added commas to Files and Bytes > 999, prepend failed jobs or jobs with errors with an asterisk "*" so they are quickly identifiable, added totals for # of jobs, files, and bytes to the end of the report, and more. New version attached below as
"baculabackupreport-20170406.sh".

*EDIT* - 20170408Added an option to include the job summaries of terminated jobs. Also added an option to include the full job logs of failed jobs, or jobs with errors. New version attached below as
"baculabackupreport-20170408.sh".

Here is a sample of the latest output:

Email Subject: Newby - (2) Jobs with Errors in the past 24 hours

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

JobId        Name           Start Time            End Time          Type    Level      Status        Files       Bytes
-----   --------------  -------------------  -------------------   -------  -----  ---------------  --------  -----------
13202   Speedy          2017-04-06 14:54:33  --=Still Running=--    Backup   Incr       Running            0      0.00 GB
13204   Newby           2017-04-06 16:42:11  2017-04-06 16:42:33    Backup   Incr        -OK-             54      0.03 GB
* 13203 RestoreFiles    2017-04-06 14:55:09  2017-04-06 15:50:09   Restore   ----       Failed             0      0.00 GB
* 13201 Stinky          2017-04-06 11:03:14  2017-04-06 11:05:21    Backup   Diff       Aborted            0      0.00 GB
13200   Catalog         2017-04-06 04:01:50  2017-04-06 04:08:59    Backup   Full        -OK-            113     13.61 GB
13199   Zimbra8         2017-04-06 03:05:03  2017-04-06 03:56:48    Backup   Full        -OK-        655,944     38.94 GB
13192   Speedy          2017-04-05 20:00:22  2017-04-05 21:22:45    Backup   Incr        -OK-         28,594      7.01 GB
13187   dns1            2017-04-05 20:23:56  2017-04-05 20:37:34    Backup   Incr        -OK-             49      8.70 GB
13198   xymon           2017-04-05 20:18:55  2017-04-05 20:37:31    Backup   Incr        -OK-            781      8.43 GB
13186   jabber          2017-04-05 20:00:22  2017-04-05 20:34:41    Backup   Full        -OK-         66,000      1.00 GB
13188   helpdesk        2017-04-05 20:03:35  2017-04-05 20:29:48    Backup   Incr        -OK-          1,270      2.43 GB
13191   proxmox-a       2017-04-05 20:00:22  2017-04-05 20:29:47    Backup   Incr        -OK-            180      0.04 GB
13195   SpeedyMusic     2017-04-05 20:00:22  2017-04-05 20:25:31    Backup   Incr        -OK-              0      0.00 GB
13194   Downloads       2017-04-05 20:18:55  2017-04-05 20:23:52    Backup   Incr        -OK-              8      0.37 GB
13189   Newby           2017-04-05 20:00:22  2017-04-05 20:23:46    Backup   Incr        -OK-          3,399      0.03 GB
13196   SpeedyVMs       2017-04-05 20:00:22  2017-04-05 20:23:33    Backup   Incr        -OK-              0      0.00 GB
13190   Postbooks       2017-04-05 20:00:26  2017-04-05 20:23:33    Backup   Incr        -OK-             30      0.02 GB
13197   upsmon          2017-04-05 20:00:22  2017-04-05 20:18:31    Backup   Incr        -OK-             30      0.00 GB
13193   TheOffice       2017-04-05 20:00:22  2017-04-05 20:18:29    Backup   Incr        -OK-              0      0.00 GB
13185   UpdateSlots     2017-04-05 20:00:21  2017-04-05 20:00:21     Admin   ----        -OK-              0      0.00 GB
---------------------------------
Total Jobs  :              20
Total Files :         756,452
Total Bytes :           80.59 GB
AttachmentSize
baculabackupreport-20170303.sh5.23 KB
baculabackupreport-20170225.sh4.9 KB
baculabackupreport-20150704.sh4.05 KB
baculabackupreportV2.sh2.42 KB
baculabackupreport.sh1.58 KB
baculabackupreport-20170406.sh11.76 KB
baculabackupreport-20170408.sh15.35 KB

Comment viewing options

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

MariaDB

You're welcome.

Funny thing is that I started to add the "maraidb" option to the case statement, but since I do not have a MariaDB instance of Bacula here, I paused, and planned to add it a bit later.

Can you confirm that the SQL query you posted here still works? You may just email me directly - waa (at) revpol (d0t) com.

If yes, I will just cut and paste it into my script and release it with support for all 3 DBs.

Thanks!

Thanks again!

I wanted to thank you again for the script. I added MariaDB support again and also changed it to send in HTML format with tables and some CSS. Works great and is easier to read IMO.

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.
3
4
@
h
5
M
Enter the code without spaces and pay attention to upper/lower case.