git.sur5r.net Git - bacula/bacula/blob - bacula/scripts/baculabackupreport.in

   1 #!/bin/sh
   2 #
   3 # baculabackupreport.sh
   4 #
   5 # ------------------------------------------------------------------------------
   6 #
   7 # waa - 20130428 - Initial release.
   8 #                  Generate basic Bacula backup report.
   9 #
  10 # waa - 20170501 - Change Log moved to bottom of script.
  11 #
  12 # ------------------------------------------------------------------------------
  13 #
  14 # Copyright (c) 2013-2017, William A. Arlofski waa-at-revpol-dot-com
  15 # All rights reserved.
  16 #
  17 # Redistribution and use in source and binary forms, with or without
  18 # modification, are permitted provided that the following conditions are
  19 # met:
  20 #
  21 # 1.  Redistributions of source code must retain the above copyright
  22 # notice, this list of conditions and the following disclaimer.
  23 #
  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.
  27 #
  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.
  39 #
  40 # ------------------------------------------------------------------------------
  41
  42
  43 # System variables
  44 # ----------------
  45 server="@hostname@"
  46 admin="@job_email@"
  47 bcbin="/opt/bacula/bin/bconsole"
  48 sendmail="/usr/sbin/sendmail"
  49 bcconfig="/opt/bacula/etc/bconsole.conf"
  50
  51 # Database variables
  52 # ------------------
  53 dbtype="pgsql"                          # Supported options are pgsql, mysql, mariadb
  54 db="bacula"
  55 dbuser="bacula"
  56 dbbin="/usr/bin/psql"
  57 # dbpass="-pPassword"   # Uncomment and set db password if one is used
  58
  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")
  64
  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
  72
  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
  77
  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
  89
  90
  91 # --------------------------------------------------
  92 # Nothing should need to be modified below this line
  93 # --------------------------------------------------
  94
  95
  96 hist=${1}
  97 if [ -z ${hist} ]; then
  98         echo -e "\nUSE:\n$0 <history in hours>\n"
  99         exit 1
 100 fi
 101
 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"
 105         exit 1
 106 fi
 107
 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"
 111         exit 1
 112 fi
 113
 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"
 117         exit 1
 118 fi
 119
 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"
 123         exit 1
 124 fi
 125
 126
 127 # Build query based on dbtype. Good thing we have "standards" Sigh...
 128 # -------------------------------------------------------------------
 129 case ${dbtype} in
 130         mysql )
 131                 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 132                 TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
 133                 FROM Job \
 134                 WHERE (RealEndTime >= DATE_ADD(NOW(), INTERVAL -${hist} HOUR) OR JobStatus='R') \
 135                 ORDER BY ${sortfield} ${sortorder};" \
 136                 | ${dbbin} -u ${dbuser} ${dbpass} ${db} \
 137                 | sed '/^JobId/d' )
 138                 ;;
 139
 140         pgsql )
 141                 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 142                 AGE(EndTime, StartTime) as RunTime, JobErrors \
 143                 FROM Job \
 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' )
 148                 ;;
 149
 150         mariadb )
 151                 queryresult=$(echo "SELECT JobId, Name, StartTime, EndTime, Type, Level, JobStatus, JobFiles, JobBytes, \
 152                 TIMEDIFF (EndTime,StartTime) as RunTime, JobErrors \
 153                 FROM Job \
 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 )
 157                 ;;
 158
 159         * )
 160                 echo "dbtype of '${dbtype}' is invalid. Please set dbtype variable to 'mysql', 'pgsql', or 'mariadb'"
 161                 exit 1
 162                 ;;
 163 esac
 164
 165
 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
 172         results="0"
 173         else
 174                 results="1"
 175
 176
 177 # Now for some fun with awk
 178 # -------------------------
 179 IFS=" "
 180 msg=$(echo ${queryresult} | \
 181 LC_ALL=en_US.UTF-8 \
 182 awk \
 183 -v html="${html}" \
 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 }
 196 {star = " " }
 197
 198
 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                                                 # +-----------+---------------------------------+----------+
 228
 229
 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]+/)
 238                                                         {
 239                                                                 $13 = $10
 240                                                                 $11 = $9
 241                                                                 $10 = $8
 242                                                                 $9  = $7
 243                                                                 $8  = $6
 244                                                                 $7  = $5
 245                                                                 $5  = "--=Still Running=--"
 246                                                                 $6  = ""
 247                                                         }
 248                                                 }
 249
 250
 251                                                 # Assign words to job status code characters
 252                                                 # ------------------------------------------
 253                                                 # First, check to see if we need to generate an HTML email
 254                                                 { if (html == "yes")
 255                                                                 {
 256                                                                         # Set default opening and closing tags for status cell
 257                                                                         # ----------------------------------------------------
 258                                                                         tdo = "<td align=\"center\">"
 259                                                                         tdc = "</td>"
 260
 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)
 265                                                                                 {
 266                                                                                         if (colorstatusbg == "yes")
 267                                                                                                 # Assign jobs that are OK or Running the goodjobcolor
 268                                                                                                 # ---------------------------------------------------
 269                                                                                                 {
 270                                                                                                         tdo = "<td align=\"center\" bgcolor=\"" goodjobcolor "\">"
 271                                                                                                 }
 272
 273                                                                                 # Should the status be bolded?
 274                                                                                 # ----------------------------
 275                                                                                 if (boldstatus == "yes")
 276                                                                                         {
 277                                                                                                 tdo=tdo"<b>"
 278                                                                                                 tdc="</b>"tdc
 279                                                                                         }
 280                                                                                 status["T"]=tdo"-OK-"tdc
 281
 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)
 286                                                                                         {
 287                                                                                                 if (colorstatusbg == "yes")
 288                                                                                                         # Assign OK jobs with errors the warnjobcolor
 289                                                                                                         # -------------------------------------------
 290                                                                                                         {
 291                                                                                                                 tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
 292                                                                                                         }
 293
 294                                                                                                 # Should the status be bolded?
 295                                                                                                 # ----------------------------
 296                                                                                                 if (boldstatus == "yes")
 297                                                                                                         {
 298                                                                                                                 tdo=tdo"<b>"
 299                                                                                                                 tdc="</b>"tdc
 300                                                                                                         }
 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                                                                                                 # -------------------------------------------------------------
 304                                                                                                 $9 = "W"
 305                                                                                                 status["W"]=tdo"OK/Warnings"tdc
 306
 307                                                                                 # If the job is still running we will
 308                                                                                 # assign it the runningjobcolor
 309                                                                                 # -----------------------------------
 310                                                                                 } else if ($9 == "R")
 311                                                                                         {
 312                                                                                                 if (colorstatusbg == "yes")
 313                                                                                                         # Assign running jobs the runningjobcolor
 314                                                                                                         # ---------------------------------------
 315                                                                                                         {
 316                                                                                                                 tdo = "<td align=\"center\" bgcolor=\"" runningjobcolor "\">"
 317                                                                                                         }
 318
 319                                                                                                 # Should the status be bolded?
 320                                                                                                 # ----------------------------
 321                                                                                                 if (boldstatus == "yes")
 322                                                                                                         {
 323                                                                                                                 tdo=tdo"<b>"
 324                                                                                                                 tdc="</b>"tdc
 325                                                                                                         }
 326                                                                                                 status["R"]=tdo"Running"tdc
 327
 328                                                                                 # If it is a bad job, then
 329                                                                                 # we assign the badjobcolor
 330                                                                                 # -------------------------
 331                                                                                 } else if ($9 ~ /[ABDef]/)
 332                                                                                         {
 333                                                                                                 if (colorstatusbg == "yes")
 334                                                                                                         # Assign bad jobs the badjobcolor
 335                                                                                                         # -------------------------------
 336                                                                                                         {
 337                                                                                                                 tdo = "<td align=\"center\" bgcolor=\"" badjobcolor "\">"
 338                                                                                                         }
 339
 340                                                                                                 # Should the status be bolded?
 341                                                                                                 # ----------------------------
 342                                                                                                 if (boldstatus == "yes")
 343                                                                                                         {
 344                                                                                                                 tdo=tdo"<b>"
 345                                                                                                                 tdc="</b>"tdc
 346                                                                                                         }
 347                                                                                                 status["A"]=tdo"Aborted"tdc
 348                                                                                                 status["D"]=tdo"Verify Diffs"tdc
 349                                                                                                 status["f"]=tdo"Failed"tdc
 350
 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]/)
 356                                                                                         {
 357                                                                                                 if (colorstatusbg == "yes")
 358                                                                                                         # Assign job the warnjobcolor
 359                                                                                                         # ---------------------------
 360                                                                                                         {
 361                                                                                                                 tdo = "<td align=\"center\" bgcolor=\"" warnjobcolor "\">"
 362                                                                                                         }
 363
 364                                                                                                 # Should the status be bolded?
 365                                                                                                 # ----------------------------
 366                                                                                                 if (boldstatus == "yes")
 367                                                                                                         {
 368                                                                                                                 tdo=tdo"<b>"
 369                                                                                                                 tdc="</b>"tdc
 370                                                                                                         }
 371                                                                                                 status["E"]=tdo"OK, w/Errors"tdc
 372                                                                                                 status["I"]=tdo"Incomplete"tdc
 373                                                                                         }
 374                                                                 } else
 375                                                                 # $html is not "yes" so statuses will be normal text
 376                                                                 # --------------------------------------------------
 377                                                                         {
 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   "
 384                                                                                 status["T"]="      -OK-     "
 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)
 389                                                                                         { $9 = "W"
 390                                                                                                 status["W"]="   OK/Warnings "
 391                                                                                         }
 392                                                                         }
 393                                                 }
 394
 395
 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                                                 # ------------------------------------------
 403                                                 {
 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"
 419                                                 }
 420
 421
 422                                                 # Assign words to job type code characters
 423                                                 # ----------------------------------------
 424                                                 {
 425                                                         jobtype["D"]="Admin"
 426                                                         jobtype["B"]="Backup"
 427                                                         jobtype["C"]="Copy"
 428                                                         jobtype["c"]="Control"
 429                                                         jobtype["R"]="Restore"
 430                                                         jobtype["V"]="Verify"
 431                                                 }
 432
 433
 434                                                 # Assign words to job level code characters
 435                                                 # -----------------------------------------
 436                                                 {
 437                                                         level["F"]="Full"
 438                                                         level["I"]="Incr"
 439                                                         level["D"]="Diff"
 440                                                         level["f"]="VFul"
 441                                                         level["-"]="----"
 442                                                 }
 443
 444
 445                                                 # Assign words to Verify job level code characters
 446                                                 # ------------------------------------------------
 447                                                 {
 448                                                         level["A"]="VVol"
 449                                                         level["C"]="VCat"
 450                                                         level["V"]="Init"
 451                                                         level["O"]="VV2C"
 452                                                         level["d"]="VD2C"
 453                                                 }
 454
 455
 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
 458                                                 # of problem jobs.
 459
 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  = "*" } } }
 467
 468
 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 = "-" } }
 474
 475
 476                                                 # Print out each job, formatted with the following fields:
 477                                                 # JobId Name Status Errors Type Level Files Bytes StartTime EndTime RunTime
 478                                                 # -------------------------------------------------------------------------
 479                                                 { if (html == "yes")
 480                                                         { printf("<tr bgcolor=\"%s\"> \
 481                                                                 <td align=\"center\">%s%s%s</td> \
 482                                                                 <td>%s</td> \
 483                                                                 %s \
 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> \
 492                                                                 </tr>\n", \
 493                                                                 jobtablejobcolor, star, $1, star, $2, status[$9], $13, jobtype[$7], level[$8], $10, $11/(1024*1024*1024), $3, $4, $5, $6, $12);
 494                                                         } else
 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);
 497                                                                 }
 498                                                 }
 499
 500
 501                                                 # Count the number of jobs
 502                                                 # ------------------------
 503                                                 { totaljobs++ }
 504
 505
 506                                                 # Count the number of files and bytes from all jobs
 507                                                 # -------------------------------------------------
 508                                                 { files += $10 }
 509                                                 { bytes += $11 }
 510
 511
 512 # Finally, print out the summaries
 513 # --------------------------------
 514 END {
 515 if (printsummary == "yes")
 516         { if (html == "yes")
 517                 {
 518                         printf("</table>")
 519                         printf("<br>\
 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>\
 525                         </table>\
 526                         <hr align=\"left\" width=\"25%\">",\
 527                         totaljobs, files, bytes/(1024*1024*1024));
 528                 } else
 529 printf("\
 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));
 536 } exit awkerr }
 537 ')
 538
 539
 540 # Any failed jobs, or jobs with errors?
 541 # -------------------------------------
 542 numbadjobs=$?
 543
 544
 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) }')
 554
 555
 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                 # -------------------
 562                 msg="${msg}"$(
 563                 if [ ${html} == "yes" ]; then
 564                         echo "<pre>====================================="
 565                                 else
 566                                         echo -e "\n\n\n====================================="
 567                 fi
 568                         echo "Job Summaries of All Terminated Jobs:"
 569                         echo "====================================="
 570                 )
 571
 572
 573                 # Get the job logs from all jobs and just grep for the summary
 574                 # ------------------------------------------------------------
 575                 for jobid in ${alljobids}; do
 576                         msg="${msg}"$(
 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 "======================================================================"
 582                         )
 583                 done
 584                 if [ ${html} == "yes" ]; then
 585                         msg=${msg}$(echo "</pre>")
 586                 fi
 587         fi
 588 fi
 589
 590
 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) }')
 599
 600
 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                 # -------------------
 607                 msg="${msg}"$(
 608                 if [ ${html} == "yes" ]; then
 609                         echo "<pre>=========================================================="
 610                                 else
 611                                         echo -e "\n\n\n=========================================================="
 612                 fi
 613                         echo "Job logs of failed jobs, or good jobs with JobErrors != 0:"
 614                         echo "=========================================================="
 615                 )
 616
 617
 618                 # Get the bad job's log from the Director via bconsole
 619                 # ----------------------------------------------------
 620                 for jobid in ${badjobids}; do
 621                         msg="${msg}"$(
 622                                 echo -e "\n--------------"
 623                                 echo "JobId: ${jobid}"
 624                                 echo "--------------"
 625                                 echo "llist joblog jobid=${jobid}" | ${bcbin} -c ${bcconfig}
 626                                 echo "======================================================================"
 627                         )
 628                 done
 629                 if [ ${html} == "yes" ]; then
 630                         msg=${msg}$(echo "</pre>")
 631                 fi
 632         fi
 633 fi
 634
 635
 636 # Prepend the header to the $msg output
 637 # -------------------------------------
 638 if [ ${html} == "yes" ]; then
 639         msg="<html>
 640         <head><meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\">
 641                 <style>
 642                         body {font-family:$fontfamily; font-size:$fontsize;} td {font-size:$fontsizejobinfo;} pre {font-size:$fontsizesumlog;}
 643                 </style>
 644         </head>
 645         <body>
 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>
 660                 </tr>
 661 ${msg}
 662 </body></html>"
 663         else
 664                 msg="
 665   ${emailtitle}
 666   ------------------------------------------
 667
 668   JobId      Job Name          Status         Errors     Type    Level   Files       Bytes         Start Time            End Time        Run Time
 669   -----   --------------   ---------------  ----------  -------  -----  --------  -----------  -------------------  -------------------  --------
 670 ${msg}"
 671 fi
 672
 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..."
 682         else
 683                 # Totally unnecessary, but, well...  OCD... :)
 684                 # --------------------------------------------
 685                 if [ ${numbadjobs} -ne 0 ]; then
 686                         if [ ${numbadjobs} -eq 1 ]; then
 687                                 job="Job"
 688                                         else
 689                                                 job="Jobs"
 690                         fi
 691                         status="(${numbadjobs}) ${job} with Errors"
 692                         subjecticon="${badjobsicon}"
 693                                 else
 694                                         status="All Jobs OK"
 695                                         subjecticon="${goodjobsicon}"
 696                 fi
 697 fi
 698
 699
 700 # More silliness
 701 # --------------
 702 if [ ${hist} -eq 1 ]; then
 703         hour="Hour"
 704         else
 705                 hour="Hours"
 706 fi
 707
 708
 709 # Email the report
 710 # ----------------
 711 (
 712 echo "To: ${admin}"
 713 echo "From: ${admin}"
 714 if [ ${addsubjecticon} == "yes" ]; then
 715         echo "Subject: ${subjecticon} ${server} - ${status} in the Past ${hist} ${hour}"
 716                 else
 717                         echo "Subject: ${server} - ${status} in the Past ${hist} ${hour}"
 718 fi
 719 if [ ${html} == "yes" ]  &&  [ ${results} -ne 0 ]; then
 720  echo "Content-Type: text/html"
 721  echo "MIME-Version: 1.0"
 722 fi
 723 echo ""
 724 echo "${msg}"
 725 ) | /usr/sbin/sendmail -t
 726 # -------------
 727 # End of script
 728 # -------------
 729
 730
 731 # ----------
 732 # Change Log
 733 # ----------
 734 # ----------------------------
 735 # William A. Arlofski
 736 # Reverse Polarity, LLC
 737 # helpdesk@revpol.com
 738 # http://www.revpol.com/bacula
 739 # ----------------------------
 740 #
 741 #
 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
 746 #
 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",
 750 #            "JobFiles"
 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
 754 #            formatting
 755 #
 756 # 20140107 - Modified script to include more information and cleaned
 757 #            up the output formatting
 758 #
 759 # 20150704 - Added ability to work with MySQL or Postgresql
 760 #
 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"
 764 #            column.
 765 #
 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"
 770 #
 771 # 20170303 - Fixed output in cases where there are jobs running and there
 772 #            is no "Stop Time" for a job.
 773 #
 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
 778 #               jobs
 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,
 785 #               and Bytes
 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.
 790 #
 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.
 797 #
 798 # 20170417 - Added some tests for binaries and the bconsole config file
 799 #
 800 # 20170429 - Thanks to Chris Couture for contacting me and submitting a
 801 #            working query for MariaDB. I have added 'mariadb' as a new
 802 #            dbtype option.
 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>
 807 #                in HTML emails.
 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
 835 #
 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
 839 #
 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
 846 #            db in the future.
 847 #          - Added an "Errors" column to the table to show "JobErrors" from the
 848 #            db.
 849 #          - Some minor variable name changes and other minor changes
 850 #
 851 # 20170511 - Minor adjustments to the alignment formatting of the text email
 852 #          - Minor 'case' changes to a couple levels (Init & VCat)
 853 #
 854 # ------------------------------------------------------------------------------
 855
 856
 857 # I like small tabs. Use  :set list in vim to see tabbing etc
 858 # vim: set tabstop=2:softtabstop=2:shiftwidth=2 #