aleks-mariusz/parsePerfsh-solaris_v2.pl

## parsePerfsh-solaris_v2.pl
#!/usr/bin/perl

$| = 1;

BEGIN { unshift(@INC, "/usr/perl5/site_perl/5.005" ); };

use strict;
use Time::ParseDate;
use Spreadsheet::WriteExcel;

unless ($#ARGV == 0) {
	my $progName = ($0 =~ m#^.+/([^/]+)$#)[0];

	print "Usage: $progName <pathToPerf.SHdirectory>\n\n";
	exit 1;
}

my $perfDir = shift;

if (! -d $perfDir) {
	print "No such directory: $perfDir\n";
	exit 1;
}

my $dataSet;

$dataSet = &loadData($perfDir);
&generateReport($dataSet);

use Data::Dumper; print &Dumper($dataSet);

sub loadData { my $dir = shift;
	my %data;

	print "Now loading data from $dir (please wait).. ";
	foreach my $currType (qw/iostat mpstat netstat vmstat/) {
		my $currFile = (glob("$dir/$currType.*"))[0];

		next unless -f $currFile;
		print "\n\t- processing $currType data.. ";
		&loadDataFile(\%data,$currFile,$currType);
	}

	print "\nDone loading data into memory!\n\n";
	return \%data;
}

sub loadDataFile { my ($ds,$file,$type) = @_;
	my $setDate;    # current run's start date
	my @field;	# will contain the field/key names

	open FILE, $file || die "Cannot open file $1: $!\n";
	undef $/;	# slurp entire file into one scalar variable
	$file = <FILE>; # read file into memory
	close FILE;

	if ($type eq "iostat") {

		my %tempIOSTAT; # hash to hold split io runs by start date

		# list of io runs split up per execution
		my @dateList = split /\n\n(Sun|Mon|Tue|Wed|Thu|Fri|Sat) /, $file;

		# go through each execution
		foreach my $currSet (@dateList) {

			next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

			# grab the output of the 'date' command that was saved immediatley before io run started
			$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

			# and split/save each "line" from iostat
			$tempIOSTAT{$setDate} = [ split /\n\s+extended device statistics/, $currSet ];
		} # done going through all explicit dates

		# get a list of dates, in chronological order
		@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempIOSTAT;

		# we'll go through each date from beginning to end (may want to filter this)
		for (my $i = 0; $i < $#dateList; ++$i) {

			# figure out the bounds for this iostat's run
			my $baseTS = &parsedate($dateList[$i]);
			my $endTS = &parsedate($dateList[$i+1]);

			# save the individual "lines" of this iostat
			my @iostats = @{$tempIOSTAT{$dateList[$i]}};
			shift @iostats; # very first line is blank
			shift @iostats; # first "line" is sys average since system boot

			# we need to determine how much time was used between "lines"
			my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@iostats);

			# go through each "line" of iostat's run
			for (my $j = 0; $j <= $#iostats; ++$j) {

				# figure which time this run represents
				my $currTS = $baseTS + $interval*$j;

				# split the "line" up each respective device
				my @deviceStats = split /\n/, $iostats[$j];
				shift @deviceStats; # first line is blank

				# extract the field names
				@field = split /\s+/, $deviceStats[0];
				shift @deviceStats; # done with that, so lose the header information

				# go through each remaining device
				foreach my $currStat (@deviceStats) {

					# split each stat for this device
					my @stat = split /\s+/, $currStat;

					# save the device's stat
					$ds->{$type}->{$currTS}->{$stat[0]} = {
						map {
							$field[$_], $stat[$_]
						} (1 .. $#field)
					};
				}
			}
		}

	} elsif ($type eq "mpstat") {

		my %tempMPSTAT; # hash to hold split cpu stats by start date

		# list of cpu stats split up per execution
		my @dateList = split /\n\n(Sun|Mon|Tue|Wed|Thu|Fri|Sat) /, $file;

		# go through each execution
		foreach my $currSet (@dateList) {

			next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

			# grab the output of the 'date' command that was saved immediatley before io run started
			$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

			# and split/save each "line" from mpstat
			$tempMPSTAT{$setDate} = [ split /\n(?=CPU)/, $currSet ];
		} # done going through all explicit dates

		# get a list of dates, in chronological order
		@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempMPSTAT;

		# we'll go through each date from beginning to end (may want to filter this)
		for (my $i = 0; $i < $#dateList; ++$i) {

			# figure out the bounds for this mpstat's run
			my $baseTS = &parsedate($dateList[$i]);
			my $endTS = &parsedate($dateList[$i+1]);

			# save the individual "lines" of this mpstat
			my @mpstats = @{$tempMPSTAT{$dateList[$i]}};
			shift @mpstats; # very first line is the date

			# we need to determine how much time was used between "lines"
			my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@mpstats);
			shift @mpstats; # first "line" is sys average since system boot

			# go through each "line" of mpstat's run
			for (my $j = 0; $j <= $#mpstats; ++$j) {

				# figure which time this run represents
				my $currTS = $baseTS + $interval*$j;

				# split the "line" up each set of respective cpu statistics
				my @cpuStats = split /\n/, $mpstats[$j];

				# extract the field names
				if ($cpuStats[0] =~ /CPU/) {
					@field = split /\s+/, $cpuStats[0];
					shift @cpuStats; # done with that, so lose the header information
				}

				# go through each remaining CPU
				foreach my $currCPU (@cpuStats) {

					# split each stat for this CPU
					my @CPU = split /\s+/, $currCPU;

					# save the cpu's stat
					$ds->{$type}->{$currTS}->{$CPU[1]} = {
						map {
							$field[$_], $CPU[$_+1]
						} (1 .. $#field)
					};
				}
			}
		}

	} elsif ($type eq "netstat") {

		my %tempNETSTAT; # hash to hold split cpu stats by start date

		# list of cpu stats split up per execution
		my @dateList = split /\n\n(Sun|Mon|Tue|Wed|Thu|Fri|Sat) /, $file;

		# go through each execution
		foreach my $currSet (@dateList) {

			next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

			# grab the output of the 'date' command that was saved immediatley before io run started
			$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

			# and split/save each "line" from netstat
			$tempNETSTAT{$setDate} = [ split /\n(?=\d)/, $currSet ];
		} # done going through all explicit dates

		# get a list of dates, in chronological order
		@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempNETSTAT;

		# we'll go through each date from beginning to end (may want to filter this)
		for (my $i = 0; $i < $#dateList; ++$i) {

			# figure out the bounds for this netstat's run
			my $baseTS = &parsedate($dateList[$i]);
			my $endTS = &parsedate($dateList[$i+1]);

			# save the individual "lines" of this netstat
			my $fieldInfo = shift @{$tempNETSTAT{$dateList[$i]}}; # contains header information
			my @netstats = grep { /^\d/ } @{$tempNETSTAT{$dateList[$i]}};
			shift @netstats; # first "line" is sys average since system boot

			# we need to determine how much time was used between lines
			my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@netstats);

			@field = &parseFieldNames($fieldInfo);

			# go through each "line" of netstat's run
			for (my $j = 0; $j <= $#netstats; ++$j) {

				# figure which time this run represents
				my $currTS = $baseTS + $interval*$j;

				# split the "line" up each respective net statistic
				my @netStat = split /\n/, $netstats[$j];

				# go through each net statistics
				foreach my $currNS (@netStat) {

					# split each stat for this network statistic
					my @NS = split /\s+/, $currNS;

					# save the network's stat
					$ds->{$type}->{$currTS} = {
						map {
							$field[$_], $NS[$_]
						} (0 .. $#field)
					};
				}
			}
		}

	} elsif ($type eq "vmstat") {

		my %tempVMSTAT; # hash to hold split cpu stats by start date

		# list of vm stats split up per execution
		my @dateList = split /\n\n(Sun|Mon|Tue|Wed|Thu|Fri|Sat) /, $file;

		# go through each execution
		foreach my $currSet (@dateList) {

			next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

			# grab the output of the 'date' command that was saved immediatley before io run started
			$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

			# and split/save each "line" from vmstat
			$tempVMSTAT{$setDate} = [ split /\n\s+(?=\d)/, $currSet ];
		} # done going through all explicit dates

		# get a list of dates, in chronological order
		@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempVMSTAT;

		# we'll go through each date from beginning to end (may want to filter this)
		for (my $i = 0; $i < $#dateList; ++$i) {

			# figure out the bounds for this vmstat's run
			my $baseTS = &parsedate($dateList[$i]);
			my $endTS = &parsedate($dateList[$i+1]);

			# save the individual "lines" of this vmstat
			my $fieldInfo = shift @{$tempVMSTAT{$dateList[$i]}}; # contains header information
			my @vmstats = map { (/^([^\n]+)/)[0] } @{$tempVMSTAT{$dateList[$i]}};
			shift @vmstats; # first "line" is sys average since system boot

			# we need to determine how much time was used between lines
			my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@vmstats);

			@field = &parseFieldNames($fieldInfo);

			# go through each "line" of vmstat's run
			for (my $j = 0; $j <= $#vmstats; ++$j) {

				# figure which time this run represents
				my $currTS = $baseTS + $interval*$j;

				# split the line up each set of statistic
				my @vmStat = split /\n/, $vmstats[$j];

				# go through each set of statistic
				foreach my $currVMS (@vmStat) {

					# split each stat for this set
					my @VMS = split /\s+/, $currVMS;

					# save the stat
					$ds->{$type}->{$currTS} = {
						map {
							$field[$_], $VMS[$_]
						} (0 .. $#field)
					};
				}
			}
		}
	}
}

sub parseFieldNames { my $fieldInfo = shift;

	my @fieldNames;

	my @fieldHeaderLines = split /\n/, $fieldInfo;

	if ($fieldHeaderLines[-2] =~ /\(Total\)/) {
		# these are netstat fields

		# input   ce0       output           input  (Total)    output
		# packets errs  packets errs  colls  packets errs  packets errs  colls
		my $intName = "mainInt";
		$intName = $1 if $fieldHeaderLines[-2] =~ /^\s+input\s+(\S+)\s+output/;

		@fieldNames = ( "$intName-input-packets",
		        	"$intName-input-errs",
				"$intName-output-packets",
				"$intName-output-errs",
				"$intName-colls",
				"Total-input-packets",
				"Total-input-errs",
				"Total-output-packets",
				"Total-output-errs",
				"Total-colls"
		);
	} elsif ($fieldHeaderLines[-2] =~ /memory/) {
		# these are vmstat fields

		#  kthr      memory            page            disk          faults      cpu
		# r b w   swap  free  re  mf pi po fr de sr m0 m1 m2 m1   in   sy   cs us sy id

		my $diskNames;
		$diskNames = $1 if
			$fieldHeaderLines[-1] =~
				/\s+r\s+b\s+w\s+swap\s+free\s+re\s+mf\s+pi\s+po\s+fr\s+de\s+sr\s+(.+)\s+in\s+sy\s+cs\s+us\s+sy\s+id/;

		my @diskNames = map { "disk-$_" } (split /\s+/, $diskNames);

		@fieldNames = ( "kthr-r",
		        	"kthr-b",
				"kthr-w",
				"memory-swap",
				"memory-free",
				"page-re",
				"page-mf",
				"page-pi",
				"page-po",
				"page-fr",
				"page-de",
				"page-sr",
				@diskNames,
				"faults-in",
				"faults-sy",
				"faults-cs",
				"cpu-us",
				"cpu-sy",
				"cpu-id"
		);

	}

	return (@fieldNames);
}

sub generateReport { my $ds = shift;
	my ($timeStart,$timeEnd,$workBook,$workSheet,$tsArrayVM,$tsArrayNS,$tsArrayIO,$interval,$fmt);

	$/ = "\n";
#	do {	print "Please enter date/time or timestamp of when you want reporting to begin: ";
#		$timeStart = <>; chomp $timeStart;
#		print "\n";
#		$timeStart = &parsedate($timeStart) if $timeStart !~ /^\d+$/;
	#
	#} while ($timeStart !~ /^\d+$/);

	#do { 	print "Please enter date/time or timestamp of when you want reporting to stop: ";
	#	$timeEnd = <>; chomp $timeEnd;
	#	print "\n";
	#	$timeEnd = &parsedate($timeEnd) if $timeEnd !~ /^\d+$/;
	#} while ($timeEnd !~ /^\d+$/);

	($timeStart,$timeEnd) = (0,99999999999999999);

	# create a list of arrayrefs for each set of data, for the interest set of times/dates requested
	$tsArrayNS = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'netstat'}}) ];
	$tsArrayVM = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'vmstat'}}) ];
	$tsArrayIO = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'iostat'}}) ];

	$workBook = Spreadsheet::WriteExcel->new("perf-cpu.xls");

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet = $workBook->add_worksheet("Processor");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:F', 5);
	$workSheet->set_row(0, 100);

	$workSheet->write_row(
		0,
		0,
		[ 	"Time",
			"CPU Utilization",
			"CPU in System Mode",
			"CPU in User Mode",
			"Interrupt/sec",
			"Context Switch/sec"
		],
		$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayVM; ++$i ) {

#		$interval = $tsArrayVM->[$i+1]-$tsArrayVM if $tsArrayVM->[$i+1];

		$workSheet->write_row(
			$i+1,
			0,
			[ 	&formatDate($tsArrayVM->[$i]),
				(100 - $ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-id'})."%",
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-sy'}."%",
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-us'}."%",
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'faults-in'},
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'faults-cs'},
			],
			$fmt
		);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-mem.xls");

	$workSheet = $workBook->add_worksheet("Memory System");

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:D', 5);
	$workSheet->set_row(0, 100);

	$workSheet->write_row(
		0,
		0,
		[ 	"Time",
			"Page-in rate/sec",
			"Page-out rate/sec",
			"Paging rate/sec",
		],
		$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayVM; ++$i ) {

#		$interval = $tsArrayVM->[$i+1]-$tsArrayVM if $tsArrayVM->[$i+1];

		$workSheet->write_row(
			$i+1,
			0,
			[ 	&formatDate($tsArrayVM->[$i]),
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-pi'},
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-po'},
				$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-pi'}+$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-po'}

			],
			$fmt
		);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-io.xls");
	$workSheet = $workBook->add_worksheet("Disk IO");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:C', 10);
	$workSheet->set_row(0, 100);

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->write_row(
		0,
		0,
		[ 	"Time",
			"Disk Read rate/s",
			"Disk Write rate/s",
		],
		$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayIO; ++$i ) {

#		$interval = $tsArrayIO->[$i+1]-$tsArrayIO if $tsArrayIO->[$i+1];

		$workSheet->write_row(
			$i+1,
			0,
			[ 	&formatDate($tsArrayIO->[$i]),
				$ds->{'iostat'}->{$tsArrayIO->[$i]}->{'md0'}->{'kr/s'},
				$ds->{'iostat'}->{$tsArrayIO->[$i]}->{'md0'}->{'kw/s'},

			],
			$fmt
		);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-network.xls");
	$workSheet = $workBook->add_worksheet("Network");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:F', 8);
	$workSheet->set_row(0, 100);

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->write_row(
		0,
		0,
		[ 	"Time",
			"Collision rate/min",
			"Incoming pkt error/min",
			"Incoming pkt rate/min",
			"Outgoing pkg error/min",
			"Outgoing pkg rate/min"
		],
		$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayNS; ++$i ) {

		$interval = $tsArrayNS->[$i+1]-$tsArrayNS->[$i] if $tsArrayNS->[$i+1];

		$workSheet->write_row(
			$i+1,
			0,
			[ 	&formatDate($tsArrayNS->[$i]),
				int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-colls'}/$interval),
				int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-input-errs'}/$interval),
				int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-input-packets'}/$interval),
				int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-output-errs'}/$interval),
				int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-output-packets'}/$interval),
			],
			$fmt
		);
	}
	$workBook->close();

}

sub formatDate {
	my $timeStamp = shift || return;
	my @tempTime = localtime($timeStamp); # format of the moth names

	# return a nice string with the appropriately formatted date
	return sprintf "%02d/%02d/%02d %02d:%02d:%02d",
		$tempTime[4]+1, $tempTime[3], $tempTime[5]-100, $tempTime[2], $tempTime[1], $tempTime[0];
}

## weeklyUsage_v3.5.pl
#!/usr/bin/perl
$| = 1;

use strict; no strict 'refs';	# discipline is good
use Time::ParseDate;
use Spreadsheet::WriteExcel;
use Spreadsheet::WriteExcel::Utility;

die "Need exactly one argument: file with list of hosts to query!" unless $#ARGV == 0;

my %sar; 	# will contain sar info on a per host basis
my %mem;	# will contain amount of system memory on a per host basis
my %date;	# will contain list of valid dates to report fields for
my @host;	# read in from a file, list of hosts to log into
my %hostRow;    # will hold what row the host ended up being in
my $sarThreshold = 0;	# need at least this many hosts with the date to include in the final report
my ($sarTimeFrom,$sarTimeTo) = qw/0 23/; # what times of day to look at values (in 24-hr time)
my ($sarDayFrom,$sarDayTo) = qw/0 6/; # what days of the week to look at values (0 - Sun, 6 - Sat)
my ($row,$col);
my @dataType = qw/CPU-Avg MEM-Avg/;

my @prog = qw/x | \/ - \\/;	# progress indicator characters
my $prog = 0;			# progress indicator counter

my @timeList = localtime(time);
my $nowYear = $timeList[5]+1900;# the ls output needs to be corrected for the appropriate
my $nowMon = $timeList[4];	#+year, and these values help figure out what the year is

my @monthName = qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/; 	# used to populate the hash

# hash of month name to numeric translations (so i can avoid using the 3rd party Time::ParseDate module
my %monthName = map { $monthName[$_], $_ } (0 .. $#monthName);

# will be used as part of the filename
my $filename = "weekly-".&formatDate($monthName[$nowMon]." ".$timeList[3]." ".$nowYear).".xls";

my $workBook = Spreadsheet::WriteExcel->new($filename);

my $fmtPercentage = $workBook->add_format(); $fmtPercentage->set_num_format('0.0%');
my $fmtPercentage2 = $workBook->add_format(); $fmtPercentage2->set_num_format('0.0%'); $fmtPercentage2->set_fg_color('yellow');
my $fmtRotated = $workBook->add_format(); $fmtRotated->set_num_format('dd-mm'); $fmtRotated->set_rotation(90); $fmtRotated->set_align('right'); $fmtRotated->set_align('bottom');

undef $/;	# we'll use an undefined field seperator to slurp the entire file into a scalar
# here we'll read in the file and put it into a array
open HOSTLIST, $ARGV[0] || die "Cannot open $ARGV[0]: $!\n"; {
	my $hostList = <HOSTLIST>;		# defined in 'local' context, doesn't exist afterwards
	@host = split /\n/, $hostList; }	#+which is my sorry way of saving memory, lol
close HOSTLIST;

print "\nNow logging into hosts & retrieving information..  ";
foreach my $currHost (grep { !/^=/ } @host) {
	++$prog; print "\x08"; print $prog[$prog%$#prog+1];	# progress indicator

	# log into each host, run
	# 1.) prtconf
	#  a.) grep for memroy
	# 3.) bash with sh-style for loop to run on all four-letter files in /var/adm/sa/ that begin with "sa"
	#  a.) run an ls -ld on them (to get the timestamp)
	#  b.) run sar -u to get cpu info from that file
	#  c.) run sar -r to get mem info from that file
#	my $sshOutSAR = `ssh $currHost "/etc/swap -s; /usr/sbin/prtconf|grep Memory;bash -c 'for i in /var/adm/sa/sa??; do ls -ld \\\$i; /usr/bin/sar -u -f \\\$i; /usr/bin/sar -r -f \\\$i; done'" 2>/dev/null`;

	my $sshOutSAR = `ssh $currHost "/usr/sbin/prtconf|grep Memory;bash -c 'for i in /var/adm/sa/sa??; do ls -ld \\\$i; /usr/bin/sar -u -f \\\$i; /usr/bin/sar -r -f \\\$i; done'" 2>/dev/null`;
# THIS SECTION WORKS ON THE SAR OUTPUT
	next unless $sshOutSAR =~ /Average/;	# there is nothing to do with this host if no sar (no average reported)

	$mem{$currHost} = $1 if $sshOutSAR =~ /Memory\s+size:\s+(\d+)\s+M/; 	# save amount of memory on system
#	$sshOutSAR =~ s/^[^\n]+\n//;						#+and remove from input

	# for each log file entry up (which consists of the ls, the sar -u and the sar -r)
	foreach my $currLog (split /\n-rw/, $sshOutSAR) {
		next unless $currLog =~ /Average/;	# this is an empty log file if no Average string appears

		# break up current log commands between the ls, the sar -u, and the sar -r
		my ($logFile,$sarCPU,$sarMEM) = split(/\nSunOS/,$currLog);
		next unless $logFile;	# if we have no ls, then we have no log file

		# retreiving the timestamp on the log file (from the ls)
		$logFile =~ /\d+\s(\S+\s+\d+)\s+(\S+)\s\/var\/adm/;
		my ($currDay,$currYear) = ($1,$2); # saving the Month N,
		if ($currYear =~ /\d+:\d+/) { 	# check if the ls command included a time instead of a year, this
			$currYear = $nowYear; 	#+means it was less than 6 months ago, so we correct it:

			# but if today's month is less than a half year, then any months from the latter twelve
			#+months of a year, obvious refer to the last year, here we're correcting for that too
			--$currYear if $nowMon < 6 && ($monthName{($currDay =~ /^(\S+)\s/)[0]}) > 5;
		}
		# we are interested in only running this on certain days..  here we get the day of the week for this date
		my $dayOfWeek = (localtime(&parsedate($currDay." ".$currYear)))[6];
		next if $dayOfWeek < $sarDayFrom || $dayOfWeek > $sarDayTo;	#+ and skip if it isn't in our range

		my (	# since the utilization reports are the amount unused, we start with large values
			$sarCPUpeak,$sarMEMpeak,$sarSWAPpeak,
			$sarCPUavg,$sarMEMavg,$sarSWAPavg,
			$avgCounter,$avgSum
		) = ( 999,99999999,99999999999,0,0,0,0,0 );

		# fetch the average idle cpu time
		$avgCounter = 0; $avgSum = 0;
		foreach my $currSARcpu (split /\n/, $sarCPU) { # look for the smallest idle
			next unless $currSARcpu =~ /^(\d+):\d+:\d+\s+\d+\s+\d+\s+\d+\s+(\d+)/;
			next unless $1 >= $sarTimeFrom && $1 <= $sarTimeTo;	# only interested in certain times of day

			++$avgCounter; $avgSum += $2;
			$sarCPUpeak = $2 if $2 < $sarCPUpeak;
		}
		$sarCPUavg = ($avgSum/$avgCounter) unless $avgCounter == 0;	# calculate average

		# fetch the average free memory
		$avgCounter = 0; $avgSum = 0;
		foreach my $currSARmem (split /\n/, $sarMEM) { # look for the smallest free-mem
			next unless $currSARmem =~ /^(\d+):\d+:\d+\s+(\d+)\s+/;
			next unless $1 >= $sarTimeFrom && $1 <= $sarTimeTo;	# only interested in certain times of day

			++$avgCounter; $avgSum += $2;
			$sarMEMpeak = $2 if $2 < $sarMEMpeak;
		}
		$sarMEMavg = ($avgSum/$avgCounter) unless $avgCounter == 0;	# calculate average

		++$date{$currDay. " ". $currYear}; 	# retain this date for when we print out the column headers

		# save the avg/peak cpu/mem values based on date, in this elaborate data structure
		$sar{$currHost}->{$currDay. " ". $currYear} = {
			'CPU' => {
				'Avg' => $sarCPUavg,
			},
			'MEM' => {
				'Avg' => $sarMEMavg,
			}
		};

	} # done going through each log file

} # done going through all hosts
#print "\x08 \n\nCut and Paste this into Excel\n";
print "\x08Done\n\nNow creating Excel speadsheet (\"$filename\")... ";

# debugging purposes only
#use Data::Dumper;
#print &Dumper(\@host);
#print &Dumper(\%sar);
#print &Dumper(\%fs);

# get a list of times, in chronological order
@timeList = reverse ( (
		map {
			$_->[0]
		} sort {
			$b->[1] <=> $a->[1] || $b->[2] <=> $a->[2] || $b->[3] <=> $a->[3] # reverse chron order
		} map { # Scwartzian transform sort technique
			[ $_, ($_ =~ /(\d{4}$)/)[0], $monthName{($_ =~ /^(\w+)\s/)[0]}, ($_ =~ /(\d+)\s\d{4}/)[0] ]
		} grep {
			$date{$_} > $sarThreshold
		} keys %date
	)[0..6]
);

# go through each stat (currently only CPU and MEM)
foreach my $currStatFull (@dataType) {
	my ($currStat,$currType) = split /-/, $currStatFull;

	# will be populated with location of the hosts
	%hostRow = ( );

	# create the work sheet, set some widths for the rows
	my $workSheet = $workBook->add_worksheet($currStat);
	$workSheet->set_column('A:A',16);
	$workSheet->set_column('B:H', undef, $fmtPercentage);
	$workSheet->set_column('I:I',128);
	$workSheet->set_row('A:A', 72, undef);

	($row,$col) = (0,1);

	# draw out the timestamps
	map { $workSheet->write($row,$col++, &formatDate2($_), $fmtRotated) } @timeList;

	# go through all entries, including hosts as well as groups
	foreach my $currHost (@host) {
		# if we're not a group, and we have no stats, what's to print? let's skip past this one
		next if $currHost !~ /^=/ && $sar{$currHost}->{$timeList[0]}->{$currStat}->{$currType} eq "";

		# if this is a group..
		if ($currHost =~ /^=/) {
			my $group = $currHost; $group =~ s/^=\s*//;
			my @groupHost = grep { $sar{$_}->{$timeList[0]}->{$currStat}->{$currType} } map { s/\s+//; $_ } split (/\s+/, $group);
			next unless scalar(@groupHost);
		} # we want to make sure that its members has data as well

		# if we got this far, we deserve to advance a row, and save the host's row location
		++$row; $col = 0; $hostRow{$currHost} = $row;

		# if this is not a group (it's a host)
		if ($currHost !~ /^=/) {
			$workSheet->write($row,$col++,$currHost);
			map {
				$workSheet->write($row,$col++,&{"format".$currStat}($sar{$currHost}->{$_}->{$currStat}->{$currType}, $mem{$currHost}))
			} @timeList;
		}
	} # done going through all possible hosts and groups

	# go through only the groups
	foreach my $currGroup (grep { /^=/ } @host) {
		# get the previously saved locations
		($row,$col) = ($hostRow{$currGroup},0);

		# don't ask me why, but changing $currGroup seems to reflect the change back in @hosts.. is this a bug or what?
		my $group = $currGroup; $group =~ s/^=\s*//;

		# get a list of hosts, but only ones with data in them
		my @groupHost = grep { $sar{$_}->{$timeList[0]}->{$currStat}->{$currType} } map { s/\s+//; $_ } split (/\s+/, $group);
		next unless scalar(@groupHost); # if there's no hosts found with data, we don't want to print this line

		# go through each date available and print out the formula to calculate the average for all the hosts
		map {	++$col;
			$workSheet->write(
				$row,
				$col,
				( scalar(@groupHost) ? "=AVERAGE(".join(",",map { &xl_rowcol_to_cell($hostRow{$_},$col) } @groupHost).")" : "" ),
				$fmtPercentage2
			);
		} @timeList;

		# record the hosts that were used in this line's calculations
		$workSheet->write($row,++$col,join(",",@groupHost));
	}
}

print "Done!\n";

# take the Mon N Year, and conver it to the YYYYMMDD format
sub formatDate { local($_) = $_[0];
	my ($mon,$day,$yr) = split /\s+/;
	sprintf "%04d%02d%02d", $yr, $monthName{$mon}+1, $day;
}

# take the Mon N Year format and convert it to the MM-DD-YYYY format that is compatible with excel
sub formatDate2 { local($_) = $_[0];
	my ($mon,$day,$yr) = split /\s+/;
	sprintf "$day-$mon";
}

# take the idle time, and figure out the inverse, create a percentage val
sub formatCPU { local($_) = $_[0]; sprintf "%.5f", ((100-$_)/100); }

# take the free memory, convert it to the same unit as the free memory amount, and create a percentage val
sub formatMEM { my ($freeMem,$totalMem) = @_; sprintf "%.5f", (1-($freeMem * 8192 / 1024 / 1024)/$totalMem); }
	#!/usr/bin/perl

	$\| = 1;

	BEGIN { unshift(@INC, "/usr/perl5/site_perl/5.005" ); };

	use strict;
	use Time::ParseDate;
	use Spreadsheet::WriteExcel;

	unless ($#ARGV == 0) {
	my $progName = ($0 =~ m#^.+/([^/]+)$#)[0];

	print "Usage: $progName <pathToPerf.SHdirectory>\n\n";
	exit 1;
	}

	my $perfDir = shift;

	if (! -d $perfDir) {
	print "No such directory: $perfDir\n";
	exit 1;
	}

	my $dataSet;

	$dataSet = &loadData($perfDir);
	&generateReport($dataSet);

	use Data::Dumper; print &Dumper($dataSet);

	sub loadData { my $dir = shift;
	my %data;

	print "Now loading data from $dir (please wait).. ";
	foreach my $currType (qw/iostat mpstat netstat vmstat/) {
	my $currFile = (glob("$dir/$currType.*"))[0];

	next unless -f $currFile;
	print "\n\t- processing $currType data.. ";
	&loadDataFile(\%data,$currFile,$currType);
	}

	print "\nDone loading data into memory!\n\n";
	return \%data;
	}

	sub loadDataFile { my ($ds,$file,$type) = @_;
	my $setDate; # current run's start date
	my @field; # will contain the field/key names

	open FILE, $file \|\| die "Cannot open file $1: $!\n";
	undef $/; # slurp entire file into one scalar variable
	$file = <FILE>; # read file into memory
	close FILE;

	if ($type eq "iostat") {

	my %tempIOSTAT; # hash to hold split io runs by start date

	# list of io runs split up per execution
	my @dateList = split /\n\n(Sun\|Mon\|Tue\|Wed\|Thu\|Fri\|Sat) /, $file;

	# go through each execution
	foreach my $currSet (@dateList) {

	next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

	# grab the output of the 'date' command that was saved immediatley before io run started
	$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

	# and split/save each "line" from iostat
	$tempIOSTAT{$setDate} = [ split /\n\s+extended device statistics/, $currSet ];
	} # done going through all explicit dates

	# get a list of dates, in chronological order
	@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempIOSTAT;

	# we'll go through each date from beginning to end (may want to filter this)
	for (my $i = 0; $i < $#dateList; ++$i) {

	# figure out the bounds for this iostat's run
	my $baseTS = &parsedate($dateList[$i]);
	my $endTS = &parsedate($dateList[$i+1]);

	# save the individual "lines" of this iostat
	my @iostats = @{$tempIOSTAT{$dateList[$i]}};
	shift @iostats; # very first line is blank
	shift @iostats; # first "line" is sys average since system boot

	# we need to determine how much time was used between "lines"
	my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@iostats);

	# go through each "line" of iostat's run
	for (my $j = 0; $j <= $#iostats; ++$j) {

	# figure which time this run represents
	my $currTS = $baseTS + $interval*$j;

	# split the "line" up each respective device
	my @deviceStats = split /\n/, $iostats[$j];
	shift @deviceStats; # first line is blank

	# extract the field names
	@field = split /\s+/, $deviceStats[0];
	shift @deviceStats; # done with that, so lose the header information

	# go through each remaining device
	foreach my $currStat (@deviceStats) {

	# split each stat for this device
	my @stat = split /\s+/, $currStat;

	# save the device's stat
	$ds->{$type}->{$currTS}->{$stat[0]} = {
	map {
	$field[$_], $stat[$_]
	} (1 .. $#field)
	};
	}
	}
	}

	} elsif ($type eq "mpstat") {

	my %tempMPSTAT; # hash to hold split cpu stats by start date

	# list of cpu stats split up per execution
	my @dateList = split /\n\n(Sun\|Mon\|Tue\|Wed\|Thu\|Fri\|Sat) /, $file;

	# go through each execution
	foreach my $currSet (@dateList) {

	next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

	# grab the output of the 'date' command that was saved immediatley before io run started
	$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

	# and split/save each "line" from mpstat
	$tempMPSTAT{$setDate} = [ split /\n(?=CPU)/, $currSet ];
	} # done going through all explicit dates

	# get a list of dates, in chronological order
	@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempMPSTAT;

	# we'll go through each date from beginning to end (may want to filter this)
	for (my $i = 0; $i < $#dateList; ++$i) {

	# figure out the bounds for this mpstat's run
	my $baseTS = &parsedate($dateList[$i]);
	my $endTS = &parsedate($dateList[$i+1]);

	# save the individual "lines" of this mpstat
	my @mpstats = @{$tempMPSTAT{$dateList[$i]}};
	shift @mpstats; # very first line is the date

	# we need to determine how much time was used between "lines"
	my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@mpstats);
	shift @mpstats; # first "line" is sys average since system boot

	# go through each "line" of mpstat's run
	for (my $j = 0; $j <= $#mpstats; ++$j) {

	# figure which time this run represents
	my $currTS = $baseTS + $interval*$j;

	# split the "line" up each set of respective cpu statistics
	my @cpuStats = split /\n/, $mpstats[$j];

	# extract the field names
	if ($cpuStats[0] =~ /CPU/) {
	@field = split /\s+/, $cpuStats[0];
	shift @cpuStats; # done with that, so lose the header information
	}

	# go through each remaining CPU
	foreach my $currCPU (@cpuStats) {

	# split each stat for this CPU
	my @CPU = split /\s+/, $currCPU;

	# save the cpu's stat
	$ds->{$type}->{$currTS}->{$CPU[1]} = {
	map {
	$field[$_], $CPU[$_+1]
	} (1 .. $#field)
	};
	}
	}
	}

	} elsif ($type eq "netstat") {

	my %tempNETSTAT; # hash to hold split cpu stats by start date

	# list of cpu stats split up per execution
	my @dateList = split /\n\n(Sun\|Mon\|Tue\|Wed\|Thu\|Fri\|Sat) /, $file;

	# go through each execution
	foreach my $currSet (@dateList) {

	next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

	# grab the output of the 'date' command that was saved immediatley before io run started
	$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

	# and split/save each "line" from netstat
	$tempNETSTAT{$setDate} = [ split /\n(?=\d)/, $currSet ];
	} # done going through all explicit dates

	# get a list of dates, in chronological order
	@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempNETSTAT;

	# we'll go through each date from beginning to end (may want to filter this)
	for (my $i = 0; $i < $#dateList; ++$i) {

	# figure out the bounds for this netstat's run
	my $baseTS = &parsedate($dateList[$i]);
	my $endTS = &parsedate($dateList[$i+1]);

	# save the individual "lines" of this netstat
	my $fieldInfo = shift @{$tempNETSTAT{$dateList[$i]}}; # contains header information
	my @netstats = grep { /^\d/ } @{$tempNETSTAT{$dateList[$i]}};
	shift @netstats; # first "line" is sys average since system boot

	# we need to determine how much time was used between lines
	my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@netstats);

	@field = &parseFieldNames($fieldInfo);

	# go through each "line" of netstat's run
	for (my $j = 0; $j <= $#netstats; ++$j) {

	# figure which time this run represents
	my $currTS = $baseTS + $interval*$j;

	# split the "line" up each respective net statistic
	my @netStat = split /\n/, $netstats[$j];

	# go through each net statistics
	foreach my $currNS (@netStat) {

	# split each stat for this network statistic
	my @NS = split /\s+/, $currNS;

	# save the network's stat
	$ds->{$type}->{$currTS} = {
	map {
	$field[$_], $NS[$_]
	} (0 .. $#field)
	};
	}
	}
	}

	} elsif ($type eq "vmstat") {

	my %tempVMSTAT; # hash to hold split cpu stats by start date

	# list of vm stats split up per execution
	my @dateList = split /\n\n(Sun\|Mon\|Tue\|Wed\|Thu\|Fri\|Sat) /, $file;

	# go through each execution
	foreach my $currSet (@dateList) {

	next if $currSet =~ /^\w{3}$/; # the day of the week is all we have, skip it..

	# grab the output of the 'date' command that was saved immediatley before io run started
	$setDate = $1 if $currSet =~ /(\w+\s+\d+\s+\d\d:\d\d:\d\d\s+[A-Z]{3}\s+\d{4})/;

	# and split/save each "line" from vmstat
	$tempVMSTAT{$setDate} = [ split /\n\s+(?=\d)/, $currSet ];
	} # done going through all explicit dates

	# get a list of dates, in chronological order
	@dateList = map { $_->[0] } sort { $a->[1] <=> $b->[1] } map { [ $_, &parsedate($_) ] } keys %tempVMSTAT;

	# we'll go through each date from beginning to end (may want to filter this)
	for (my $i = 0; $i < $#dateList; ++$i) {

	# figure out the bounds for this vmstat's run
	my $baseTS = &parsedate($dateList[$i]);
	my $endTS = &parsedate($dateList[$i+1]);

	# save the individual "lines" of this vmstat
	my $fieldInfo = shift @{$tempVMSTAT{$dateList[$i]}}; # contains header information
	my @vmstats = map { (/^([^\n]+)/)[0] } @{$tempVMSTAT{$dateList[$i]}};
	shift @vmstats; # first "line" is sys average since system boot

	# we need to determine how much time was used between lines
	my $interval = sprintf "%d", ($endTS - $baseTS)/scalar(@vmstats);

	@field = &parseFieldNames($fieldInfo);

	# go through each "line" of vmstat's run
	for (my $j = 0; $j <= $#vmstats; ++$j) {

	# figure which time this run represents
	my $currTS = $baseTS + $interval*$j;

	# split the line up each set of statistic
	my @vmStat = split /\n/, $vmstats[$j];

	# go through each set of statistic
	foreach my $currVMS (@vmStat) {

	# split each stat for this set
	my @VMS = split /\s+/, $currVMS;

	# save the stat
	$ds->{$type}->{$currTS} = {
	map {
	$field[$_], $VMS[$_]
	} (0 .. $#field)
	};
	}
	}
	}
	}
	}

	sub parseFieldNames { my $fieldInfo = shift;

	my @fieldNames;

	my @fieldHeaderLines = split /\n/, $fieldInfo;

	if ($fieldHeaderLines[-2] =~ /\(Total\)/) {
	# these are netstat fields

	# input ce0 output input (Total) output
	# packets errs packets errs colls packets errs packets errs colls
	my $intName = "mainInt";
	$intName = $1 if $fieldHeaderLines[-2] =~ /^\s+input\s+(\S+)\s+output/;

	@fieldNames = ( "$intName-input-packets",
	"$intName-input-errs",
	"$intName-output-packets",
	"$intName-output-errs",
	"$intName-colls",
	"Total-input-packets",
	"Total-input-errs",
	"Total-output-packets",
	"Total-output-errs",
	"Total-colls"
	);
	} elsif ($fieldHeaderLines[-2] =~ /memory/) {
	# these are vmstat fields

	# kthr memory page disk faults cpu
	# r b w swap free re mf pi po fr de sr m0 m1 m2 m1 in sy cs us sy id

	my $diskNames;
	$diskNames = $1 if
	$fieldHeaderLines[-1] =~
	/\s+r\s+b\s+w\s+swap\s+free\s+re\s+mf\s+pi\s+po\s+fr\s+de\s+sr\s+(.+)\s+in\s+sy\s+cs\s+us\s+sy\s+id/;

	my @diskNames = map { "disk-$_" } (split /\s+/, $diskNames);

	@fieldNames = ( "kthr-r",
	"kthr-b",
	"kthr-w",
	"memory-swap",
	"memory-free",
	"page-re",
	"page-mf",
	"page-pi",
	"page-po",
	"page-fr",
	"page-de",
	"page-sr",
	@diskNames,
	"faults-in",
	"faults-sy",
	"faults-cs",
	"cpu-us",
	"cpu-sy",
	"cpu-id"
	);

	}

	return (@fieldNames);
	}

	sub generateReport { my $ds = shift;
	my ($timeStart,$timeEnd,$workBook,$workSheet,$tsArrayVM,$tsArrayNS,$tsArrayIO,$interval,$fmt);

	$/ = "\n";
	# do { print "Please enter date/time or timestamp of when you want reporting to begin: ";
	# $timeStart = <>; chomp $timeStart;
	# print "\n";
	# $timeStart = &parsedate($timeStart) if $timeStart !~ /^\d+$/;
	#
	#} while ($timeStart !~ /^\d+$/);

	#do { print "Please enter date/time or timestamp of when you want reporting to stop: ";
	# $timeEnd = <>; chomp $timeEnd;
	# print "\n";
	# $timeEnd = &parsedate($timeEnd) if $timeEnd !~ /^\d+$/;
	#} while ($timeEnd !~ /^\d+$/);

	($timeStart,$timeEnd) = (0,99999999999999999);

	# create a list of arrayrefs for each set of data, for the interest set of times/dates requested
	$tsArrayNS = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'netstat'}}) ];
	$tsArrayVM = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'vmstat'}}) ];
	$tsArrayIO = [ sort { $a <=> $b } grep { $_ >= $timeStart && $_ <= $timeEnd } (keys %{$ds->{'iostat'}}) ];

	$workBook = Spreadsheet::WriteExcel->new("perf-cpu.xls");

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet = $workBook->add_worksheet("Processor");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:F', 5);
	$workSheet->set_row(0, 100);

	$workSheet->write_row(
	0,
	0,
	[ "Time",
	"CPU Utilization",
	"CPU in System Mode",
	"CPU in User Mode",
	"Interrupt/sec",
	"Context Switch/sec"
	],
	$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayVM; ++$i ) {

	# $interval = $tsArrayVM->[$i+1]-$tsArrayVM if $tsArrayVM->[$i+1];

	$workSheet->write_row(
	$i+1,
	0,
	[ &formatDate($tsArrayVM->[$i]),
	(100 - $ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-id'})."%",
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-sy'}."%",
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'cpu-us'}."%",
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'faults-in'},
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'faults-cs'},
	],
	$fmt
	);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-mem.xls");

	$workSheet = $workBook->add_worksheet("Memory System");

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:D', 5);
	$workSheet->set_row(0, 100);

	$workSheet->write_row(
	0,
	0,
	[ "Time",
	"Page-in rate/sec",
	"Page-out rate/sec",
	"Paging rate/sec",
	],
	$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayVM; ++$i ) {

	# $interval = $tsArrayVM->[$i+1]-$tsArrayVM if $tsArrayVM->[$i+1];

	$workSheet->write_row(
	$i+1,
	0,
	[ &formatDate($tsArrayVM->[$i]),
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-pi'},
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-po'},
	$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-pi'}+$ds->{'vmstat'}->{$tsArrayVM->[$i]}->{'page-po'}

	],
	$fmt
	);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-io.xls");
	$workSheet = $workBook->add_worksheet("Disk IO");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:C', 10);
	$workSheet->set_row(0, 100);

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->write_row(
	0,
	0,
	[ "Time",
	"Disk Read rate/s",
	"Disk Write rate/s",
	],
	$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayIO; ++$i ) {

	# $interval = $tsArrayIO->[$i+1]-$tsArrayIO if $tsArrayIO->[$i+1];

	$workSheet->write_row(
	$i+1,
	0,
	[ &formatDate($tsArrayIO->[$i]),
	$ds->{'iostat'}->{$tsArrayIO->[$i]}->{'md0'}->{'kr/s'},
	$ds->{'iostat'}->{$tsArrayIO->[$i]}->{'md0'}->{'kw/s'},

	],
	$fmt
	);
	}

	$workBook->close();
	$workBook = Spreadsheet::WriteExcel->new("perf-network.xls");
	$workSheet = $workBook->add_worksheet("Network");

	$workSheet->set_column('A:A', 16);
	$workSheet->set_column('B:F', 8);
	$workSheet->set_row(0, 100);

	$fmt = $workBook->add_format();
	$fmt->set_rotation(60); $fmt->set_align('center'); $fmt->set_align('bottom'); $fmt->set_border(1);

	$workSheet->write_row(
	0,
	0,
	[ "Time",
	"Collision rate/min",
	"Incoming pkt error/min",
	"Incoming pkt rate/min",
	"Outgoing pkg error/min",
	"Outgoing pkg rate/min"
	],
	$fmt
	);
	$fmt = $workBook->add_format(); $fmt->set_align('center');
	for (my $i = 0; $i < $#$tsArrayNS; ++$i ) {

	$interval = $tsArrayNS->[$i+1]-$tsArrayNS->[$i] if $tsArrayNS->[$i+1];

	$workSheet->write_row(
	$i+1,
	0,
	[ &formatDate($tsArrayNS->[$i]),
	int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-colls'}/$interval),
	int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-input-errs'}/$interval),
	int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-input-packets'}/$interval),
	int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-output-errs'}/$interval),
	int(60*$ds->{'netstat'}->{$tsArrayNS->[$i]}->{'Total-output-packets'}/$interval),
	],
	$fmt
	);
	}
	$workBook->close();

	}

	sub formatDate {
	my $timeStamp = shift \|\| return;
	my @tempTime = localtime($timeStamp); # format of the moth names

	# return a nice string with the appropriately formatted date
	return sprintf "%02d/%02d/%02d %02d:%02d:%02d",
	$tempTime[4]+1, $tempTime[3], $tempTime[5]-100, $tempTime[2], $tempTime[1], $tempTime[0];
	}
	#!/usr/bin/perl
	$\| = 1;

	use strict; no strict 'refs'; # discipline is good
	use Time::ParseDate;
	use Spreadsheet::WriteExcel;
	use Spreadsheet::WriteExcel::Utility;

	die "Need exactly one argument: file with list of hosts to query!" unless $#ARGV == 0;

	my %sar; # will contain sar info on a per host basis
	my %mem; # will contain amount of system memory on a per host basis
	my %date; # will contain list of valid dates to report fields for
	my @host; # read in from a file, list of hosts to log into
	my %hostRow; # will hold what row the host ended up being in
	my $sarThreshold = 0; # need at least this many hosts with the date to include in the final report
	my ($sarTimeFrom,$sarTimeTo) = qw/0 23/; # what times of day to look at values (in 24-hr time)
	my ($sarDayFrom,$sarDayTo) = qw/0 6/; # what days of the week to look at values (0 - Sun, 6 - Sat)
	my ($row,$col);
	my @dataType = qw/CPU-Avg MEM-Avg/;

	my @prog = qw/x \| \/ - \\/; # progress indicator characters
	my $prog = 0; # progress indicator counter

	my @timeList = localtime(time);
	my $nowYear = $timeList[5]+1900;# the ls output needs to be corrected for the appropriate
	my $nowMon = $timeList[4]; #+year, and these values help figure out what the year is

	my @monthName = qw/Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec/; # used to populate the hash

	# hash of month name to numeric translations (so i can avoid using the 3rd party Time::ParseDate module
	my %monthName = map { $monthName[$_], $_ } (0 .. $#monthName);

	# will be used as part of the filename
	my $filename = "weekly-".&formatDate($monthName[$nowMon]." ".$timeList[3]." ".$nowYear).".xls";

	my $workBook = Spreadsheet::WriteExcel->new($filename);

	my $fmtPercentage = $workBook->add_format(); $fmtPercentage->set_num_format('0.0%');
	my $fmtPercentage2 = $workBook->add_format(); $fmtPercentage2->set_num_format('0.0%'); $fmtPercentage2->set_fg_color('yellow');
	my $fmtRotated = $workBook->add_format(); $fmtRotated->set_num_format('dd-mm'); $fmtRotated->set_rotation(90); $fmtRotated->set_align('right'); $fmtRotated->set_align('bottom');

	undef $/; # we'll use an undefined field seperator to slurp the entire file into a scalar
	# here we'll read in the file and put it into a array
	open HOSTLIST, $ARGV[0] \|\| die "Cannot open $ARGV[0]: $!\n"; {
	my $hostList = <HOSTLIST>; # defined in 'local' context, doesn't exist afterwards
	@host = split /\n/, $hostList; } #+which is my sorry way of saving memory, lol
	close HOSTLIST;

	print "\nNow logging into hosts & retrieving information.. ";
	foreach my $currHost (grep { !/^=/ } @host) {
	++$prog; print "\x08"; print $prog[$prog%$#prog+1]; # progress indicator

	# log into each host, run
	# 1.) prtconf
	# a.) grep for memroy
	# 3.) bash with sh-style for loop to run on all four-letter files in /var/adm/sa/ that begin with "sa"
	# a.) run an ls -ld on them (to get the timestamp)
	# b.) run sar -u to get cpu info from that file
	# c.) run sar -r to get mem info from that file
	# my $sshOutSAR = `ssh $currHost "/etc/swap -s; /usr/sbin/prtconf\|grep Memory;bash -c 'for i in /var/adm/sa/sa??; do ls -ld \\\$i; /usr/bin/sar -u -f \\\$i; /usr/bin/sar -r -f \\\$i; done'" 2>/dev/null`;

	my $sshOutSAR = `ssh $currHost "/usr/sbin/prtconf\|grep Memory;bash -c 'for i in /var/adm/sa/sa??; do ls -ld \\\$i; /usr/bin/sar -u -f \\\$i; /usr/bin/sar -r -f \\\$i; done'" 2>/dev/null`;
	# THIS SECTION WORKS ON THE SAR OUTPUT
	next unless $sshOutSAR =~ /Average/; # there is nothing to do with this host if no sar (no average reported)

	$mem{$currHost} = $1 if $sshOutSAR =~ /Memory\s+size:\s+(\d+)\s+M/; # save amount of memory on system
	# $sshOutSAR =~ s/^[^\n]+\n//; #+and remove from input

	# for each log file entry up (which consists of the ls, the sar -u and the sar -r)
	foreach my $currLog (split /\n-rw/, $sshOutSAR) {
	next unless $currLog =~ /Average/; # this is an empty log file if no Average string appears

	# break up current log commands between the ls, the sar -u, and the sar -r
	my ($logFile,$sarCPU,$sarMEM) = split(/\nSunOS/,$currLog);
	next unless $logFile; # if we have no ls, then we have no log file

	# retreiving the timestamp on the log file (from the ls)
	$logFile =~ /\d+\s(\S+\s+\d+)\s+(\S+)\s\/var\/adm/;
	my ($currDay,$currYear) = ($1,$2); # saving the Month N,
	if ($currYear =~ /\d+:\d+/) { # check if the ls command included a time instead of a year, this
	$currYear = $nowYear; #+means it was less than 6 months ago, so we correct it:

	# but if today's month is less than a half year, then any months from the latter twelve
	#+months of a year, obvious refer to the last year, here we're correcting for that too
	--$currYear if $nowMon < 6 && ($monthName{($currDay =~ /^(\S+)\s/)[0]}) > 5;
	}
	# we are interested in only running this on certain days.. here we get the day of the week for this date
	my $dayOfWeek = (localtime(&parsedate($currDay." ".$currYear)))[6];
	next if $dayOfWeek < $sarDayFrom \|\| $dayOfWeek > $sarDayTo; #+ and skip if it isn't in our range

	my ( # since the utilization reports are the amount unused, we start with large values
	$sarCPUpeak,$sarMEMpeak,$sarSWAPpeak,
	$sarCPUavg,$sarMEMavg,$sarSWAPavg,
	$avgCounter,$avgSum
	) = ( 999,99999999,99999999999,0,0,0,0,0 );

	# fetch the average idle cpu time
	$avgCounter = 0; $avgSum = 0;
	foreach my $currSARcpu (split /\n/, $sarCPU) { # look for the smallest idle
	next unless $currSARcpu =~ /^(\d+):\d+:\d+\s+\d+\s+\d+\s+\d+\s+(\d+)/;
	next unless $1 >= $sarTimeFrom && $1 <= $sarTimeTo; # only interested in certain times of day

	++$avgCounter; $avgSum += $2;
	$sarCPUpeak = $2 if $2 < $sarCPUpeak;
	}
	$sarCPUavg = ($avgSum/$avgCounter) unless $avgCounter == 0; # calculate average

	# fetch the average free memory
	$avgCounter = 0; $avgSum = 0;
	foreach my $currSARmem (split /\n/, $sarMEM) { # look for the smallest free-mem
	next unless $currSARmem =~ /^(\d+):\d+:\d+\s+(\d+)\s+/;
	next unless $1 >= $sarTimeFrom && $1 <= $sarTimeTo; # only interested in certain times of day

	++$avgCounter; $avgSum += $2;
	$sarMEMpeak = $2 if $2 < $sarMEMpeak;
	}
	$sarMEMavg = ($avgSum/$avgCounter) unless $avgCounter == 0; # calculate average

	++$date{$currDay. " ". $currYear}; # retain this date for when we print out the column headers

	# save the avg/peak cpu/mem values based on date, in this elaborate data structure
	$sar{$currHost}->{$currDay. " ". $currYear} = {
	'CPU' => {
	'Avg' => $sarCPUavg,
	},
	'MEM' => {
	'Avg' => $sarMEMavg,
	}
	};

	} # done going through each log file

	} # done going through all hosts
	#print "\x08 \n\nCut and Paste this into Excel\n";
	print "\x08Done\n\nNow creating Excel speadsheet (\"$filename\")... ";

	# debugging purposes only
	#use Data::Dumper;
	#print &Dumper(\@host);
	#print &Dumper(\%sar);
	#print &Dumper(\%fs);

	# get a list of times, in chronological order
	@timeList = reverse ( (
	map {
	$_->[0]
	} sort {
	$b->[1] <=> $a->[1] \|\| $b->[2] <=> $a->[2] \|\| $b->[3] <=> $a->[3] # reverse chron order
	} map { # Scwartzian transform sort technique
	[ $_, ($_ =~ /(\d{4}$)/)[0], $monthName{($_ =~ /^(\w+)\s/)[0]}, ($_ =~ /(\d+)\s\d{4}/)[0] ]
	} grep {
	$date{$_} > $sarThreshold
	} keys %date
	)[0..6]
	);

	# go through each stat (currently only CPU and MEM)
	foreach my $currStatFull (@dataType) {
	my ($currStat,$currType) = split /-/, $currStatFull;

	# will be populated with location of the hosts
	%hostRow = ( );

	# create the work sheet, set some widths for the rows
	my $workSheet = $workBook->add_worksheet($currStat);
	$workSheet->set_column('A:A',16);
	$workSheet->set_column('B:H', undef, $fmtPercentage);
	$workSheet->set_column('I:I',128);
	$workSheet->set_row('A:A', 72, undef);

	($row,$col) = (0,1);

	# draw out the timestamps
	map { $workSheet->write($row,$col++, &formatDate2($_), $fmtRotated) } @timeList;

	# go through all entries, including hosts as well as groups
	foreach my $currHost (@host) {
	# if we're not a group, and we have no stats, what's to print? let's skip past this one
	next if $currHost !~ /^=/ && $sar{$currHost}->{$timeList[0]}->{$currStat}->{$currType} eq "";

	# if this is a group..
	if ($currHost =~ /^=/) {
	my $group = $currHost; $group =~ s/^=\s*//;
	my @groupHost = grep { $sar{$_}->{$timeList[0]}->{$currStat}->{$currType} } map { s/\s+//; $_ } split (/\s+/, $group);
	next unless scalar(@groupHost);
	} # we want to make sure that its members has data as well

	# if we got this far, we deserve to advance a row, and save the host's row location
	++$row; $col = 0; $hostRow{$currHost} = $row;

	# if this is not a group (it's a host)
	if ($currHost !~ /^=/) {
	$workSheet->write($row,$col++,$currHost);
	map {
	$workSheet->write($row,$col++,&{"format".$currStat}($sar{$currHost}->{$_}->{$currStat}->{$currType}, $mem{$currHost}))
	} @timeList;
	}
	} # done going through all possible hosts and groups

	# go through only the groups
	foreach my $currGroup (grep { /^=/ } @host) {
	# get the previously saved locations
	($row,$col) = ($hostRow{$currGroup},0);

	# don't ask me why, but changing $currGroup seems to reflect the change back in @hosts.. is this a bug or what?
	my $group = $currGroup; $group =~ s/^=\s*//;

	# get a list of hosts, but only ones with data in them
	my @groupHost = grep { $sar{$_}->{$timeList[0]}->{$currStat}->{$currType} } map { s/\s+//; $_ } split (/\s+/, $group);
	next unless scalar(@groupHost); # if there's no hosts found with data, we don't want to print this line

	# go through each date available and print out the formula to calculate the average for all the hosts
	map { ++$col;
	$workSheet->write(
	$row,
	$col,
	( scalar(@groupHost) ? "=AVERAGE(".join(",",map { &xl_rowcol_to_cell($hostRow{$_},$col) } @groupHost).")" : "" ),
	$fmtPercentage2
	);
	} @timeList;

	# record the hosts that were used in this line's calculations
	$workSheet->write($row,++$col,join(",",@groupHost));
	}
	}

	print "Done!\n";

	# take the Mon N Year, and conver it to the YYYYMMDD format
	sub formatDate { local($_) = $_[0];
	my ($mon,$day,$yr) = split /\s+/;
	sprintf "%04d%02d%02d", $yr, $monthName{$mon}+1, $day;
	}

	# take the Mon N Year format and convert it to the MM-DD-YYYY format that is compatible with excel
	sub formatDate2 { local($_) = $_[0];
	my ($mon,$day,$yr) = split /\s+/;
	sprintf "$day-$mon";
	}

	# take the idle time, and figure out the inverse, create a percentage val
	sub formatCPU { local($_) = $_[0]; sprintf "%.5f", ((100-$_)/100); }

	# take the free memory, convert it to the same unit as the free memory amount, and create a percentage val
	sub formatMEM { my ($freeMem,$totalMem) = @_; sprintf "%.5f", (1-($freeMem * 8192 / 1024 / 1024)/$totalMem); }