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-20171202.sh38.77 KB

Comment viewing options

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

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.
{
1
a
H
7
^
Enter the code without spaces and pay attention to upper/lower case.