3 # bacula report generation
5 # (C) Arno Lehmann 2005
10 # Usage: See funtion print_usage
11 # or use this script with option --help
15 # 0.2 publicly available, works reliable
16 # 0.3 increasing weight of No. of tapes in guess reliability
17 # and including tape capacity guessing when no volumes in subpool
18 # using default values from temp. table
26 $0 =~ /.*\/([^\/]*)$/;
31 my $db_user = "bacula";
32 my $db_database = "mysql:bacula";
42 my $out_pooldetails = "";
44 my $out_bargraphlen = 70;
45 my $out_subpools = "";
46 my $out_subpooldetails = "";
47 my $out_subbargraph = "";
48 my $out_cutmarks = "";
50 # This is the data we're interested in:
51 # In this array we have a hash reference to each Pool.
52 # A pool consists of a hash having
57 # VolumesFull (This is State Full
58 # VolumesEmpty (This is Purged and Recycle)
59 # VolumesPartly (Append)
60 # VolumesAway (Archive, Read-Only)
61 # VolumesOther (Busy, Used)
62 # VolumesOff (Disabled, Error)
65 # GuessReliability (This is the weighted average of the Reliability
66 # of all the Media Type Guesses in this Pool)
67 # MediaTypes is an array of references to hashes for collected
68 # information for all the Media Types in this pool.
69 # This has the same as the pools summary and adds
70 # MediaType The String
71 # AvgFullBytes (The Avg. Number of Bytes per full Volume)
72 # BytesFreeEmpty (The estimated Free Bytes on Empty Volumes)
75 # We use: $the_pools[0]->MediaTypes[0]->{MediaType} or
77 # I hope you get the point. I hope I do.
79 Getopt::Long::Configure("bundling");
80 GetOptions("host=s"=>\$db_host,
81 "user|U=s"=>\$db_user,
82 "database|D=s"=>\$db_database,
83 "password|P=s"=>\$db_pass,
86 "version|V"=>\$do_version,
87 "subpools|s"=>\$out_subpools,
88 "subpool-details"=>\$out_subpooldetails,
89 "pool-details|d"=>\$out_pooldetails,
90 "pool-bargraph!"=>\$out_bargraph,
91 "bar-length|l=i"=>\$out_bargraphlen,
92 "cutmarks|c"=>\$out_cutmarks,
93 "subpool-bargraph"=>\$out_subbargraph
96 debug_out(100, "I've got
99 database: $db_database
105 pool details: $out_pooldetails
106 subpools: $out_subpools
107 subpool details: $out_subpooldetails
108 bargraph: $out_bargraph
109 subpool bargraph: $out_subbargraph
110 bar length: $out_bargraphlen
111 cutmarks: $out_cutmarks
112 I was called as $0 and am version $version.
124 $out_subpools = 1 if ($out_subpooldetails);
125 $out_subpools = 1 if ($out_subbargraph);
126 $out_bargraphlen = 70 if (15 > $out_bargraphlen);
127 $out_bargraphlen = 70 if (200 < $out_bargraphlen);
128 $out_bargraph = 1 if (! $out_pooldetails);
130 debug_out(100, "Output options after dependencies:
131 pool details: $out_pooldetails
132 subpools: $out_subpools
133 subpool details: $out_subpooldetails
134 bargraph: $out_bargraph
135 subpool bargraph: $out_subbargraph
136 bar length: $out_bargraphlen
137 cutmarks: $out_cutmarks
140 my (undef, $min, $hour, $mday, $mon, $year) = localtime();
142 $mon = sprintf("%02i", $mon+1);
143 $mday = sprintf("%02i", $mday);
144 $min = sprintf("%02i", $min);
145 $hour = sprintf("%02i", $hour);
146 print "bacula volume / pool status report $year-$mon-$mday $hour:$min\n",
147 "Volumes Are Full, Other, Append, Empty, aWay or X (error)\n";
148 my $dbconn = "dbi:" . $db_database;
149 $dbconn .= "\@" . $db_host if $db_host;
150 debug_out(40, "DBI connect with $dbconn");
152 my $h_db = DBI->connect($dbconn,
156 ) || die DBI::errstr;
157 debug_out(10, "Have database connection $h_db");
159 debug_out(100, "creating temp tables...");
161 $h_db->do("CREATE TABLE alrep_M(PoolId INT(10) UNSIGNED,MediaType TINYBLOB)") || debug_abort(0, "Can't create temp table alrep_M - another script running?");
162 unshift @temp_tables, "alrep_M";
163 debug_out(45, "Table alrep_M created.");
166 debug_out(40, "All tables done.");
168 debug_out(40, "Filling temp tables...");
169 if ($h_db->do("INSERT INTO alrep_M SELECT Pool.PoolId,Media.MediaType FROM Pool,Media WHERE Pool.PoolId=Media.PoolId GROUP BY PoolId,MediaType")) {
170 debug_out(45, "PoolId-MediaType table populated.");
172 debug_abort(0, "Couldn't populate PoolId and MediaType table alrep_M.");
175 debug_out(40, "All tables done.");
177 debug_out(40, "Getting Pool Names.");
178 my $h_st = $h_db->prepare("SELECT Name,PoolId FROM Pool ORDER BY Name") ||
179 debug_abort(0, "Couldn't get Pool Information.", $h_db->errstr());
180 $h_st->execute() || debug_abort(0, "Couldn't query Pool information.",
183 while ($pools=$h_st->fetchrow_hashref()) {
184 process_pool($pools->{Name}, $pools->{PoolId})
186 debug_out(10, "All Pool data collected.");
187 debug_out(7, "Pools analyzed: $#the_pools.");
188 debug_out(10, "Going to print...");
191 for $pi (@the_pools) {
195 debug_out(10, "Program terminates normally.");
197 debug_out(10, "Finishing.");
204 baculareport.pl - a script to produce some bacula reports out of
205 the catalog database.
209 B<baculareport.pl> B<--help>|B<-h>
211 B<baculareport.pl> B<--version>|B<-V>
213 B<baculareport.pl> [B<--host> I<Hostname>] [B<--user>|B<-U> I<Username>]
214 [B<--database>|B<-D> I<Database>] [B<--password>|B<-P> I<Password>]
215 [B<--debug> I<Level>] [B<--pool-details>|B<-d>]
216 [B<--pool-bargraph>|B<--nopool-bargraph>] [B<--subpools>|B<-s>]
217 [B<--subpool-details>] [B<--subpool-bargraph>] [B<--bar-length>|B<-l>
218 I<Length>] [B<--cutmarks>|B<-c>]
220 The long options can be abbreviated, as long as they remain unique.
221 Short options (and values) can be grouped, for more information see
222 B<perldoc Getopt::Long>.
226 B<baculareport.pl> accesses the catalog used by the backup program bacula
227 to produce some report about pool and volume usage.
229 The command line options B<--host> I<host>, B<--user> or B<-U>
230 I<user>, B<--database> or B<-D> and B<--password> or B<-P> define the
231 database to query. See below for security considerations concerning
234 The I<database> must be given in perl's B<DBI>-syntax, as in
235 I<mysql:bacula>. Currently, only MySQL is supported, though PostgreSQL
236 should work with only minor modifications to B<baculareport.pl>.
238 Output of reports is controlled using the command-line switches
239 B<--*pool*>, B<--bar-length> and B<--cutmarks> or there one-letter
242 The report for a pool can contain a one-line overview of the volumes
243 in that pool, giving the numbers of volumes in different states, the
244 total bytes stored and an estimate of the available capacity.
246 The estimated consists of a percentage describing the reliability of
247 this estimate and the guessed free capacity.
249 A visual representation of the pools state represented as a bar graph,
250 together with the number of full, appendable and free volumes is the
253 The length of this graph can be set with B<--bar-length> or B<-l>
254 I<length in characters>.
256 As a pool can contain volumes of different media type, the report's
257 output can include the information about those collections of volumes
258 called subpools in B<baculareport.pl>s documentation.
260 The subpool overview data presents the same information about the
261 volumes the pool details have, but includes the media type and excludes
262 the free capacity guess.
264 Subpool details report the average amount of data on full volumes,
265 together with what is estimated to be available on appendable and empty
266 volumes. A measurement on the reliability of this estimate is given as a
267 percent value. See below in L<"CAPACITY GUESSING"> for more
270 Finally, a bar graph representing this subpools fill-level can be printed.
271 For easier overview it is scaled like the pools bargraph.
273 B<--cutmarks> or B<-c> prints some marks above each pool report to
274 make cutting the report easier if you want to file it.
276 Sample reports are in L<"SAMPLE REPORTS">.
278 The B<--debug>-option activates debug output. Without understanding the
279 source code this will not be helpful. See below L<"DEBUG OUTPUT">.
281 =head1 DATABASE ACCESS AND SECURITY
283 baculareport.pl needs access to baculas catalog. This might introduce
284 a security risk if the database access password is published to people who
285 shouldn't know it, but need to create reports.
287 The solution is to set up a database account which can only read from
288 baculas catalog. Use your favorite database administration tool for
291 Command line passing of the password is also not really secure - anybody
292 with sufficient access rights can read the command line etc. So, if you use this script on a multi-user machine, you are well advised to
298 I<use a special, limited database account for catalog access>, or
302 I<modify the hard-coded default database password to the one
307 This should limit security risks to a minimum.
309 If B<baculareport.pl> is used by your backup admin only, don't bother
310 - she has access to all your data anyway. (B<No. Please not. This was
313 =head1 SAMPLE REPORTS
315 The reports can be customized using the above explained command line switches.
318 bacula volume / pool status report 2005-01-18 23:40
319 Volumes Are Full, Other, Append, Empty, aWay or X (error)
322 ######################################################----------------
323 |0% |20% |40% |60% |80% 100%|
324 48.38GB used Rel: 24% free 13.88GB
325 17 F Volumes 3 A and 4 E Volumes
328 #######################################-------------------------------
329 |0% |20% |40% |60% |80% 100%|
330 310.66GB used Rel: 58% free 241.64GB
331 43 F Volumes 2 A and 14 E Volumes
334 #######################################################---------------
335 |0% |20% |40% |60% |80% 100%|
336 28.51GB used Rel: 0% (def.) free 7.61GB
337 0 F Volumes 3 A and 4 E Volumes
342 This is the sort of report you get when you use this script without
343 any special output options. After a short header, for all pools in
344 the catalog a graphic representation of its usage is
345 printed. Below that, you find some essential information: The
346 capacity used, a guess of the remaining capacity (see
347 L<"CAPACITY GUESSING"> below), and
348 an overview of the volumes: Here, in pool Incr we have no full
349 volumes, 3 appendable ones and 4 empty volumes.
351 In this example, the pool TMPDisk does not contain anything which can
354 Following you have an example with all output options set.
358 ###################################################----
359 |0% |25% |50% |75% 100%|
360 10 Volumes (2 F, 0 O, 2 A, 6 E, 0 W, 0 X) Total 59.64GB Rel: 29% avail.: 4.57GB
361 Details by Mediatype:
362 DDS1 (0 F, 0 O, 1 A, 4 E, 0 W, 0 X) Total 4.53GB
364 |0% |25% |50% |75% 100%|
365 Avg, avail. Partly, Empty, Total, Rel.: N/A N/A N/A N/A 0%
366 DDS2 (0 F, 0 O, 0 A, 2 E, 0 W, 0 X) Total 0.00B
367 Avg, avail. Partly, Empty, Total, Rel.: N/A N/A N/A N/A 0%
368 DLTIV (2 F, 0 O, 1 A, 0 E, 0 W, 0 X) Total 55.11GB
369 #############################################----
370 |0% |25% |50% |75% 100%|
371 Avg, avail. Partly, Empty, Total, Rel.: 19.89GB 4.57GB N/A 4.57GB 96%
375 1 Volumes (0 F, 0 O, 0 A, 1 E, 0 W, 0 X) Total 0.00B Rel: 0% avail.: 0.00B
376 Details by Mediatype:
377 File (0 F, 0 O, 0 A, 1 E, 0 W, 0 X) Total 0.00B
379 Avg, avail. Partly, Empty, Total, Rel.: N/A N/A N/A N/A 0%
381 Cut marks are included for easier cutting in case you want to file the
382 printed report. Then, the length of the bar graphs was changed.
384 More detail for the pools is shown: Not only the overwiev graphics,
385 but also a listing of the status of all media in this
386 pool, followed by the reliability of the guess of available
387 capacity and the probable available capacity itself.
389 After this summary you find a similar report for all media types in
390 this pool. Here, the media type starts the details line. The next
391 line is a breakdown of the capacity inside this subpool: The
392 average capacity of the full volumes, followed by the probable
393 available capacity on appendable and empty volumes. Total is the
394 probable free capacity on these volumes, and Rel is the
395 reliability of the capacity guessing.
397 Note that some of the items are not always displayed: A pool or
398 subpool with no bytes in it will not have a bar graph, and some of
399 the statistical data is marked as N/A for not available.
401 The above output was generated with the following command:
404 baculareport.pl --password <yestherewasapassword>\
405 --pool-bargraph --pool-details --subpools\
406 --subpool-details --subpool-bargraph --bar-length 55\
409 The following command would have given the same output:
412 baculareport.pl -P <therightpassphrase> -csdl55\
413 --subpool-d --subpool-b >> >>
415 =head1 CAPACITY GUESSING
417 For empty and appendable volumes, the average capacity of the full
418 volumes is used as the base for estimating what can be
419 stored. This usually depends heavily on the type of data to store,
420 and of course this works only with volumes of the same nominal
423 The reliability of all this guesswork is expressed based on the
424 standard deviation among the full volumes, scaled to percent. 100%
425 is a very reliable estimate (Note: NOT absolutely reliable!) while
426 a small percentage (from personal experience: below 60-70 percent)
427 means that you shouldn't rely on the reported available data storage.
429 To determine the overall reliability in a pool, the reliabilites of
430 the subpools are weighted - a subpool with many volumes has a higer
431 influence on overall reliability.
433 Keep in mind that the reported free capacities and reliabilities can
434 only be a help and don't rely on these figures alone. Keep enough
435 spare tapes available!
437 Default capacities for some media types are included now. Consider this
438 feature a temporarily kludge - At the moment, there is a very simple
439 media capacity guessing implemented. Search for the function
440 `get_default_bytes' and modify it to your needs.
442 In the future, I expect some nominal volume capacity knowledge inside
443 baculas catalog, and when this is available, that data will be used.
445 Capacity estimates with defaults in the calculation are marked with
446 B<(def.)> after the reliability percentage. If you see B<0% (def.)>
447 only the defaults are used because no full tapes were available.
451 Debugging, or more generally verbose output, is activated by the
452 --debug <level> command switch.
454 The higher the level, the more output you get.
456 Currently, levels 10 and up are real debugging output. Levels above
457 100 are not used. I<With debug level 100 (and above) the database
458 password is printed!>
460 The debug levels used are:
466 Some warnings are printed.
470 Program Flow is reported.
474 More detailed Program flow, for example loops.
478 Database actions are printed.
482 Table actions are reported.
486 Even more database activity.
490 All internal state data is printed. Beware: This includes the database
497 Probably many. If you find one, notify the author. Better: notify me
500 Currently this script works only with MySQL and catalog version 8
501 (probably older versions as well, but that is untested).
505 Arno Lehmann al@its-lehmann.de
509 This is copyrighted work: (C) 2005 Arno Lehmann IT-Service Lehmann
511 Use, modification and (re-)distribution are allowed provided this
512 license and the names of all contributing authors are included.
514 No author or contributor gives any warranty on this script. If you
515 want to use it, you are all on your own. Please read the documentation,
516 and, if you feel unsure, read and understand the sourcecode.
518 The terms and idea of the GNU GPL, version 2 or, at you option, any
519 later version, apply. See http://www.fsf.org.
521 You can contact the author using the above email address. I will try to
522 answer any question concerning this script, but still - no promises!
524 Bacula is (C) copyright 2000-2005 Kern Sibbald. See http://www.bacula.org.
526 (Bacula consulting available.)
531 my %pool = (BytesTotal=>0,
539 VolumesCleaning=>"Not counted",
542 AvgFullUsesDefaults=>""
544 debug_out(10, "Working on Pool $pools->{Name}.");
549 debug_out(30, "Pool $pool{Name} is Id $pool{Id}.");
550 my $h_st = $h_db->prepare("SELECT MediaType FROM alrep_M WHERE
551 PoolId = $pool{Id} ORDER BY MediaType") ||
553 "Can't query Media table.", $h_st->errstr());
556 "Can't get Media Information", $h_st->errstr());
557 while (my $mt=$h_st->fetchrow_hashref()) {
558 # In this loop, we process one media type in a pool
559 my %subpool = (MediaType=>$mt->{MediaType});
560 debug_out(45, "Working on MediaType $mt->{MediaType}.");
562 $h_db->prepare("SELECT COUNT(*) AS Nr,SUM(VolBytes) AS Bytes," .
563 "STD(VolBytes) AS Std,AVG(VolBytes) AS Avg " .
564 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
565 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
566 ") AND (VolStatus=\'Full\')")
568 "Can't query Media Summary Information by MediaType.",
570 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
571 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Full");
572 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
573 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
575 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
576 # Don't know why, but otherwise the handle access
577 # methods result in a warning...
579 if (1 == $h_qu->rows()) {
580 if (my $qr = $h_qu->fetchrow_hashref) {
581 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
582 $subpool{VolumesFull} = $qr->{Nr};
583 $subpool{VolumesTotal} += $qr->{Nr};
584 $subpool{BytesTotal} = $qr->{Bytes} if (defined($qr->{Bytes}));
585 if (defined($qr->{Bytes}) && (0 < $qr->{Bytes}) &&
587 $subpool{AvgFullBytes} = int($qr->{Bytes} / $qr->{Nr});
589 $subpool{AvgFullBytes} = get_default_bytes($mt->{MediaType});
590 $subpool{AvgFullUsesDefaults} = 1;
592 if (defined($qr->{Std}) &&
593 defined($qr->{Avg}) &&
595 # $subpool{GuessReliability} = 100-(100*$qr->{Std}/$qr->{Avg});
596 $subpool{GuessReliability} =
597 100 - # 100 Percent minus...
598 ( 100 * # Percentage of
599 ( $qr->{Std}/$qr->{Avg} ) * # V
600 ( 1 - 1 / $qr->{Nr} ) # ... the more tapes
601 # the better the guess
604 $subpool{GuessReliability} = 0;
607 debug_out(1, "Can't get Media Summary Information by MediaType.",
609 $subpool{VolumesFull} = 0;
610 $subpool{BytesTotal} = 0;
611 $subpool{GuessReliability} = 0;
612 $subpool{AvgFullBytes} = -1;
615 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
618 # Here, Full Media are done
619 debug_out(15, "Full Media done. Now Empty ones.");
621 $h_db->prepare("SELECT COUNT(*) AS Nr " .
622 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
623 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
624 ") AND ((VolStatus=\'Purged\') OR " .
625 "(VolStatus=\'Recycle\'))")
627 "Can't query Media Summary Information by MediaType.",
629 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
630 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Recycle OR Purged");
631 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
632 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
634 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
635 # Don't know why, but otherwise the handle access
636 # methods result in a warning...
638 if (1 == $h_qu->rows()) {
639 if (my $qr = $h_qu->fetchrow_hashref) {
640 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
641 $subpool{VolumesEmpty} = $qr->{Nr};
642 $subpool{VolumesTotal} += $qr->{Nr};
643 if (($subpool{AvgFullBytes} > 0) && ($qr->{Nr} > 0)) {
644 $subpool{BytesFreeEmpty} = $qr->{Nr} * $subpool{AvgFullBytes};
646 $subpool{BytesFreeEmpty} = -1;
649 debug_out(1, "Can't get Media Summary Information by MediaType.",
651 $subpool{VolumesEmpty} = 0;
652 $subpool{BytesFreeEmpty} = 0;
655 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
658 # Here, Empty Volumes are processed.
660 debug_out(15, "Empty Media done. Now Partly filled ones.");
662 $h_db->prepare("SELECT COUNT(*) AS Nr,SUM(VolBytes) AS Bytes " .
663 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
664 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
665 ") AND (VolStatus=\'Append\')")
667 "Can't query Media Summary Information by MediaType.",
669 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
670 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Append");
671 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
672 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
674 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
675 # Don't know why, but otherwise the handle access
676 # methods result in a warning...
678 if (1 == $h_qu->rows()) {
679 if (my $qr = $h_qu->fetchrow_hashref) {
680 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
681 $subpool{VolumesPartly} = $qr->{Nr};
682 $subpool{VolumesTotal} += $qr->{Nr};
683 $subpool{BytesTotal} += $qr->{Bytes};
684 if (($subpool{AvgFullBytes} > 0) && ($qr->{Nr} > 0)) {
685 $subpool{BytesFreePartly} = $qr->{Nr} * $subpool{AvgFullBytes} - $qr->{Bytes};
686 $subpool{BytesFreePartly} = $qr->{Nr} if $subpool{BytesFreePartly} < 1;
688 $subpool{BytesFreePartly} = -1;
691 debug_out(1, "Can't get Media Summary Information by MediaType.",
693 $subpool{VolumesPartly} = 0;
694 $subpool{BytesFreePartly} = 0;
697 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
700 # Here, Partly filled volumes are done
702 debug_out(15, "Partly Media done. Now Away ones.");
704 $h_db->prepare("SELECT COUNT(*) AS Nr,SUM(VolBytes) AS Bytes " .
705 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
706 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
707 ") AND ((VolStatus=\'Archive\') OR " .
708 "(VolStatus=\'Read-Only\'))")
710 "Can't query Media Summary Information by MediaType.",
712 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
713 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Recycle OR Purged");
714 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
715 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
717 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
718 # Don't know why, but otherwise the handle access
719 # methods result in a warning...
721 if (1 == $h_qu->rows()) {
722 if (my $qr = $h_qu->fetchrow_hashref) {
723 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
724 $subpool{VolumesAway} = $qr->{Nr};
725 $subpool{VolumesTotal} += $qr->{Nr};
726 $subpool{BytesTotal} += $qr->{Bytes};
728 debug_out(1, "Can't get Media Summary Information by MediaType.",
730 $subpool{VolumesAway} = 0;
733 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
736 # Here, Away Volumes are processed.
738 debug_out(15, "Away Media done. Now Other ones.");
740 $h_db->prepare("SELECT COUNT(*) AS Nr,SUM(VolBytes) AS Bytes " .
741 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
742 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
743 ") AND ((VolStatus=\'Busy\') OR " .
744 "(VolStatus=\'Used\'))")
746 "Can't query Media Summary Information by MediaType.",
748 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
749 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Recycle OR Purged");
750 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
751 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
753 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
754 # Don't know why, but otherwise the handle access
755 # methods result in a warning...
757 if (1 == $h_qu->rows()) {
758 if (my $qr = $h_qu->fetchrow_hashref) {
759 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
760 $subpool{VolumesOther} = $qr->{Nr};
761 $subpool{VolumesTotal} += $qr->{Nr};
762 $subpool{BytesTotal} += $qr->{Bytes};
764 debug_out(1, "Can't get Media Summary Information by MediaType.",
766 $subpool{VolumesOther} = 0;
769 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
772 # Here, Other Volumes are processed.
774 debug_out(15, "Other Media done. Now Off ones.");
776 $h_db->prepare("SELECT COUNT(*) AS Nr,SUM(VolBytes) AS Bytes " .
777 "FROM Media WHERE (PoolId=$pool{Id}) AND " .
778 "(MediaType=" . $h_db->quote($mt->{MediaType}) .
779 ") AND ((VolStatus=\'Disabled\') OR " .
780 "(VolStatus=\'Error\'))")
782 "Can't query Media Summary Information by MediaType.",
784 debug_out(48, "Query active: ", $h_qu->{Active}?"Yes":"No");
785 debug_out(45, "Now selecting Summary Information for $pool{Name}:$mt->{MediaType}:Recycle OR Purged");
786 debug_out(48, "Query: ", $h_qu->{Statement}, "Params: ",
787 $h_qu->{NUM_OF_PARAMS}, " Rows: ", $h_qu->rows);
789 debug_out(48, "Result:", $h_qu->rows(), "Rows.");
790 # Don't know why, but otherwise the handle access
791 # methods result in a warning...
793 if (1 == $h_qu->rows()) {
794 if (my $qr = $h_qu->fetchrow_hashref) {
795 debug_out(45, "Got $qr->{Nr} and $qr->{Bytes}.");
796 $subpool{VolumesOff} = $qr->{Nr};
797 $subpool{VolumesTotal} += $qr->{Nr};
799 debug_out(1, "Can't get Media Summary Information by MediaType.",
801 $subpool{VolumesOff} = 0;
804 debug_out(45, "Got nothing: ", (defined($h_qu->errstr()))?$h_qu->errstr():"No error.");
807 # Here, Off Volumes are processed.
809 if ((0 < $subpool{BytesFreeEmpty}) ||
810 (0 < $subpool{BytesFreePartly})) {
811 debug_out(15, "We have a guess.");
812 $subpool{BytesFree} = 0;
813 $subpool{BytesFree} += $subpool{BytesFreeEmpty} if
814 (0 < $subpool{BytesFreeEmpty});
815 $subpool{BytesFree} += $subpool{BytesFreePartly} if
816 (0 < $subpool{BytesFreePartly});
818 debug_out(15, "Neither Empty nor Partly BytesFree available - no guess!");
819 $subpool{BytesFree} = -1;
821 if ($subpool{AvgFullUsesDefaults}) {
822 debug_out(15, "Average Full Capacity calculation included defaults.");
823 $pool{AvgFullUsesDefaults} = 1;
825 $pool{BytesTotal} += $subpool{BytesTotal};
826 $pool{VolumesTotal} += $subpool{VolumesTotal};
827 $pool{VolumesFull} += $subpool{VolumesFull};
828 $pool{VolumesEmpty} += $subpool{VolumesEmpty};
829 $pool{VolumesPartly} += $subpool{VolumesPartly};
830 $pool{VolumesAway} += $subpool{VolumesAway};
831 $pool{VolumesOther} += $subpool{VolumesOther};
832 $pool{VolumesOff} += $subpool{VolumesOff};
834 # $pool{VolumesCleaning} += $subpool{VolumesCleaning};
836 $pool{BytesFree} += $subpool{BytesFree} if ($subpool{BytesFree} > 0);
838 debug_out(10, "Now storing sub-pool with MediaType", $subpool{MediaType});
839 push @subpools, \%subpool;
841 $pool{MediaTypes} = \@subpools;
844 my $subcnt = scalar(@{$pool{MediaTypes}});
845 my $guess_includes_defaults = 0;
846 debug_out(10, "Summarizing Reliabilities from $subcnt sub-pools.");
847 foreach my $rel (@{$pool{MediaTypes}}) {
848 $allrels += $rel->{GuessReliability} * $rel->{VolumesTotal};
850 debug_out(15, "We have $allrels summed/weighted reliabilites and $pool{VolumesTotal} Volumes.");
851 if ($pool{VolumesTotal} > 0) {
852 $pool{GuessReliability} = $allrels / $pool{VolumesTotal};
854 $pool{GuessReliability} = "N/A";
856 push @the_pools, \%pool;
860 debug_out(10, "Printing pool data.");
862 $pool->{GuessReliability} += 1000.0 if
863 (($pool->{GuessReliability} ne "N/A") &&
864 $pool->{AvgFullUsesDefaults});
865 printf((($out_cutmarks)?" -" . " " x ($out_bargraphlen - 6) . "-\n":
867 "Pool%15.15s%s\n", "$pool->{Name}",
868 ($debug>=5)?sprintf(" %5.9s", "(" . $pool->{Id} . ")"):"");
869 my $poolbarbytes = $pool->{BytesTotal} + $pool->{BytesFree};
871 print bargraph($out_bargraphlen, 2,
873 $pool->{BytesTotal}, $pool->{BytesFree});
875 if ($out_pooldetails) {
876 print(" $pool->{VolumesTotal} Volumes ($pool->{VolumesFull} F, ",
877 "$pool->{VolumesOther} O, $pool->{VolumesPartly} A, ",
878 "$pool->{VolumesEmpty} E, $pool->{VolumesAway} W, ",
879 "$pool->{VolumesOff} X) Total ",
880 human_readable("B", $pool->{BytesTotal}),
881 " Rel: ", human_readable("P", $pool->{GuessReliability}),
882 " avail.: ", human_readable("B", $pool->{BytesFree}), "\n");
884 print bargraph_legend($out_bargraphlen, 2,
885 $pool->{BytesTotal} + $pool->{BytesFree},
886 $pool->{BytesTotal}, $pool->{BytesFree},
887 $pool->{VolumesFull}, $pool->{VolumesPartly},
888 $pool->{VolumesEmpty}, $pool->{GuessReliability});
891 debug_out(10, "Printing details:", $#{$pool->{MediaTypes}}+1, "MediaTypes");
892 if (0 < scalar($pool->{MediaTypes})) {
893 print " Details by Mediatype:\n";
894 foreach my $i (@{$pool->{MediaTypes}}) {
895 debug_out(15, "Media Type $i->{MediaType}");
896 $i->{GuessReliability} += 1000.0 if ($i->{AvgFullUsesDefaults});
897 print(" $i->{MediaType} ($i->{VolumesFull} F, ",
898 "$i->{VolumesOther} O, $i->{VolumesPartly} A, ",
899 "$i->{VolumesEmpty} E, $i->{VolumesAway} W, " ,
900 "$i->{VolumesOff} X) Total ",
901 human_readable("B", $i->{BytesTotal}), "\n");
902 if ($out_subbargraph) {
903 print bargraph($out_bargraphlen - 3, 5,
908 if ($out_subpooldetails) {
909 print " Avg, avail. Partly, Empty, Total, Rel.: ",
910 ($i->{AvgFullBytes} > 0)?human_readable("B", $i->{AvgFullBytes}):"N/A", " ",
911 ($i->{BytesFreePartly} > 0)?human_readable("B", $i->{BytesFreePartly}):"N/A", " ",
912 ($i->{BytesFreeEmpty} > 0)?human_readable("B", $i->{BytesFreeEmpty}):"N/A", " ",
913 ($i->{BytesFree} > 0)?human_readable("B", $i->{BytesFree}):"N/A", " ",
914 human_readable("P", $i->{GuessReliability}), "\n";
916 print bargraph_legend($out_bargraphlen - 3, 5,
923 $i->{GuessReliability}
924 ) if ($out_subbargraph);
931 sub bargraph_legend {
932 debug_out(15, "bargraph_legend called with ", join(":", @_));
933 my ($len, $pad, $b_all, $b_tot, $b_free, $v_total, $v_app,
934 $v_empty, $g_r) = @_;
935 if ((9 == scalar(@_)) &&
936 defined($len) && ($len >= 0) && ($len =~ /^\d+$/) &&
937 defined($pad) && ($pad >= 0) && ($pad =~ /^\d+$/) &&
938 defined($b_all) && ($b_all =~ /^\d+$/) &&
939 defined($b_tot) && ($b_tot =~ /^-?\d+$/) &&
940 defined($b_free) && ($b_free =~ /^-?\d+$/) &&
941 defined($v_total) && ($v_total =~ /^\d+$/) &&
942 defined($v_app) && ($v_app =~ /^\d+$/) &&
943 defined($v_empty) && ($v_empty =~ /^\d+$/) &&
944 ($g_r =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?/)
946 return "" if ( 0 == $b_all);
947 $b_tot = 0 if ($b_tot < 0);
948 $b_free = 0 if ($b_free < 0);
949 return "" if (0 == ($b_tot + $b_free));
951 my $l1 = human_readable("B", $b_tot) . " used ";
952 my $l2 = "Rel: " . human_readable("P", $g_r) . " free " . human_readable("B", $b_free);
953 $ll = $l1 . " " x ($len - length($l1) - length($l2)) . $l2;
954 $l1 = $v_total . " F Volumes ";
955 $l2 = $v_app . " A and " . $v_empty . " E Volumes";
956 $lm = $l1 . " " x ($len - length($l1) - length($l2)) . $l2;
957 return " " x $pad . $ll . "\n" .
958 " " x $pad . $lm . "\n";
960 debug_out(1, "bargraph_legend called without proper parameters");
966 debug_out(15, "bargraph called with ", join(":", @_));
967 my ($len, $pad, $p_all, $p_full, $p_empty) = @_;
968 if ((5 == scalar(@_)) &&
969 defined($len) && ($len >= 0) && ($len =~ /^\d+$/) &&
970 defined($pad) && ($pad >= 0) && ($pad =~ /^\d+$/) &&
971 defined($p_full) && ($p_full =~ /^-?\d+$/) &&
972 defined($p_empty) && ($p_empty =~ /^-?\d+$/) &&
973 defined($p_all) && ($p_all >= $p_full + $p_empty) &&
976 $len = 12 if ($len < 12);
977 $p_full = 0 if ($p_full < 0);
978 $p_empty = 0 if ($p_empty < 0);
979 debug_out(15, "bargraph: len $len all $p_all full $p_full empty $p_empty");
980 return " " x $pad . "Nothing to report.\n" if (0 == $p_all);
981 return "" if (0 == ($p_full + $p_empty));
982 my $contperbox = $p_all / $len;
983 my $boxfull = sprintf("%u", ($p_full / $contperbox) + 0.5);
984 my $boxempty = sprintf("%u", ($p_empty / $contperbox) + 0.5);
985 my $boxnon = $len - $boxfull - $boxempty;
986 debug_out(15, "bargraph: output $boxfull $boxempty $boxnon");
987 $contperbox = sprintf("%f", $len / 100.0);
989 my $ticks = sprintf("%u", ($len-12) / 12.5);
992 for my $i (1..$ticks) {
993 debug_out(15, "Tick loop. Previous pos: $now Previous Tick: ", $i-1);
994 my $pct = sprintf("%f", 100.0 / ($ticks+1.0) * $i);
995 $be = sprintf("%u", 0.5 + ($pct * $contperbox));
996 debug_out(15, "Tick $i ($pct percent) goes to pos $be. Chars per Percent: $contperbox");
998 debug_out(15, "Need $bl blanks to fill up.");
999 $leg .= " " x $bl . sprintf("|%2u%%", 0.5 + $pct);
1002 debug_out(15, "Fillup... Now at pos $now and $contperbox char/pct.");
1003 $be = $len - $now - 4;
1004 $leg .= " " x $be . "100%|";
1005 return " " x $pad . "#" x $boxfull . "-" x $boxempty .
1006 " " x $boxnon . "\n" . " " x $pad . "$leg\n";
1008 debug_out(1, "bargrahp called without proper parameters.");
1013 sub human_readable {
1014 debug_out(15, "human_readable called with ", join(":", @_));
1015 if (2 == scalar(@_)) {
1016 debug_out(15, "2 Params - let's see what we've got.");
1020 debug_out(15, "Working with Bytes.");
1038 return sprintf("%0.2f%s", $v, $d);
1042 debug_out(15, "Working with Percent value.");
1044 if ($v =~ /^([+-]?)(?=\d|\.\d)\d*(\.\d*)?([Ee]([+-]?\d+))?/) {
1051 $ret = sprintf("%1.0f%%", $v) . $ret;
1059 return join("", @_);
1063 sub get_default_bytes {
1064 debug_out(15, "get_default_bytes called with ", join(":", @_));
1065 if (1 == scalar(@_)) {
1066 debug_out(15, "1 Param - let's see what we've got.");
1068 /DDS/ && return 2000000000;
1069 /DDS1/ && return 2000000000;
1070 /DDS2/ && return 4000000000;
1071 /DLTIV/ && return 20000000000;
1072 /DC6525/ && return 525000000;
1073 /File/ && return 128*1024*1024;
1075 debug_out(0, "$_ is not a known Media Type. Assuming 1 kBytes");
1080 debug_out(0, "This is not right...");
1086 if ($debug >= shift) {
1099 debug_out(40, "Closing database connection...");
1100 while ($t=shift @temp_tables) {
1101 debug_out(40, "Now dropping table $t");
1102 $h_db->do("DROP TABLE $t") || debug_out(0, "Can't drop $t.");
1104 $h_db->disconnect();
1105 debug_out(40, "Database disconnected.");
1110 $ME (C) 2005 Arno Lehmann, IT-Service Lehmann
1112 produce bacula statistics to stdout
1114 usage: $ME options more help: perldoc $ME
1115 Options can be abbreviated to uniqueness. Negating --option is done
1117 --help -h print this help
1118 --version -V print version and license
1119 --host database host, default unset (use unix socket)
1120 --user -U database user, default unset
1121 --database -D database name, default "mysql:bacula"
1122 (notice database driver!)
1123 --password -P database password, default unset
1124 --debug debug level, default 0. Higer level, more output
1125 General output control:
1126 --subpools -s no subpool (Media types in each pool) details
1127 --subpool-details no more detailed information
1128 --pool-details -d no detailed pool information
1129 --pool-bargraph yes show visual pool usage, can be negated
1130 --subpool-bargraph no show visual subpool usage
1131 --bar-length -l 70 length for graphical pool usage display
1132 --cutmarks -c no print cut marks above the pools
1138 This is baculareport.pl called as $0
1139 This program was created by Arno Lehmann (al\@its-lehmann.de) in
1142 You have Version $version.
1144 This program is copyrighted, but everybody is allowed to use, modify
1145 and distribute this program under the following conditions:
1146 - This license and the original copyright holder must not be changed
1147 - The terms and the idea of the GPL apply. If you are unsure, ask
1148 the copyright holder if your planned usage is ok.
1149 - No warranties, no promises. You are all on your own.
1150 This program needs access to your bacula catalog. If you don't like
1151 that idea, don't use it or check the sourcecode!
1153 Although I give no warranties, in case of problems you can contact me.
1154 I will help as good as possible.
1155 Bacula consulting available.
1157 Bacula is a Trademark and Copyright of Kern Sibbald. See www.bacula.org