3 # baculabackupreport.sh
5 # ------------------------------------------------------------------------------
7 # waa - 20130428 - Initial release.
8 # Generate basic Bacula backup report.
10 # waa - 20170501 - Change Log moved to bottom of script.
12 # ------------------------------------------------------------------------------
14 # Copyright (c) 2013-2017, William A. Arlofski waa-at-revpol-dot-com
15 # All rights reserved.
17 # Redistribution and use in source and binary forms, with or without
18 # modification, are permitted provided that the following conditions are
21 # 1. Redistributions of source code must retain the above copyright
22 # notice, this list of conditions and the following disclaimer.
24 # 2. Redistributions in binary form must reproduce the above copyright
25 # notice, this list of conditions and the following disclaimer in the
26 # documentation and/or other materials provided with the distribution.
28 # THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
29 # IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
30 # TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
31 # PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
32 # HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
33 # SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
34 # TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
35 # PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
36 # LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
37 # NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
38 # SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
40 # ------------------------------------------------------------------------------
47 bcbin="/opt/bacula/bin/bconsole"
48 sendmail="/usr/sbin/sendmail"
49 bcconfig="/opt/bacula/etc/bconsole.conf"
53 dbtype="pgsql" # Supported options are pgsql, mysql, mariadb
57 # dbpass="-pPassword" # Uncomment and set db password if one is used
59 # Formatting variables
60 # --------------------
61 html="yes" # Generate HTML emails instead of plain text emails?
62 boldstatus="yes" # Set <b> tag on Status field (only if html="yes")
63 colorstatusbg="yes" # Colorize the Status cell's background? (only if html="yes")
65 jobtableheadercolor="#b0b0b0" # Background color for the HTML table's header
66 jobtablejobcolor="#f4f4f4" # Background color for the job rows in the HTML table
67 runningjobcolor="#4d79ff" # Background color of the Status cell for "Running" jobs
68 goodjobcolor="#00f000" # Background color of the Status cell for "OK" jobs
69 warnjobcolor="#ffff00" # Background color of the Status cell for "OK" jobs (with warnings - well, actually with 'joberrors')
70 badjobcolor="#cc3300" # Background color of the Status cell for "bad" jobs
71 goodjobwitherrcolor="#cccc00" # Background color of the Status cell for "OK" jobs (with errors) - Not implemented due to request
73 fontfamily="Verdana, Arial, Helvetica, sans-serif" # Set the font family to use for HTML emails
74 fontsize="16px" # Set the font size to use for email title and print summaries
75 fontsizejobinfo="12px" # Set the font size to use for job information inside of table
76 fontsizesumlog="10px" # Set the font size of bad logs and job summaries
78 printsummary="yes" # Print a short summary after the job list table? (Total Jobs, Files & Bytes)
79 emailsummaries="no" # Email all job summaries. Be careful with this, it can generate very large emails
80 emailbadlogs="yes" # Email logs of bad jobs or jobs with JobErrors -ne 0. Be careful, this can generate very large emails.
81 addsubjecticon="yes" # Prepend the email Subject with UTF-8 icons (a 'checkmark', 'circle with slash', or a bold 'x')
82 nojobsicon="=?utf-8?Q?=E2=8A=98?=" # utf-8 subject icon when no jobs have been run
83 goodjobsicon="=?utf-8?Q?=E2=9C=94?=" # utf-8 subject icon when all jobs were "OK"
84 badjobsicon="=?utf-8?Q?=E2=9C=96?=" # utf-8 subject icon when there are jobs with errors etc
85 starbadjobids="yes" # Prepend an asterisk "*" to jobids of "bad" jobs
86 sortfield="EndTime" # Which catalog db field to sort on? Multiple,fields,work,here
87 sortorder="DESC" # Which direction to sort?
88 emailtitle="Jobs Run On ${server} in the Past ${1} Hours" # This is prepended at the top of the email, before the jobs table
91 # --------------------------------------------------
92 # Nothing should need to be modified below this line
93 # --------------------------------------------------
97 if [ -z ${hist} ]; then
98 echo -e "\nUSE:\n$0 <history in hours>\n"
102 if [ ! -e ${bcconfig} ]; then
103 echo -e "\nThe bconsole configuration file does not seem to be '${bcconfig}'."
104 echo -e "Please check the setting for the variable 'bcconfig'.\n"
108 if [ ! -x ${bcbin} ]; then
109 echo -e "\nThe bconsole binary does not seem to be '${bcbin}', or it is not executable."
110 echo -e "Please check the setting for the variable 'bcbin'.\n"
114 if [ ! -x ${dbbin} ]; then
115 echo -e "\nThe database client binary does not seem to be '${dbbin}', or it is not executable."
116 echo -e "Please check the setting for the variable 'dbbin'.\n"
120 if [ ! -x ${sendmail} ]; then
121 echo -e "\nThe sendmail binary does not seem to be '${sendmail}', or it is not executable."
122 echo -e "Please check the setting for the variable 'sendmail'.\n"
127 # Build query based on dbtype. Good thing we have "standards" Sigh...
128 # -------------------------------------------------------------------
131 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
132 TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
134 WHERE (RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) OR JobStatus='R') \
135 ORDER BY ${sortfield} ${sortorder};" \
136 | ${dbbin} -u ${dbuser} ${dbpass} ${db} \
141 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
142 AGE(EndTime, StartTime) as RunTime, JobErrors \
144 WHERE (RealEndTime >= CURRENT_TIMESTAMP(2) - cast('${hist} HOUR' as INTERVAL) OR JobStatus='R') \
145 ORDER BY ${sortfield} ${sortorder};" \
146 | ${dbbin} -U ${dbuser} ${dbpass} ${db} -0t \
147 | sed -e 's/|//g' -e '/^$/d' )
151 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
152 TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
154 WHERE (RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) OR JobStatus='R') \
155 ORDER BY ${sortfield} ${sortorder};" \
156 | ${dbbin} -u ${dbuser} -p${dbpass} ${db} -s -N )
160 echo "dbtype of '${dbtype}' is invalid. Please set dbtype variable to 'mysql', 'pgsql', or 'mariadb'"
166 # If we have no jobs to report on, then
167 # we need to skip the entire awk script
168 # and some bash stuff and jump all the
169 # way to about line 673
170 # -------------------------------------
171 if [ -z "${queryresult}" ]; then
177 # Now for some fun with awk
178 # -------------------------
180 msg=$(echo ${queryresult} | \
184 -v boldstatus="${boldstatus}" \
185 -v colorstatusbg="${colorstatusbg}" \
186 -v jobtableheadercolor="${jobtableheadercolor}" \
187 -v jobtablejobcolor="${jobtablejobcolor}" \
188 -v runningjobcolor="${runningjobcolor}" \
189 -v goodjobcolor="${goodjobcolor}" \
190 -v goodjobwitherrcolor="${goodjobwitherrcolor}" \
191 -v warnjobcolor="${warnjobcolor}" \
192 -v badjobcolor="${badjobcolor}" \
193 -v printsummary="${printsummary}" \
194 -v starbadjobids="${starbadjobids}" \
195 'BEGIN { awkerr = 0 }
199 # List of possible jobstatus codes
200 # --------------------------------
201 # Enter SQL query: SELECT * FROM status;
202 # +-----------+---------------------------------+----------+
203 # | jobstatus | jobstatuslong | severity |
204 # +-----------+---------------------------------+----------+
205 # | C | Created, not yet running | 15 |
206 # | R | Running | 15 |
207 # | B | Blocked | 15 |
208 # | T | Completed successfully | 10 |
209 # | E | Terminated with errors | 25 |
210 # | e | Non-fatal error | 20 |
211 # | f | Fatal error | 100 |
212 # | D | Verify found differences | 15 |
213 # | A | Canceled by user | 90 |
214 # | F | Waiting for Client | 15 |
215 # | S | Waiting for Storage daemon | 15 |
216 # | m | Waiting for new media | |
217 # | M | Waiting for media mount | 15 |
218 # | s | Waiting for storage resource | 15 |
219 # | j | Waiting for job resource | 15 |
220 # | c | Waiting for client resource | 15 |
221 # | d | Waiting on maximum jobs | 15 |
222 # | t | Waiting on start time | 15 |
223 # | p | Waiting on higher priority jobs | 15 |
224 # | a | SD despooling attributes | 15 |
225 # | i | Doing batch insert file records | 15 |
226 # | I | Incomplete Job | 25 |
227 # +-----------+---------------------------------+----------+
230 # Is this job still running?
231 # If a job is still running, then there will be no "Stop Time"
232 # fields, so $9 (jobstatus) will be shifted left two columns
233 # to $7, and we will need to test and then reassign these variables
234 # Note, this seems to be required for PostgreSQL, but MariaDB and
235 # MySQL return all zeros for the date and time for running jobs
236 # -----------------------------------------------------------------
237 { if ($7 == "R" && $8 ~ /^[0-9]+/)
245 $5 = "--=Still Running=--"
251 # Assign words to job status code characters
252 # ------------------------------------------
253 # First, check to see if we need to generate an HTML email
256 # Set default opening and closing tags for status cell
257 # ----------------------------------------------------
258 tdo = "<td align=\"center\">"
261 # Check to see if the job is "OK" then assign
262 # the "goodjobcolor" to the cell background
263 # -------------------------------------------
264 if ($9 ~ /[T]/ && $13 == 0)
266 if (colorstatusbg == "yes")
267 # Assign jobs that are OK or Running the goodjobcolor
268 # ---------------------------------------------------
270 tdo = "<td align=\"center\" bgcolor=\"" goodjobcolor "\">"
273 # Should the status be bolded?
274 # ----------------------------
275 if (boldstatus == "yes")
280 status["T"]=tdo"-OK-"tdc
282 # If it is a good job, but with errors or warnings
283 # then we will assign the warnjobcolor
284 # ------------------------------------------------
285 } else if ($9 == "T" && $13 != 0)
287 if (colorstatusbg == "yes")
288 # Assign OK jobs with errors the warnjobcolor
289 # -------------------------------------------
291 tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
294 # Should the status be bolded?
295 # ----------------------------
296 if (boldstatus == "yes")
301 # Since the "W" jobstatus never appears in the DB, we manually
302 # assign it here so it can be recognized later on in the script
303 # -------------------------------------------------------------
305 status["W"]=tdo"OK/Warnings"tdc
307 # If the job is still running we will
308 # assign it the runningjobcolor
309 # -----------------------------------
310 } else if ($9 == "R")
312 if (colorstatusbg == "yes")
313 # Assign running jobs the runningjobcolor
314 # ---------------------------------------
316 tdo = "<td align=\"center\" bgcolor=\"" runningjobcolor "\">"
319 # Should the status be bolded?
320 # ----------------------------
321 if (boldstatus == "yes")
326 status["R"]=tdo"Running"tdc
328 # If it is a bad job, then
329 # we assign the badjobcolor
330 # -------------------------
331 } else if ($9 ~ /[ABDef]/)
333 if (colorstatusbg == "yes")
334 # Assign bad jobs the badjobcolor
335 # -------------------------------
337 tdo = "<td align=\"center\" bgcolor=\"" badjobcolor "\">"
340 # Should the status be bolded?
341 # ----------------------------
342 if (boldstatus == "yes")
347 status["A"]=tdo"Aborted"tdc
348 status["D"]=tdo"Verify Diffs"tdc
349 status["f"]=tdo"Failed"tdc
351 # If it is a job with warnings or errors, assign the job the warnjobcolor
352 # I have never seen a "W" status in the db. Jobs that are "OK -- with warnings"
353 # still have a "T" jobstatus, but the joberrors field is incremented in the db
354 # -----------------------------------------------------------------------------
355 } else if ($9 ~ /[EI]/)
357 if (colorstatusbg == "yes")
358 # Assign job the warnjobcolor
359 # ---------------------------
361 tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
364 # Should the status be bolded?
365 # ----------------------------
366 if (boldstatus == "yes")
371 status["E"]=tdo"OK, w/Errors"tdc
372 status["I"]=tdo"Incomplete"tdc
375 # $html is not "yes" so statuses will be normal text
376 # --------------------------------------------------
378 status["A"]=" Aborted "
379 status["D"]=" Verify Diffs "
380 status["E"]=" OK, w/Errors "
381 status["f"]=" Failed "
382 status["I"]=" Incomplete "
383 status["R"]=" Running "
385 # Since the "W" jobstatus never appears in the DB, we manually
386 # assign it here so it can be recognized later on in the script
387 # -------------------------------------------------------------
388 if ($9 == "T" && $13 != 0)
390 status["W"]=" OK/Warnings "
396 # These status characters seem to only
397 # be Director "in memory" statuses. They
398 # do not get entered into the DB ever so we
399 # cannot catch them with the db query we use
400 # I might have to query the DIR as well as
401 # the DB to be able to capture these
402 # ------------------------------------------
404 status["C"]=" Created "
405 status["B"]=" Blocked "
406 status["F"]=" Wait FD "
407 status["S"]=" Wait SD "
408 status["m"]=" Wait New Media"
409 status["M"]=" Wait Mount "
410 status["s"]=" Wait Storage"
411 status["j"]=" Wait Job "
412 status["c"]=" Wait Client "
413 status["d"]=" Wait Max Jobs"
414 status["t"]="Wait Start Time"
415 status["p"]=" Wait Priority"
416 status["a"]=" Despool Attrs"
417 status["i"]=" Batch Insert "
418 status["L"]="Spool Last Data"
422 # Assign words to job type code characters
423 # ----------------------------------------
426 jobtype["B"]="Backup"
428 jobtype["c"]="Control"
429 jobtype["R"]="Restore"
430 jobtype["V"]="Verify"
434 # Assign words to job level code characters
435 # -----------------------------------------
445 # Assign words to Verify job level code characters
446 # ------------------------------------------------
456 # Check to see if the job did not "T"erminate OK then increment $awkerr,
457 # and prepend the JobId with an asterisk for quick visual identification
460 # Need to choose between a positive or negative test of the job status code
461 # -------------------------------------------------------------------------
462 # Negative check - testing for non existence of all "good" status codes
463 # $9 !~ /[TRCFSMmsjcdtpai]/ { awkerr++; $1 = "* "$1 }
464 # Positive check - testing the existence of all "bad" status codes
465 # good { if ($9 ~ /[ABDEIWef]/ || $13 != 0) { awkerr++; if (starbadjobids == "yes") { star = "*" } } }
466 { if ($9 ~ /[ABDEIef]/) { awkerr++; if (starbadjobids == "yes") { star = "*" } } }
469 # If the job is an Admin, Copy, Control,
470 # Restore, or Migration job it will have
471 # no real "Level", so we set it to "----"
472 # ---------------------------------------
473 { if ($7 ~ /[CcDRm]/) { $8 = "-" } }
476 # Print out each job, formatted with the following fields:
477 # JobId Name Status Errors Type Level Files Bytes StartTime EndTime RunTime
478 # -------------------------------------------------------------------------
480 { printf("<tr bgcolor=\"%s\"> \
481 <td align=\"center\">%s%s%s</td> \
484 <td align=\"right\">%'"'"'d</td> \
485 <td align=\"center\">%s</td> \
486 <td align=\"center\">%s</td> \
487 <td align=\"right\">%'"'"'d</td> \
488 <td align=\"right\">%'"'"'9.2f GB</td> \
489 <td align=\"center\">%s %s</td> \
490 <td align=\"center\">%s %s</td> \
491 <td align=\"center\">%s</td> \
493 jobtablejobcolor, star, $1, star, $2, status[$9], $13, jobtype[$7], level[$8], $10, $11/(1024*1024*1024), $3, $4, $5, $6, $12);
495 { printf("%s %-7s %-14s %16s %'"'"'12d %8s %6s %'"'"'9d %'"'"'9.2f GB %11s %-9s %-10s %-9s %-9s\n", \
496 star, $1, $2, status[$9], $13, jobtype[$7], level[$8], $10, $11/(1024*1024*1024), $3, $4, $5, $6, $12);
501 # Count the number of jobs
502 # ------------------------
506 # Count the number of files and bytes from all jobs
507 # -------------------------------------------------
512 # Finally, print out the summaries
513 # --------------------------------
515 if (printsummary == "yes")
520 <hr align=\"left\" width=\"25%\">\
521 <table width=\"25%\">\
522 <tr><td><b>Total Jobs</b></td><td align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15d</b></td></tr>\
523 <tr><td><b>Total Files</b></td><td align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15d</b></td></tr>\
524 <tr><td><b>Total Bytes</b></td><td align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15.2f GB</b></td></tr>\
526 <hr align=\"left\" width=\"25%\">",\
527 totaljobs, files, bytes/(1024*1024*1024));
530 =================================\n\
531 Total Jobs : %'"'"'15d\n\
532 Total Files : %'"'"'15d\n\
533 Total Bytes : %'"'"'15.2f GB\n\
534 =================================\n",\
535 totaljobs, files, bytes/(1024*1024*1024));
540 # Any failed jobs, or jobs with errors?
541 # -------------------------------------
545 # Do we email the job summaries?
546 # ------------------------------
547 if [ ${emailsummaries} == "yes" ]; then
548 # Get all of the jobids from the query results, but
549 # skip any running jobs because they will not have
550 # a summary in the DB until the job has terminated
551 # -------------------------------------------------
552 alljobids=$(echo "${queryresult}" \
553 | awk '{ if ($7 != "R") printf("%s ", $1) }')
556 # If no jobids were returned, skip creating
557 # the header and looping through zero records
558 # -------------------------------------------
559 if [ ! -z "${alljobids}" ]; then
560 # Generate the header
561 # -------------------
563 if [ ${html} == "yes" ]; then
564 echo "<pre>====================================="
566 echo -e "\n\n\n====================================="
568 echo "Job Summaries of All Terminated Jobs:"
569 echo "====================================="
573 # Get the job logs from all jobs and just grep for the summary
574 # ------------------------------------------------------------
575 for jobid in ${alljobids}; do
577 echo -e "\n--------------"
578 echo "JobId: ${jobid}"
579 echo "--------------"
580 echo "llist joblog jobid=${jobid}" | ${bcbin} -c ${bcconfig} | grep -A31 "^ Build OS:"
581 echo "======================================================================"
584 if [ ${html} == "yes" ]; then
585 msg=${msg}$(echo "</pre>")
591 # Do we email the bad job logs with the report?
592 # ---------------------------------------------
593 if [ ${emailbadlogs} == "yes" ]; then
594 # Get the badjobs, or the good jobs with
595 # JobErrors != 0 from the query results
596 # --------------------------------------
597 badjobids=$(echo "${queryresult}" \
598 | awk '{ if ($9 ~ /[ABDEIef]/ || ($9 == "T" && $13 != 0)) printf("%s ", $1) }')
601 # If no jobids were returned, skip creating
602 # the header and looping through zero records
603 # -------------------------------------------
604 if [ ! -z "${badjobids}" ]; then
605 # Generate the header
606 # -------------------
608 if [ ${html} == "yes" ]; then
609 echo "<pre>=========================================================="
611 echo -e "\n\n\n=========================================================="
613 echo "Job logs of failed jobs, or good jobs with JobErrors != 0:"
614 echo "=========================================================="
618 # Get the bad job's log from the Director via bconsole
619 # ----------------------------------------------------
620 for jobid in ${badjobids}; do
622 echo -e "\n--------------"
623 echo "JobId: ${jobid}"
624 echo "--------------"
625 echo "llist joblog jobid=${jobid}" | ${bcbin} -c ${bcconfig}
626 echo "======================================================================"
629 if [ ${html} == "yes" ]; then
630 msg=${msg}$(echo "</pre>")
636 # Prepend the header to the $msg output
637 # -------------------------------------
638 if [ ${html} == "yes" ]; then
640 <head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">
642 body {font-family:$fontfamily; font-size:$fontsize;} td {font-size:$fontsizejobinfo;} pre {font-size:$fontsizesumlog;}
646 <p><u><b>${emailtitle}</b></u></p>
647 <table width=\"98%\" align=\"center\" border=\"1\" cellpadding=\"2\" cellspacing=\"0\">
648 <tr bgcolor=\"${jobtableheadercolor}\">
649 <td align=\"center\"><b>Job ID</b></td>
650 <td align=\"center\"><b>Job Name</b></td>
651 <td align=\"center\"><b>Status</b></td>
652 <td align=\"center\"><b>Errors</b></td>
653 <td align=\"center\"><b>Type</b></td>
654 <td align=\"center\"><b>Level</b></td>
655 <td align=\"center\"><b>Files</b></td>
656 <td align=\"center\"><b>Bytes</b></td>
657 <td align=\"center\"><b>Start Time</b></td>
658 <td align=\"center\"><b>End Time</b></td>
659 <td align=\"center\"><b>Run Time</b></td>
666 ------------------------------------------
668 JobId Job Name Status Errors Type Level Files Bytes Start Time End Time Run Time
669 ----- -------------- --------------- ---------- ------- ----- -------- ----------- ------------------- ------------------- --------
673 fi # If there were zero results returned from the
674 # SQL the query, we skip the entire awk script,
675 # and a lot of other bash stuff that generates
676 # the email body and we end up here
677 # -------------------------------------------------
678 if [ ${results} -eq 0 ]; then
679 status="No Jobs Have Been Run"
680 subjecticon="${nojobsicon}"
681 msg="Nothing to see here..."
683 # Totally unnecessary, but, well... OCD... :)
684 # --------------------------------------------
685 if [ ${numbadjobs} -ne 0 ]; then
686 if [ ${numbadjobs} -eq 1 ]; then
691 status="(${numbadjobs}) ${job} with Errors"
692 subjecticon="${badjobsicon}"
695 subjecticon="${goodjobsicon}"
702 if [ ${hist} -eq 1 ]; then
713 echo "From: ${admin}"
714 if [ ${addsubjecticon} == "yes" ]; then
715 echo "Subject: ${subjecticon} ${server} - ${status} in the Past ${hist} ${hour}"
717 echo "Subject: ${server} - ${status} in the Past ${hist} ${hour}"
719 if [ ${html} == "yes" ] && [ ${results} -ne 0 ]; then
720 echo "Content-Type: text/html"
721 echo "MIME-Version: 1.0"
725 ) | /usr/sbin/sendmail -t
734 # ----------------------------
735 # William A. Arlofski
736 # Reverse Polarity, LLC
737 # helpdesk@revpol.com
738 # http://www.revpol.com/bacula
739 # ----------------------------
742 # 20130428 - Initial release
743 # Generate and email a basic Bacula backup report
744 # 1st command line parameter is expected to be a
745 # number of hours. No real error checking is done
747 # 20131224 - Removed "AND JobStatus='T'" to get all backup jobs
748 # whether running, or completed with errors etc.
749 # - Added Several fields "StartTime", "EndTime",
751 # - Removed "JobType" because we are only selecting
752 # jobs of type "Backup" (AND Type='B')
753 # - Modified header lines and printf lines for better
756 # 20140107 - Modified script to include more information and cleaned
757 # up the output formatting
759 # 20150704 - Added ability to work with MySQL or Postgresql
761 # 20150723 - Modified query, removed "Type='B'" clause to catch all jobs,
762 # including Copy jobs, Admin jobs etc. Modified header, and
763 # output string to match new query and include job's "Type"
766 # 20170225 - Rewrote awk script so that a status/summary could be set in
767 # the email report's subject. eg:
768 # Subject: "serverName - All Jobs OK in the past x hours"
769 # Subject: "serverName - x Jobs FAILED in the past y hours"
771 # 20170303 - Fixed output in cases where there are jobs running and there
772 # is no "Stop Time" for a job.
774 # 20170406 - Some major modifications:
775 # - Added feature to spell out words instead of using the
776 # single character codes for Job type, Job Status, and
777 # Job Level - Including the different levels for Verify
779 # - If a job terminates with an error or warning, then the
780 # job's line in the output is prepended with an asterisk
781 # "*" for quick visual identification
782 # - Modified the outputs of the files and bytes fields to
783 # include commas when the number is > 999
784 # - Added totals to the end of the report for Jobs, Files,
786 # - Added $sortfield and $sortorder variables to allow output
787 # to be sorted as desired
788 # - Set the level of a job to "----" when the level is not
789 # applicable as in Restore jobs, Admin jobs etc.
791 # 20170408 - Some minor cleanup, and moving things around
792 # - Added $emailsummaries variable to append the job summaries
793 # to the end of the report.
794 # - Added $emailbadlogs variable to append full joblogs of jobs
795 # which have failed or jobs with errors to the end of the report
796 # for quick access to investigate failed jobs.
798 # 20170417 - Added some tests for binaries and the bconsole config file
800 # 20170429 - Thanks to Chris Couture for contacting me and submitting a
801 # working query for MariaDB. I have added 'mariadb' as a new
803 # - Thanks to Chris Couture for the ideas and some code examples
804 # to create an HTML email.
805 # - Added $html variable to enable HTML emails.
806 # - Added $boldstatus variable to make the Status <b>bold</b>
808 # - Added $colorstatusbg variable to color the background of
809 # the Status cell in HTML emails.
810 # - Thanks to Chris Couture for the idea of adding RunTime
811 # to the email output.
812 # - Thanks to Chris Couture for the idea of using some unicode
813 # characters (a 'checkmark'or a bold 'x') in the Subject:
814 # to quickly see if everything ran OK.
815 # - Added $addsubjecticon variable to enable/disable the
816 # prepending of this icon to the Subject.
817 # - Added $printsumary variable to give the option to print the
818 # total Jobs, Files, and Bytes after the job listing table.
819 # - Added $starbadjobids variable to enable/disable prepending
820 # the bad jobids with an asterisk "*".
821 # - Modified the way the email is built at the end. Thanks to
822 # Chris Courture again for this nice idea.
823 # - Added $jobtableheadercolor, $jobtablejobcolor, $goodjobcolor,
824 # $goodjobwitherrcolor, $runningjobcolor, $warnjobcolor, and
825 # $badjobcolor variables to colorize HTML emails
826 # - Added $emailtitle variable for the title at the top
827 # - Added $fontfamily, $fontsize, $fontsizejobinfo, and $fontsizesumlog
828 # variables to allow styling of the HTML output (Thanks again Chris)
829 # - Added $nojobsicon, $goodjobsicon, and $badjobsicon variables to
830 # allow setting the prepended utf-8 subject icon character
831 # - Reformatted things so that if there are no jobs returned by the
832 # SQL query, the email message sent is nice and short
833 # - Modified the license to allow for inclusion into Bacula Community,
834 # and possibly the Enterprise Edition releases
836 # 20170430 - Modified the order of the fields to make more sense
837 # - Re-aligned the text email so that when an asterisk is pre-pended it
838 # does not shift the whole line
840 # 20170508 - Re-worked some of the logic so that good jobs (JobStatus="T") which
841 # have errors will have their status listed as "OK/Warnings", and it
842 # will not trigger as a "bad job" on the JobErrors, so it will not
843 # have an asterisk prepended to the JobId in the job listing. I think
844 # this fix is more of a temporary change in the hopes that a "W"
845 # status to represent "good jobs with warnings" is implemented in the
847 # - Added an "Errors" column to the table to show "JobErrors" from the
849 # - Some minor variable name changes and other minor changes
851 # 20170511 - Minor adjustments to the alignment formatting of the text email
852 # - Minor 'case' changes to a couple levels (Init & VCat)
854 # ------------------------------------------------------------------------------
857 # I like small tabs. Use :set list in vim to see tabbing etc
858 # vim: set tabstop=2:softtabstop=2:shiftwidth=2 #