"Brian A. Seklecki" <bseklecki AT collaborativefusion DOT com> writes:

> B-W feature question:
>
> Could we write a query that examines overall pool volume write
> capacity?

no, since the configuration files need to be available.

> It would be nice to track historical bytes written by a select set
> of jobs (normally started by a single schedule entry).
>
> I don't think that a data structure exists in the database that
> links jobs to a schedule, though ....

right.

I've written a Perl script which groups jobs by schedule, and emits
SQL queries to fetch the total size of each schedule.  it's just a
hack (the parser is not very general), but I use it to choose a
lightly loaded schedule when adding a new client.

it is very simplistic -- ideally it would find the schedule which
avoids huge loads on a single day.  in my case it's close enough,
since each schedule is "defined" by when its single full dump is, so
two schedules will never run fulls on the same day.

#! /usr/bin/perl -w

use strict;
use Getopt::Std;

sub usage {
    print STDERR "Usage: $0 [-s] [-t time|size|files] FILE ...\n";
    print STDERR "\t-s\tMake output suitable for pasting into manual queries\n";
    print STDERR "\t-t CRITERIA\tMake SQL queries for SUM of CRITERIA\n";
    exit(64);
}

sub HELP_MESSAGE { usage(); }

my ($in_job, $job_name, $schedule);
my %sched;

our ($opt_s, $opt_t);
getopts("st:") || usage();
my %query = (size => "JobBytes",
             time => "EndTime - StartTime",
             files => "JobFiles",
    );
if ($opt_t) {
    $opt_t = $query{$opt_t};
    usage() unless $opt_t;
}

while (<>) {
      if (/Job \{/) {
          $in_job = 1;
      } elsif ($in_job && /^\s*Name\s*=\s*\"(.*?)\"/i) {
          $job_name = $1;
      } elsif ($in_job && /^\s*Schedule\s*=\s*\"?(.*?)\"?\s*$/i) {
          $schedule = $1;
      } elsif ($in_job && /\}/) {
          $in_job = undef;
          push(@{$sched{$schedule}}, $job_name) if $schedule && $job_name;
      }
}

for my $s (keys %sched) {
    my ($sec,$min,$hour,$mday,$mon,$year) = (localtime(time() - 7*86400))[0..5];
    my $a_week_ago = sprintf("%04d-%02d-%02d %02d:%02d:%02d",
                             $year+1900, $mon+1, $mday, $hour, $min, $sec);
    if ($opt_t) {
        print("SELECT SUM($opt_t), '$s' FROM Job ",
              "WHERE EndTime > '$a_week_ago' AND Name IN (",
              join(", ", map { "'$_'" } @{$sched{$s}}),
              ");\n");
    } elsif ($opt_s) {
        print "$s\t", join(", ", map { "'$_'" } @{$sched{$s}}), "\n";
    } else {
        print "$s\t", join(" ", @{$sched{$s}}), "\n";
    }
}

actual usage is something like:

  bacula-job-sched -t size *.conf > /tmp/query
  mysql bacula < /tmp/query | grep -v SUM | sort -rn

> IMPORTANT: This message contains confidential information and is
> intended only for the individual named. If the reader of this
> message is not an intended recipient (or the individual responsible
> for the delivery of this message to an intended recipient), please
> be advised that any re-use, dissemination, distribution or copying
> of this message is prohibited. Please notify the sender immediately
> by e-mail if you have received this e-mail by mistake and delete
> this e-mail from your system.

oops?  perhaps I should delete this thread from my server ;-)

-- 
regards,          | Redpill  _
Kjetil T. Homme   | Linpro  (_)

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users