#!/bin/sh
#
# baculabackupreport.sh
#
# ------------------------------------------------------------------------------
#
# waa - 20130428 - Initial release.
#                  Generate basic Bacula backup report.
#
# waa - 20170501 - Change Log moved to bottom of script.
#
# ------------------------------------------------------------------------------
#
# Copyright (c) 2013-2017, William A. Arlofski waa-at-revpol-dot-com
# All rights reserved.
#
# Redistribution and use in source and binary forms, with or without
# modification, are permitted provided that the following conditions are
# met:
#
# 1.  Redistributions of source code must retain the above copyright
# notice, this list of conditions and the following disclaimer.
#
# 2.  Redistributions in binary form must reproduce the above copyright
# notice, this list of conditions and the following disclaimer in the
# documentation and/or other materials provided with the distribution.
#
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS
# IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED
# TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
# PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
# HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
# SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
# TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR
# PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
# LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING
# NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
# SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
#
# ------------------------------------------------------------------------------


# System variables
# ----------------
server="@hostname@"
admin="@job_email@"
bcbin="/opt/bacula/bin/bconsole"
sendmail="/usr/sbin/sendmail"
bcconfig="/opt/bacula/etc/bconsole.conf"

# Database variables
# ------------------
dbtype="pgsql"                          # Supported options are pgsql, mysql, mariadb
db="bacula"
dbuser="bacula"
dbbin="/usr/bin/psql"
# dbpass="-pPassword"   # Uncomment and set db password if one is used

# Formatting variables
# --------------------
html="yes"                                                                              # Generate HTML emails instead of plain text emails?
boldstatus="yes"                                                        # Set <b> tag on Status field (only if html="yes")
colorstatusbg="yes"                                             # Colorize the Status cell's background? (only if html="yes")

jobtableheadercolor="#b0b0b0"   # Background color for the HTML table's header
jobtablejobcolor="#f4f4f4"              # Background color for the job rows in the HTML table
runningjobcolor="#4d79ff"                       # Background color of the Status cell for "Running" jobs
goodjobcolor="#00f000"                          # Background color of the Status cell for "OK" jobs
warnjobcolor="#ffff00"                          # Background color of the Status cell for "OK" jobs (with warnings - well, actually with 'joberrors')
badjobcolor="#cc3300"                                   # Background color of the Status cell for "bad" jobs
goodjobwitherrcolor="#cccc00"   # Background color of the Status cell for "OK" jobs (with errors) - Not implemented due to request

fontfamily="Verdana, Arial, Helvetica, sans-serif"      # Set the font family to use for HTML emails
fontsize="16px"                                                         # Set the font size to use for email title and print summaries
fontsizejobinfo="12px"                          # Set the font size to use for job information inside of table
fontsizesumlog="10px"                                   # Set the font size of bad logs and job summaries

printsummary="yes"                                              # Print a short summary after the job list table? (Total Jobs, Files & Bytes)
emailsummaries="no"                                             # Email all job summaries. Be careful with this, it can generate very large emails
emailbadlogs="yes"                                              # Email logs of bad jobs or jobs with JobErrors -ne 0. Be careful, this can generate very large emails.
addsubjecticon="yes"                                    # Prepend the email Subject with UTF-8 icons (a 'checkmark', 'circle with slash', or a bold 'x')
nojobsicon="=?utf-8?Q?=E2=8A=98?="              # utf-8 subject icon when no jobs have been run
goodjobsicon="=?utf-8?Q?=E2=9C=94?="    # utf-8 subject icon when all jobs were "OK"
badjobsicon="=?utf-8?Q?=E2=9C=96?="             # utf-8 subject icon when there are jobs with errors etc
starbadjobids="yes"                                             # Prepend an asterisk "*" to jobids of "bad" jobs
sortfield="EndTime"                                             # Which catalog db field to sort on? Multiple,fields,work,here
sortorder="DESC"                                                        # Which direction to sort?
emailtitle="Jobs Run On ${server} in the Past ${1} Hours"       # This is prepended at the top of the email, before the jobs table


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


hist=${1}
if [ -z ${hist} ]; then
        echo -e "\nUSE:\n$0 <history in hours>\n"
        exit 1
fi

if [ ! -e ${bcconfig} ]; then
        echo -e "\nThe bconsole configuration file does not seem to be '${bcconfig}'."
        echo -e "Please check the setting for the variable 'bcconfig'.\n"
        exit 1
fi

if [ ! -x ${bcbin} ]; then
        echo -e "\nThe bconsole binary does not seem to be '${bcbin}', or it is not executable."
        echo -e "Please check the setting for the variable 'bcbin'.\n"
        exit 1
fi

if [ ! -x ${dbbin} ]; then
        echo -e "\nThe database client binary does not seem to be '${dbbin}', or it is not executable."
        echo -e "Please check the setting for the variable 'dbbin'.\n"
        exit 1
fi

if [ ! -x ${sendmail} ]; then
        echo -e "\nThe sendmail binary does not seem to be '${sendmail}', or it is not executable."
        echo -e "Please check the setting for the variable 'sendmail'.\n"
        exit 1
fi


# Build query based on dbtype. Good thing we have "standards" Sigh...
# -------------------------------------------------------------------
case ${dbtype} in
        mysql )
                queryresult=$(echo "SELECT JobId, 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') \
                ORDER BY ${sortfield} ${sortorder};" \
                | ${dbbin} -u ${dbuser} ${dbpass} ${db} \
                | sed '/^JobId/d' )
                ;;

        pgsql )
                queryresult=$(echo "SELECT JobId, 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') \
                ORDER BY ${sortfield} ${sortorder};" \
                | ${dbbin} -U ${dbuser} ${dbpass} ${db} -0t \
                | sed -e 's/|//g' -e '/^$/d' )
                ;;

        mariadb )
                queryresult=$(echo "SELECT JobId, 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') \
                ORDER BY ${sortfield} ${sortorder};" \
                | ${dbbin} -u ${dbuser} -p${dbpass} ${db} -s -N )
                ;;

        * )
                echo "dbtype of '${dbtype}' is invalid. Please set dbtype variable to 'mysql', 'pgsql', or 'mariadb'"
                exit 1
                ;;
esac


# If we have no jobs to report on, then
# we need to skip the entire awk script
# and some bash stuff and jump all the
# way to about line 673
# -------------------------------------
if [ -z "${queryresult}" ]; then
        results="0"
        else
                results="1"


# Now for some fun with awk
# -------------------------
IFS=" "
msg=$(echo ${queryresult} | \
LC_ALL=en_US.UTF-8 \
awk \
-v html="${html}" \
-v boldstatus="${boldstatus}" \
-v colorstatusbg="${colorstatusbg}" \
-v jobtableheadercolor="${jobtableheadercolor}" \
-v jobtablejobcolor="${jobtablejobcolor}" \
-v runningjobcolor="${runningjobcolor}" \
-v goodjobcolor="${goodjobcolor}" \
-v goodjobwitherrcolor="${goodjobwitherrcolor}" \
-v warnjobcolor="${warnjobcolor}" \
-v badjobcolor="${badjobcolor}" \
-v printsummary="${printsummary}" \
-v starbadjobids="${starbadjobids}" \
'BEGIN { awkerr = 0 }
{star = " " }


                                                # List of possible jobstatus codes
                                                # --------------------------------
                                                # Enter SQL query: SELECT * FROM status;
                                                # +-----------+---------------------------------+----------+
                                                # | jobstatus | jobstatuslong                   | severity |
                                                # +-----------+---------------------------------+----------+
                                                # | C         | Created, not yet running        |       15 |
                                                # | R         | Running                         |       15 |
                                                # | B         | Blocked                         |       15 |
                                                # | T         | Completed successfully          |       10 |
                                                # | E         | Terminated with errors          |       25 |
                                                # | e         | Non-fatal error                 |       20 |
                                                # | f         | Fatal error                     |      100 |
                                                # | D         | Verify found differences        |       15 |
                                                # | A         | Canceled by user                |       90 |
                                                # | F         | Waiting for Client              |       15 |
                                                # | S         | Waiting for Storage daemon      |       15 |
                                                # | m         | Waiting for new media           |          |
                                                # | M         | Waiting for media mount         |       15 |
                                                # | s         | Waiting for storage resource    |       15 |
                                                # | j         | Waiting for job resource        |       15 |
                                                # | c         | Waiting for client resource     |       15 |
                                                # | d         | Waiting on maximum jobs         |       15 |
                                                # | t         | Waiting on start time           |       15 |
                                                # | p         | Waiting on higher priority jobs |       15 |
                                                # | a         | SD despooling attributes        |       15 |
                                                # | i         | Doing batch insert file records |       15 |
                                                # | I         | Incomplete Job                  |       25 |
                                                # +-----------+---------------------------------+----------+


                                                # Is this job still running?
                                                # If a job is still running, then there will be no "Stop Time"
                                                # fields, so $9 (jobstatus) will be shifted left two columns
                                                # to $7, and we will need to test and then reassign these variables
                                                # Note, this seems to be required for PostgreSQL, but MariaDB and
                                                # MySQL return all zeros for the date and time for running jobs
                                                # -----------------------------------------------------------------
                                                { if ($7 == "R" && $8 ~ /^[0-9]+/)
                                                        {
                                                                $13 = $10
                                                                $11 = $9
                                                                $10 = $8
                                                                $9  = $7
                                                                $8  = $6
                                                                $7  = $5
                                                                $5  = "--=Still Running=--"
                                                                $6  = ""
                                                        }
                                                }


                                                # Assign words to job status code characters
                                                # ------------------------------------------
                                                # First, check to see if we need to generate an HTML email
                                                { if (html == "yes")
                                                                {
                                                                        # Set default opening and closing tags for status cell
                                                                        # ----------------------------------------------------
                                                                        tdo = "<td align=\"center\">"
                                                                        tdc = "</td>"

                                                                        # Check to see if the job is "OK" then assign
                                                                        # the "goodjobcolor" to the cell background
                                                                        # -------------------------------------------
                                                                        if ($9 ~ /[T]/ && $13 == 0)
                                                                                {
                                                                                        if (colorstatusbg == "yes")
                                                                                                # Assign jobs that are OK or Running the goodjobcolor
                                                                                                # ---------------------------------------------------
                                                                                                {
                                                                                                        tdo = "<td align=\"center\" bgcolor=\"" goodjobcolor "\">"
                                                                                                }

                                                                                # Should the status be bolded?
                                                                                # ----------------------------
                                                                                if (boldstatus == "yes")
                                                                                        {
                                                                                                tdo=tdo"<b>"
                                                                                                tdc="</b>"tdc
                                                                                        }
                                                                                status["T"]=tdo"-OK-"tdc

                                                                                # If it is a good job, but with errors or warnings
                                                                                # then we will assign the warnjobcolor
                                                                                # ------------------------------------------------
                                                                                } else if ($9 == "T" && $13 != 0)
                                                                                        {
                                                                                                if (colorstatusbg == "yes")
                                                                                                        # Assign OK jobs with errors the warnjobcolor
                                                                                                        # -------------------------------------------
                                                                                                        {
                                                                                                                tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
                                                                                                        }

                                                                                                # Should the status be bolded?
                                                                                                # ----------------------------
                                                                                                if (boldstatus == "yes")
                                                                                                        {
                                                                                                                tdo=tdo"<b>"
                                                                                                                tdc="</b>"tdc
                                                                                                        }
                                                                                                # Since the "W" jobstatus never appears in the DB, we manually
                                                                                                # assign it here so it can be recognized later on in the script
                                                                                                # -------------------------------------------------------------
                                                                                                $9 = "W"
                                                                                                status["W"]=tdo"OK/Warnings"tdc

                                                                                # If the job is still running we will
                                                                                # assign it the runningjobcolor
                                                                                # -----------------------------------
                                                                                } else if ($9 == "R")
                                                                                        {
                                                                                                if (colorstatusbg == "yes")
                                                                                                        # Assign running jobs the runningjobcolor
                                                                                                        # ---------------------------------------
                                                                                                        {
                                                                                                                tdo = "<td align=\"center\" bgcolor=\"" runningjobcolor "\">"
                                                                                                        }

                                                                                                # Should the status be bolded?
                                                                                                # ----------------------------
                                                                                                if (boldstatus == "yes")
                                                                                                        {
                                                                                                                tdo=tdo"<b>"
                                                                                                                tdc="</b>"tdc
                                                                                                        }
                                                                                                status["R"]=tdo"Running"tdc

                                                                                # If it is a bad job, then
                                                                                # we assign the badjobcolor
                                                                                # -------------------------
                                                                                } else if ($9 ~ /[ABDef]/)
                                                                                        {
                                                                                                if (colorstatusbg == "yes")
                                                                                                        # Assign bad jobs the badjobcolor
                                                                                                        # -------------------------------
                                                                                                        {
                                                                                                                tdo = "<td align=\"center\" bgcolor=\"" badjobcolor "\">"
                                                                                                        }

                                                                                                # Should the status be bolded?
                                                                                                # ----------------------------
                                                                                                if (boldstatus == "yes")
                                                                                                        {
                                                                                                                tdo=tdo"<b>"
                                                                                                                tdc="</b>"tdc
                                                                                                        }
                                                                                                status["A"]=tdo"Aborted"tdc
                                                                                                status["D"]=tdo"Verify Diffs"tdc
                                                                                                status["f"]=tdo"Failed"tdc

                                                                                # If it is a job with warnings or errors, assign the job the warnjobcolor
                                                                                # I have never seen a "W" status in the db. Jobs that are "OK -- with warnings"
                                                                                # still have a "T" jobstatus, but the joberrors field is incremented in the db
                                                                                # -----------------------------------------------------------------------------
                                                                                } else if ($9 ~ /[EI]/)
                                                                                        {
                                                                                                if (colorstatusbg == "yes")
                                                                                                        # Assign job the warnjobcolor
                                                                                                        # ---------------------------
                                                                                                        {
                                                                                                                tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
                                                                                                        }

                                                                                                # Should the status be bolded?
                                                                                                # ----------------------------
                                                                                                if (boldstatus == "yes")
                                                                                                        {
                                                                                                                tdo=tdo"<b>"
                                                                                                                tdc="</b>"tdc
                                                                                                        }
                                                                                                status["E"]=tdo"OK, w/Errors"tdc
                                                                                                status["I"]=tdo"Incomplete"tdc
                                                                                        }
                                                                } else
                                                                # $html is not "yes" so statuses will be normal text
                                                                # --------------------------------------------------
                                                                        {
                                                                                status["A"]="     Aborted   "
                                                                                status["D"]="  Verify Diffs "
                                                                                status["E"]="  OK, w/Errors "
                                                                                status["f"]="     Failed    "
                                                                                status["I"]="   Incomplete  "
                                                                                status["R"]="     Running   "
                                                                                status["T"]="      -OK-     "
                                                                                # Since the "W" jobstatus never appears in the DB, we manually
                                                                                # assign it here so it can be recognized later on in the script
                                                                                # -------------------------------------------------------------
                                                                                if ($9 == "T" && $13 != 0)
                                                                                        { $9 = "W"
                                                                                                status["W"]="   OK/Warnings "
                                                                                        }
                                                                        }
                                                }


                                                # These status characters seem to only
                                                # be Director "in memory" statuses. They
                                                # do not get entered into the DB ever so we
                                                # cannot catch them with the db query we use
                                                # I might have to query the DIR as well as
                                                # the DB to be able to capture these
                                                # ------------------------------------------
                                                {
                                                        status["C"]="     Created   "
                                                        status["B"]="     Blocked   "
                                                        status["F"]="     Wait FD   "
                                                        status["S"]="     Wait SD   "
                                                        status["m"]=" Wait New Media"
                                                        status["M"]="   Wait Mount  "
                                                        status["s"]="   Wait Storage"
                                                        status["j"]="    Wait Job   "
                                                        status["c"]="   Wait Client "
                                                        status["d"]="  Wait Max Jobs"
                                                        status["t"]="Wait Start Time"
                                                        status["p"]="  Wait Priority"
                                                        status["a"]="  Despool Attrs"
                                                        status["i"]="  Batch Insert "
                                                        status["L"]="Spool Last Data"
                                                }


                                                # Assign words to job type code characters
                                                # ----------------------------------------
                                                {
                                                        jobtype["D"]="Admin"
                                                        jobtype["B"]="Backup"
                                                        jobtype["C"]="Copy"
                                                        jobtype["c"]="Control"
                                                        jobtype["R"]="Restore"
                                                        jobtype["V"]="Verify"
                                                }


                                                # Assign words to job level code characters
                                                # -----------------------------------------
                                                {
                                                        level["F"]="Full"
                                                        level["I"]="Incr"
                                                        level["D"]="Diff"
                                                        level["f"]="VFul"
                                                        level["-"]="----"
                                                }


                                                # Assign words to Verify job level code characters
                                                # ------------------------------------------------
                                                {
                                                        level["A"]="VVol"
                                                        level["C"]="VCat"
                                                        level["V"]="Init"
                                                        level["O"]="VV2C"
                                                        level["d"]="VD2C"
                                                }


                                                # Check to see if the job did not "T"erminate OK then increment $awkerr,
                                                # and prepend the JobId with an asterisk for quick visual identification
                                                # of problem jobs.

                                                # Need to choose between a positive or negative test of the job status code
                                                # -------------------------------------------------------------------------
                                                # Negative check - testing for non existence of all "good" status codes
                                                # $9 !~ /[TRCFSMmsjcdtpai]/ { awkerr++; $1 = "* "$1 }
                                                # Positive check - testing the existence of all "bad" status codes
                                                # good { if ($9 ~ /[ABDEIWef]/ || $13 != 0) { awkerr++; if (starbadjobids == "yes") { star  = "*" } } }
                                                { if ($9 ~ /[ABDEIef]/) { awkerr++; if (starbadjobids == "yes") { star  = "*" } } }


                                                # If the job is an Admin, Copy, Control,
                                                # Restore, or Migration job it will have
                                                # no real "Level", so we set it to "----"
                                                # ---------------------------------------
                                                { if ($7 ~ /[CcDRm]/) { $8 = "-" } }


                                                # Print out each job, formatted with the following fields:
                                                # JobId Name Status Errors Type Level Files Bytes StartTime EndTime RunTime
                                                # -------------------------------------------------------------------------
                                                { if (html == "yes")
                                                        { printf("<tr bgcolor=\"%s\"> \
                                                                <td align=\"center\">%s%s%s</td> \
                                                                <td>%s</td> \
                                                                %s \
                                                                <td align=\"right\">%'"'"'d</td> \
                                                                <td align=\"center\">%s</td> \
                                                                <td align=\"center\">%s</td> \
                                                                <td align=\"right\">%'"'"'d</td> \
                                                                <td align=\"right\">%'"'"'9.2f GB</td> \
                                                                <td align=\"center\">%s %s</td> \
                                                                <td align=\"center\">%s %s</td> \
                                                                <td align=\"center\">%s</td> \
                                                                </tr>\n", \
                                                                jobtablejobcolor, star, $1, star, $2, status[$9], $13, jobtype[$7], level[$8], $10, $11/(1024*1024*1024), $3, $4, $5, $6, $12);
                                                        } else
                                                                { printf("%s %-7s %-14s %16s %'"'"'12d %8s %6s %'"'"'9d %'"'"'9.2f GB %11s %-9s %-10s %-9s %-9s\n", \
                                                                        star, $1, $2, status[$9], $13, jobtype[$7], level[$8], $10, $11/(1024*1024*1024), $3, $4, $5, $6, $12);
                                                                }
                                                }


                                                # Count the number of jobs
                                                # ------------------------
                                                { totaljobs++ }


                                                # Count the number of files and bytes from all jobs
                                                # -------------------------------------------------
                                                { files += $10 }
                                                { bytes += $11 }


# Finally, print out the summaries
# --------------------------------
END {
if (printsummary == "yes")
        { if (html == "yes")
                {
                        printf("</table>")
                        printf("<br>\
                        <hr align=\"left\" width=\"25%\">\
                        <table width=\"25%\">\
                                <tr><td><b>Total Jobs</b></td><td  align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15d</b></td></tr>\
                                <tr><td><b>Total Files</b></td><td align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15d</b></td></tr>\
                                <tr><td><b>Total Bytes</b></td><td align=\"center\"><b>:</b></td> <td align=\"right\"><b>%'"'"'15.2f GB</b></td></tr>\
                        </table>\
                        <hr align=\"left\" width=\"25%\">",\
                        totaljobs, files, bytes/(1024*1024*1024));
                } else
printf("\
  =================================\n\
  Total Jobs  : %'"'"'15d\n\
  Total Files : %'"'"'15d\n\
  Total Bytes : %'"'"'15.2f GB\n\
  =================================\n",\
totaljobs, files, bytes/(1024*1024*1024));
} exit awkerr }
')


# Any failed jobs, or jobs with errors?
# -------------------------------------
numbadjobs=$?


# Do we email the job summaries?
# ------------------------------
if [ ${emailsummaries} == "yes" ]; then
        # Get all of the jobids from the query results, but
        # skip any running jobs because they will not have
        # a summary in the DB until the job has terminated
        # -------------------------------------------------
        alljobids=$(echo "${queryresult}" \
        | awk '{ if ($7 != "R") printf("%s ", $1) }')


        # If no jobids were returned, skip creating
        # the header and looping through zero records
        # -------------------------------------------
        if [ ! -z "${alljobids}" ]; then
                # Generate the header
                # -------------------
                msg="${msg}"$(
                if [ ${html} == "yes" ]; then
                        echo "<pre>====================================="
                                else
                                        echo -e "\n\n\n====================================="
                fi
                        echo "Job Summaries of All Terminated Jobs:"
                        echo "====================================="
                )


                # Get the job logs from all jobs and just grep for the summary
                # ------------------------------------------------------------
                for jobid in ${alljobids}; do
                        msg="${msg}"$(
                                echo -e "\n--------------"
                                echo "JobId: ${jobid}"
                                echo "--------------"
                                echo "llist joblog jobid=${jobid}" | ${bcbin} -c ${bcconfig} | grep -A31 "^  Build OS:"
                                echo "======================================================================"
                        )
                done
                if [ ${html} == "yes" ]; then
                        msg=${msg}$(echo "</pre>")
                fi
        fi
fi


# Do we email the bad job logs with the report?
# ---------------------------------------------
if [ ${emailbadlogs} == "yes" ]; then
        # Get the badjobs, or the good jobs with
        # JobErrors != 0 from the query results
        # --------------------------------------
        badjobids=$(echo "${queryresult}" \
        | awk '{ if ($9 ~ /[ABDEIef]/ || ($9 == "T" && $13 != 0)) printf("%s ", $1) }')


        # If no jobids were returned, skip creating
        # the header and looping through zero records
        # -------------------------------------------
        if [ ! -z "${badjobids}" ]; then
                # Generate the header
                # -------------------
                msg="${msg}"$(
                if [ ${html} == "yes" ]; then
                        echo "<pre>=========================================================="
                                else
                                        echo -e "\n\n\n=========================================================="
                fi
                        echo "Job logs of failed jobs, or good jobs with JobErrors != 0:"
                        echo "=========================================================="
                )


                # Get the bad job's log from the Director via bconsole
                # ----------------------------------------------------
                for jobid in ${badjobids}; do
                        msg="${msg}"$(
                                echo -e "\n--------------"
                                echo "JobId: ${jobid}"
                                echo "--------------"
                                echo "llist joblog jobid=${jobid}" | ${bcbin} -c ${bcconfig}
                                echo "======================================================================"
                        )
                done
                if [ ${html} == "yes" ]; then
                        msg=${msg}$(echo "</pre>")
                fi
        fi
fi


# Prepend the header to the $msg output
# -------------------------------------
if [ ${html} == "yes" ]; then
        msg="<html>
        <head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">
                <style>
                        body {font-family:$fontfamily; font-size:$fontsize;} td {font-size:$fontsizejobinfo;} pre {font-size:$fontsizesumlog;}
                </style>
        </head>
        <body>
                <p><u><b>${emailtitle}</b></u></p>
                <table width=\"98%\" align=\"center\" border=\"1\" cellpadding=\"2\" cellspacing=\"0\">
                        <tr bgcolor=\"${jobtableheadercolor}\">
                        <td align=\"center\"><b>Job ID</b></td>
                        <td align=\"center\"><b>Job Name</b></td>
                        <td align=\"center\"><b>Status</b></td>
                        <td align=\"center\"><b>Errors</b></td>
                        <td align=\"center\"><b>Type</b></td>
                        <td align=\"center\"><b>Level</b></td>
                        <td align=\"center\"><b>Files</b></td>
                        <td align=\"center\"><b>Bytes</b></td>
                        <td align=\"center\"><b>Start Time</b></td>
                        <td align=\"center\"><b>End Time</b></td>
                        <td align=\"center\"><b>Run Time</b></td>
                </tr>
${msg}
</body></html>"
        else
                msg="
  ${emailtitle}
  ------------------------------------------

  JobId      Job Name          Status         Errors     Type    Level   Files       Bytes         Start Time            End Time        Run Time
  -----   --------------   ---------------  ----------  -------  -----  --------  -----------  -------------------  -------------------  --------
${msg}"
fi

fi      # If there were zero results returned from the
                # SQL the query, we skip the entire awk script,
                # and a lot of other bash stuff that generates
                # the email body and we end up here
# -------------------------------------------------
if [ ${results} -eq 0 ]; then
        status="No Jobs Have Been Run"
        subjecticon="${nojobsicon}"
        msg="Nothing to see here..."
        else
                # Totally unnecessary, but, well...  OCD... :)
                # --------------------------------------------
                if [ ${numbadjobs} -ne 0 ]; then
                        if [ ${numbadjobs} -eq 1 ]; then
                                job="Job"
                                        else
                                                job="Jobs"
                        fi
                        status="(${numbadjobs}) ${job} with Errors"
                        subjecticon="${badjobsicon}"
                                else
                                        status="All Jobs OK"
                                        subjecticon="${goodjobsicon}"
                fi
fi


# More silliness
# --------------
if [ ${hist} -eq 1 ]; then
        hour="Hour"
        else
                hour="Hours"
fi


# Email the report
# ----------------
(
echo "To: ${admin}"
echo "From: ${admin}"
if [ ${addsubjecticon} == "yes" ]; then
        echo "Subject: ${subjecticon} ${server} - ${status} in the Past ${hist} ${hour}"
                else
                        echo "Subject: ${server} - ${status} in the Past ${hist} ${hour}"
fi
if [ ${html} == "yes" ]  &&  [ ${results} -ne 0 ]; then
 echo "Content-Type: text/html"
 echo "MIME-Version: 1.0"
fi
echo ""
echo "${msg}"
) | /usr/sbin/sendmail -t
# -------------
# End of script
# -------------


# ----------
# Change Log
# ----------
# ----------------------------
# William A. Arlofski
# Reverse Polarity, LLC
# helpdesk@revpol.com
# http://www.revpol.com/bacula
# ----------------------------
#
#
# 20130428 - Initial release
#            Generate and email a basic Bacula backup report
#            1st command line parameter is expected to be a
#            number of hours. No real error checking is done
#
# 20131224 - Removed "AND JobStatus='T'" to get all backup jobs
#            whether running, or completed with errors etc.
#          - Added Several fields "StartTime", "EndTime",
#            "JobFiles"
#          - Removed "JobType" because we are only selecting
#            jobs of type "Backup" (AND Type='B')
#          - Modified header lines and printf lines for better
#            formatting
#
# 20140107 - Modified script to include more information and cleaned
#            up the output formatting
#
# 20150704 - Added ability to work with MySQL or Postgresql
#
# 20150723 - Modified query, removed "Type='B'" clause to catch all jobs,
#            including Copy jobs, Admin jobs etc. Modified header, and
#            output string to match new query and include job's "Type"
#            column.
#
# 20170225 - Rewrote awk script so that a status/summary could be set in
#            the email report's subject. eg:
#            Subject: "serverName - All Jobs OK in the past x hours"
#            Subject: "serverName - x Jobs FAILED in the past y hours"
#
# 20170303 - Fixed output in cases where there are jobs running and there
#            is no "Stop Time" for a job.
#
# 20170406 - Some major modifications:
#             - Added feature to spell out words instead of using the
#               single character codes for Job type, Job Status, and
#               Job Level - Including the different levels for Verify
#               jobs
#             - If a job terminates with an error or warning, then the
#               job's line in the output is prepended with an asterisk
#               "*" for quick visual identification
#             - Modified the outputs of the files and bytes fields to
#               include commas when the number is > 999
#             - Added totals to the end of the report for Jobs, Files,
#               and Bytes
#             - Added $sortfield and $sortorder variables to allow output
#               to be sorted as desired
#             - Set the level of a job to "----" when the level is not
#               applicable as in Restore jobs, Admin jobs etc.
#
# 20170408 - Some minor cleanup, and moving things around
#          - Added $emailsummaries variable to append the job summaries
#            to the end of the report.
#          - Added $emailbadlogs variable to append full joblogs of jobs
#            which have failed or jobs with errors to the end of the report
#            for quick access to investigate failed jobs.
#
# 20170417 - Added some tests for binaries and the bconsole config file
#
# 20170429 - Thanks to Chris Couture for contacting me and submitting a
#            working query for MariaDB. I have added 'mariadb' as a new
#            dbtype option.
#          - Thanks to Chris Couture for the ideas and some code examples
#            to create an HTML email.
#              - Added $html variable to enable HTML emails.
#              - Added $boldstatus variable to make the Status <b>bold</b>
#                in HTML emails.
#              - Added $colorstatusbg variable to color the background of
#                the Status cell in HTML emails.
#          - Thanks to Chris Couture for the idea of adding RunTime
#            to the email output.
#          - Thanks to Chris Couture for the idea of using some unicode
#            characters (a 'checkmark'or a bold 'x') in the Subject:
#            to quickly see if everything ran OK.
#              - Added $addsubjecticon variable to enable/disable the
#                prepending of this icon to the Subject.
#          - Added $printsumary variable to give the option to print the
#            total Jobs, Files, and Bytes after the job listing table.
#          - Added $starbadjobids variable to enable/disable prepending
#            the bad jobids with an asterisk "*".
#          - Modified the way the email is built at the end. Thanks to
#            Chris Courture again for this nice idea.
#          - Added $jobtableheadercolor, $jobtablejobcolor, $goodjobcolor,
#            $goodjobwitherrcolor, $runningjobcolor, $warnjobcolor, and
#            $badjobcolor variables to colorize HTML emails
#          - Added $emailtitle variable for the title at the top
#          - Added $fontfamily, $fontsize, $fontsizejobinfo, and $fontsizesumlog
#            variables to allow styling of the HTML output (Thanks again Chris)
#          - Added $nojobsicon, $goodjobsicon, and $badjobsicon variables to
#            allow setting the prepended utf-8 subject icon character
#          - Reformatted things so that if there are no jobs returned by the
#            SQL query, the email message sent is nice and short
#          - Modified the license to allow for inclusion into Bacula Community,
#            and possibly the Enterprise Edition releases
#
# 20170430 - Modified the order of the fields to make more sense
#          - Re-aligned the text email so that when an asterisk is pre-pended it
#            does not shift the whole line
#
# 20170508 - Re-worked some of the logic so that good jobs (JobStatus="T") which
#            have errors will have their status listed as "OK/Warnings", and it
#            will not trigger as a "bad job" on the JobErrors, so it will not
#            have an asterisk prepended to the JobId in the job listing. I think
#            this fix is more of a temporary change in the hopes that a "W"
#            status to represent "good jobs with warnings" is implemented in the
#            db in the future.
#          - Added an "Errors" column to the table to show "JobErrors" from the
#            db.
#          - Some minor variable name changes and other minor changes
#
# 20170511 - Minor adjustments to the alignment formatting of the text email
#          - Minor 'case' changes to a couple levels (Init & VCat)
#
# ------------------------------------------------------------------------------


# I like small tabs. Use  :set list in vim to see tabbing etc
# vim: set tabstop=2:softtabstop=2:shiftwidth=2 #