Code I wrote to make life easier for reporting data totals per group

[iamwhitey]

I thought this might help out some people, here is a perl script I wrote to retrieve the group name and data totals for a certain time period. It then emails it out to the address you specify as an excel spread sheet.

enjoy!

#!/usr/bin/perl
#
# AUTHOR: John White
#
#
# PURPOSE:
#         This Script Querys for the past weeks Backup Size per Group
#         It is then exported to a Excel Document and sent via E-Mail to the users specified
#

# To add additional E-Mail Addresses add \ before the @ sign and seperate them with ,
my $email_list = "you\@yourdomain.com";

# Contruct the Query
@report_on = ("group","sumsize");
@query_on  = ("savetime>=7 days ago");

# Reconstruct the arrays
$r_assembled = join ',', sort @report_on;
$q_assembled = join ',', sort @query_on;

use Spreadsheet::WriteExcel;
use MIME::Lite;
use Date::Format;
use XML::Simple;

# some legato binary locations
my $mminfo    = "/usr/sbin/mminfo";
my $stk_eject = "/usr/sbin/stk_eject";
my $nsrjb     = "/usr/sbin/nsrjb";
my $nsrmm     = "/usr/sbin/nsrmm";
my $mmlocate  = "/usr/sbin/mmlocate";

# generate an Excel spreadsheet
print "Generating and emailing report..\n";
my $today    = time2str( "%m/%d/%Y", time );
my $fn       = $today;
$fn =~ s/\//\./g;

my $filename = "exp-" . $fn . ".xls";

my $workbook  = Spreadsheet::WriteExcel->new( "/tmp/" . $filename );
my $worksheet = $workbook->add_worksheet();

$worksheet->set_header( '&C&"Arial, Bold" WEEKLY BACKUP REPORT ' . $today );

$worksheet->set_column( 0, 0, 30 );
$worksheet->set_column( 1, 1, 15 );

my $header = $workbook->add_format();
$header->set_bold();
$header->set_size(10);
$header->set_align('center');

$worksheet->write( 0, 0, "Group",      $header );
$worksheet->write( 0, 1, "SumSize (GB)", $header );

my $rownum = 1;

# Open the input by calling the mminfo command (man mminfo for details)
open job_input, "mminfo -r \"$r_assembled\" -q \"$q_assembled\" -xc\~ |";
while (<job_input>)
        {
chomp;
unless($killheader) {$killheader=1;next;}
@line_array = split(/\~/,$_);
($num,$modif) = split(/\ /,$line_array[1],2);
if ($modif eq "MB")     { $num = $num * 1024 * 1024; }
elsif ($modif eq "KB")  { $num = $num * 1024;  }
elsif ($modif eq "GB")  { $num = $num * 1024 * 1024 * 1024; }
elsif ($modif eq "PB")  { $num = $num * 1024 * 1024 * 1024 * 1024;}
$overall_num = $num + $overall_num;

$GROUP_HASH{$line_array[0]}=$num+$GROUP_HASH{$line_array[0]}
}
foreach $group (sort keys %GROUP_HASH )
{
$GB_USED = int($GROUP_HASH{$group} / (1024*1024*1024)) ;
                 
$worksheet->write( $rownum, 0, $group );
$worksheet->write( $rownum, 1, $GB_USED );
 $rownum++;

}

$overall_num = int(10*($overall_num / (1024*1024*1024*1024)))/10;

$workbook->close();

 #  email the report
   my $message = "\n\tGroup Backup Totals " . $today . "\n\n";
   $message .= "\t" . $overall_num . "TB has been backed up this past week.\n\n\n\n\n\n";
   $message .= "\t Message Sent from legato1 /var/stargate/admin/weekly_report.pl\n\n"; 
                my $mime_msg = MIME::Lite->new(
                        From    => "root\@your_legato_server",
                                To      => $email_list,
                                        Subject => "Group Backup Totals " . $today,
                                                Type    => "multipart/mixed"
                                                    );
                                                    
                    $mime_msg->attach( Type => "TEXT", Data => $message );
                    $mime_msg->attach( Type => "application/vnd.ms-excel", Path => "/tmp/" . $filename, Filename => $filename );
                    $mime_msg->send;
                    
                    unlink( "/tmp/" . $filename );
                    print "\nE-Mail sent to " . $email_list . ".\n";
                    print "\nPlease check your E-Mail.\n";
[code]

you need to make a few changes here and there, but other then that its good to go... pretty easy and I am sure that someone could enhance it.

 

[neussi]

hi,
this looks pretty good.
I assume that this code doesn't run in a unix enviroment ?

Spreadsheet::WriteExcel

ANdreas

 

[Know1edge]

If you have perl installed, it should, right?

 

[Chacalinc]

not unix? [tt]#!/usr/bin/perl[/tt]

Cheers.

Chacal, Inc.

 

[605]

Thank you for sharing this script with us!

 

[neussi]

Hi,
yes it´s unix for shure. I´am not the admin of the storage node and new to perl. So my posting was a bit to early. I asked my admin to install the worksheet extension of perl.
There is another way to produced such report, because mminfo can write xml files. Then Excel can read this !


Andreas