Generating Basic Bacula Backup Email Summary Reports (Updated Version)

In April of 2013, 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. Over time, many features were added to this script and it has evolved quite a bit over the past 4 years. The original posting about this script may be found here: http://www.revpol.com/baculasummaryemails-ORIG

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 plain text version of this email report would something look like this:



The HTML version of this email report would look something like this:

AttachmentSize
baculabackupreport-20180919.sh41.25 KB

Comment viewing options

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

remote MySQL patch

Thanks for the script Bill, just what I was after! :)

Have a tiny patch for remote MySQL databases.

157a158,160
>               if [ "x" != "x${dbhost}" ]; then
>                       dbhost="-h ${dbhost}"
>               fi
163c166
<               | ${dbbin} -u ${dbuser} ${dbpass} ${db} \
---
>               | ${dbbin} ${dbhost} -u ${dbuser} ${dbpass} 

-- Fatman

Great script!

Hi, Bill

Your script is excellent, helped me very much.

Why don't you put it to any public git repository (gitlab/github/e.t.c.)?

Best regards,

Jean Tomáz.

Upload to Git repo...

Hi Jean,

Thank you for the compliment.

This is at least the second time I have seen this suggestion. I think I do have a github account already, so this is probably where it will end up - unless someone else has already done so - it is open source you know. :)

Best regards,

Bill

debian - connect errror

hi,

on debian with postgres i have to install gawk and do a 'export PGPASSWORD='
and delete the $dbpass from line 173
line 173 looks like : ${dbbin} -U ${dbuser} -h localhost -d ${db} -0t \

thanks for tis great script

senti

RE: debian - connect error

Hi Senti,

Thanks for the comment. Hopefully it might help some Debian users in the future.

Best regards,

Bill

Public git repository

Thank you very much for the script!
Could you, please, put it to any public git repository (gitlab/github/e.t.c.)?

RE: Public git repository

Sounds like a good idea :)

When I have a few minutes, I think I will do just that!

Best regards,

Bill

Trouble with script

Hello,
Very interested in your bacula backup report script. Just downloaded the current version and tried running it against my setup, but got a "psql: FATAL: Peer authentication failed for user "bacula"
" error. Which is very strange because user bacula is the same as in my bacula-dir.conf file and that runs every night.

Any suggestions on what may be happening?

Thanks for you work on the script by the way! It looks really useful!

Tom Plancon
BKA Architects, Inc.

Postgresql authentication

Hi Tom,

Have you tried running the script as the bacula user?

root@host # su - bacula

bacula@host $  /full/path/to/baculabackupreport.sh 24

My guess is that with "peer" authentication in postgresql, you cannot run this script as the root user because the script is going to run the psql command as user "root", but tell the postgresql database server that it is user "bacula".

There are other types of authentication settings for postgresql, and you can edit the pg_hba.conf and pg_ident.conf files to configure this to allow, for example, root unix user to log in as bacula db user.

If you are not too concerned about outside access to this DB server, you can set the postgresql auth to "trust" and this problem disappears.

Also, I run this nightly from the bacula user's cron, so this problem you are seeing would also disappear since the bacula user will be the one running it:

# crontab -u bacula -e (edit bacula user's cron)

add the following lines and save it to run it at 8am every day:

# waa - 20171124 - To make 100% sure that the baculabacupreport.sh script is POSIX compliant, don't use /bin/bash
SHELL=/bin/dash
MAILTO=some_valid_email@some_valid_domain.com

0 8 * * * /full/path/to/baculabackupreport.sh 24

Thanks for the patch!

Spaces in file names, spaces in job names... Something I always avoid by using camelCase, or like you by using underscores instead of spaces, so I never even thought of that.

The next release will include this fix.

Thanks for contributing.

Best regards,

Bill

Patch for job Names with spaces

--- baculabackupreport-20170515.sh	2017-05-15 19:15:00.000000000 +0200
+++ baculabackupreport-20170701.sh	2017-07-01 16:16:35.811119457 +0200
@@ -128,7 +129,7 @@
 # -------------------------------------------------------------------
 case ${dbtype} in
 	mysql )
-		queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
+		queryresult=$(echo "SELECT JobId,  REPLACE(Name,' ','_'), StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 		TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
 		FROM Job \
 		WHERE (RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) OR JobStatus='R') \
@@ -138,7 +139,7 @@
 		;;

 	pgsql )
-		queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
+		queryresult=$(echo "SELECT JobId, REPLACE(Name,' ','_'), StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 		AGE(EndTime, StartTime) as RunTime, JobErrors \
 		FROM Job \
 		WHERE (RealEndTime >= CURRENT_TIMESTAMP(2) - cast('${hist} HOUR' as INTERVAL) OR JobStatus='R') \
@@ -148,7 +149,7 @@
 		;;

 	mariadb )
-		queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
+		queryresult=$(echo "SELECT JobId, REPLACE(Name,' ','_'), StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 		TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
 		FROM Job \
 		WHERE (RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) OR JobStatus='R') \
@@ -174,6 +175,7 @@
 		results="1"


+echo ${queryresult}
 # Now for some fun with awk
 # -------------------------
 IFS=" "

Regards,
Sandro W.

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.
a
E
Q
9
5
p
Enter the code without spaces and pay attention to upper/lower case.