Getting information from the Netapp is easy with the web interface and the command line interface. However sometimes we may want information in a Excel format. Further we may want it automatically generated and emailed to certain recipients.
This was the case when I created the following Perl script that gathers all the volume/aggregate usage information from a Netapp filer or group of Netapp filers and sends that information via SMTP in a easy to read Excel formatted report.
The script is below and shows that it will run against 4 different Netapp filers that all have the same password and then email to bschmaus@domain.com. Note that all those variables are examples and need to be updated when using in a real environment.
#!/usr/bin/perl ######################################################### # Netapp Aggregate Report in Excel Format # ######################################################### use strict; use Net::SSH::Perl; use Spreadsheet::WriteExcel; ### Variables ### my @hosts = ("netapp1","netapp2","netapp3","netapp4"); my $user="root";my $password="pasword"; my $subject = "Netapp_Space_Utilization_Report"; my @emails = ('bschmaus\@domain.com'); my (%saw,$scon,$exit,$errors,$count,$output,$hosts,$tmparray,$aggr,$state,$value); my ($output2,$host,$flag,$vola,$volu,$volav,$aggrts,$aggrwr,$aggrsr,$aggrus,$aggrbn,$vola,$volu,$volg,$junk1,$junk2); my ($mailit,$volpu,$message,$emails,$format); my $sc1="2";my $sc2="2";my $sc3="2"; my (@output,@tmparray,$tmpaggrs,@tmpaggrs,@aggrs); ### Program Logic ### ### Setup Excel Worksheet ### my $workbook = Spreadsheet::WriteExcel->new('/tmp/netappvsr.xls'); $workbook->compatibility_mode(); my $worksheet = $workbook->add_worksheet('Aggregates'); my $worksheet = $workbook->add_worksheet('Volumes'); my $worksheet = $workbook->add_worksheet('Volume Usage in Aggregate'); my $format = $workbook->add_format(); my $header = $workbook->add_format(); create_excel(); foreach $hosts (@hosts) { print "Gathering aggregates from $hosts...\n"; $scon = Net::SSH::Perl->new ("$hosts",protocol => 2); $scon->login("$user","$password"); ($output[$count],$errors,$exit) = $scon->cmd("aggr status;logout telnet"); #print "$output[$count]\n"; $count++; } $count = 0; foreach $output (@output) { @tmparray = split(/\n/,$output); #shift(@tmparray); #pop(@tmparray); foreach $tmparray (@tmparray) { if ($tmparray =~ /aggr/) { ($aggr,$state) = split(' ',$tmparray); $value="$hosts[$count]:$aggr:$state"; push(@tmpaggrs,$value); } } $count++; } print "Sorting aggregates from Netapps...\n"; undef %saw; @saw{@tmpaggrs} = (); @aggrs = sort keys %saw; foreach (@aggrs) { ($host,$aggr,$state) = split(/:/); #print "HOST: $host AGGR: $aggr STATE: $state\n"; print "Gathering $aggr detail on $host...\n"; $scon = Net::SSH::Perl->new ("$host",protocol => 2); $scon->login("$user","$password"); ($output2,$errors,$exit) = $scon->cmd("aggr show_space -g $aggr;logout telnet"); @tmparray = split(/\n/,$output2); foreach $tmparray (@tmparray) { if (($tmparray =~ /Aggregate/) && ($tmparray =~ /\'/)) { $flag = "1"; } if (($tmparray =~ /GB/) && ( $flag eq "1" )) { ($aggrts,$aggrwr,$aggrsr,$aggrus,$aggrbn) = split(' ',$tmparray); #print "$aggrts,$aggrwr,$aggrsr,$aggrus,$aggrbn\n"; $workbook->sheets(0)->write($sc1,0,$host); $workbook->sheets(0)->write($sc1,1,$aggr); $workbook->sheets(0)->write($sc1,2,$aggrts); $workbook->sheets(0)->write($sc1,3,$aggrwr); $workbook->sheets(0)->write($sc1,4,$aggrsr); $workbook->sheets(0)->write($sc1,5,$aggrus); $sc1++; } if (($tmparray =~ /Volume/) && ($tmparray =~ /Allocated/)) { $flag="2"; } if (($tmparray =~ /vol/) && ($tmparray =~ /GB/) && ( $flag eq "2" )) { ($vola,$volu,$volg) = split(' ',$tmparray); #print "$vola,$volu,$volg\n"; $workbook->sheets(1)->write($sc2,0,$host); $workbook->sheets(1)->write($sc2,1,$vola); $workbook->sheets(1)->write($sc2,2,$volu); $workbook->sheets(1)->write($sc2,3,$volg); if ($volu eq "0GB") { $volpu = "0"; } else { $volpu = int(($volg/$volu)*100) + 1; } $workbook->sheets(1)->write($sc2,4,$volpu); $sc2++; } if (($tmparray =~ /Aggregate/) && ($tmparray =~ /Allocated/)) { $flag="3"; } if ((($tmparray =~ /Total space/) || ($tmparray =~ /Snap reserve/) || ($tmparray =~ /WAFL/)) && ($flag eq "3")) { ($junk1,$junk2,$vola,$volu,$volav) = split(/\s+/,$tmparray); #print "$junk1,$junk2,$vola,$volu,$volav\n"; $workbook->sheets(2)->write($sc3,0,$host); $workbook->sheets(2)->write($sc3,1,$aggr); if ($vola eq "0GB") { $volpu = "0"; } else { $volpu = int(($volu/$vola)*100) + 1; } if ($tmparray =~ /Total space/) { $workbook->sheets(2)->write($sc3,2,$vola); $workbook->sheets(2)->write($sc3,3,$volu); $workbook->sheets(2)->write($sc3,4,$volpu); } if ($tmparray =~ /Snap reserve/) { $workbook->sheets(2)->write($sc3,5,$vola); $workbook->sheets(2)->write($sc3,6,$volu); $workbook->sheets(2)->write($sc3,7,$volpu); } if ($tmparray =~ /WAFL/) { $workbook->sheets(2)->write($sc3,8,$vola); $workbook->sheets(2)->write($sc3,9,$volu); $workbook->sheets(2)->write($sc3,10,$volpu); $sc3++; } } } } $workbook->close(); $workbook->close(); ### Mail Off Results ### mailit(); exit; ### Setup Excel Format Subroutine ### sub create_excel { $format->set_bold(); $format->set_size(16); $format->set_align('center'); $header->set_bold(); $header->set_align('center'); $workbook->sheets(0)->set_column(0, 5, 20); $workbook->sheets(0)->write(1, 0, 'Host', $header); $workbook->sheets(0)->write(1, 1, 'Aggregate', $header); $workbook->sheets(0)->write(1, 2, 'Total Space(GB)', $header); $workbook->sheets(0)->write(1, 3, 'WAFL Reserve(GB)', $header); $workbook->sheets(0)->write(1, 4, 'Snap Reserve(GB)', $header); $workbook->sheets(0)->write(1, 5, 'Usable Space(GB)', $header); $workbook->sheets(1)->set_column(0, 4, 20); $workbook->sheets(1)->write(1, 0, 'Host', $header); $workbook->sheets(1)->write(1, 1, 'Volume', $header); $workbook->sheets(1)->write(1, 2, 'Allocated(GB)', $header); $workbook->sheets(1)->write(1, 3, 'Used(GB)', $header); $workbook->sheets(1)->write(1, 4, '%Used', $header); $workbook->sheets(2)->set_column(0, 10, 25); $workbook->sheets(2)->write(1, 0, 'Host', $header); $workbook->sheets(2)->write(1, 1, 'Aggregate', $header); $workbook->sheets(2)->write(1, 2, 'Total Space Allocated(GB)', $header); $workbook->sheets(2)->write(1, 3, 'Total Space Used(GB)', $header); $workbook->sheets(2)->write(1, 4, '%Total Space Used', $header); $workbook->sheets(2)->write(1, 5, 'Snap Reserve Allocated(GB)', $header); $workbook->sheets(2)->write(1, 6, 'Snap Reserve Used(GB)', $header); $workbook->sheets(2)->write(1, 7, '%Snap Reserve Used', $header); $workbook->sheets(2)->write(1, 8, 'WAFL Reserved Allocated(GB)', $header); $workbook->sheets(2)->write(1, 9, 'WAFL Reserved Used(GB)', $header); $workbook->sheets(2)->write(1, 10, '%WAFL Reserved Used', $header); $workbook->sheets(0)->merge_range('A1:F1','NetApp Storage Report',$format); $workbook->sheets(1)->merge_range('A1:E1','NetApp Storage Report',$format); $workbook->sheets(2)->merge_range('A1:I1','NetApp Storage Report',$format); } ### Mail Subroutine ### sub mailit { $message = `echo "NetApp Storage Report Attached">/tmp/nvsr-body.txt`; $message = `echo "">>/tmp/nvsr-body.txt`; $message = `/usr/bin/uuencode /tmp/netappvsr.xls netappvsr.xls > /tmp/nvsr-attachment.txt`; $message = `cat /tmp/nvsr-body.txt /tmp/nvsr-attachment.txt > /tmp/nvsr.txt`; foreach $emails (@emails) { $mailit = `/usr/bin/mailx -s $subject $emails < /tmp/nvsr.txt`; } }
