Active State Perl 5.8.x, OLE, and Excel not creating files

**m3rajk** · Mar 9 '06, 05:24 PM

and the code for the script/program is

Code:

#! /usr/bin/perl
use strict;
use warnings;
use Getopt::Long; # used in getting unix-style options
use Win32::OLE;
use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
$Win32::OLE::Warn = 3; # die on errors...

################################################################################
# Purpose:
#    This script catalogues the psinfo information on systems, including patches
# and software. This is sent to an excel spread sheet for easy comparison. This
# is done to keep on top of what software/patches do not have negative impacts
# upon SeaChange(tm) systems
#
# This should be kept as both a script and an executable. The executable should
# be made by the last person to revise the script. The Active State Perl PDK has
# been used.
#
# Created: 03/02/2006
# Original Author: Josh Perlmutter
# Maintainers:
# Last Revised: 03/07/2006
# Copyright SeaChange International (tm)
################################################################################

my(@comps);
my($comp, $date);
# standard options/revision information variables
# verbose line is 79 chars long w/2 \t
# base to use to add options:        -<option>\t\t<use>
my($help, $usage, $verb, $qrev, $rev, $revdat, $modules, $log, $err);
$modules="\tnone as of now\n\n"; $help=0; $qrev=0; $err=0;
$rev="0.0.3"; $revdat="03/08/2006"; $verb=0; $log='';
$usage = <<EOD;
\tFile: $0\tVersion: $rev\tReleased: $revdat\n
This is the $0 file.\n
The options for this file are:
        -help           This help screen only (overrides all other options)
        -log            File to log to (appends if the file exists)
        -version        List Version and exit (overrides everything but -help)
        -verbose        Turn on verbose (useful for debugging errors; call twice
                         for extra output)

        usage ([] denotes optional argument {} denotes default value
               {} denotes option set of which your choice of one is required):
$0 [-help] [-version] [-verbose]
to debug try: $0 -verb -verb > debug_log.txt\n
EOD

# short explanation of variables:


######
# this section deals with options. it looks for unix-style options passed. that
# is, <script> -option [value]
# now get the options 
GetOptions ('help'=>\$help, 'verbose+'=>\$verb, 'version'=>\$qrev,
	    'log:s'=>\$log, 'computers:s' => \$comp);


###########################
# arguments ARE required
unless($help || $qrev ||
       ($log)){
  my $necessary = <<EOD;
        You must supply at least 1 parameter(s) to run this program. It needs:
-log     log file to log actions to (appends if existing)
Turning on help
EOD
  print $necessary;
  $help++;
}

#
# now put the options to use...
#

# show usage and exit if asked for
die $usage if $help;

# report version and exit
die "\tFile: $0\tVersion: $rev\tReleased: $revdat\n" if $qrev;

####################################################
## this is a standardized reporting subroutine #####
## it takes a line and a flag for verbose and  #####
## reports to a universal file handle (and screen) #
####################################################
sub rep{
  # this assumes two passed values,
  # first a message
  # second a flag for verbose (screen printing)
  my $time=localtime().' | ';
  print LOG "$time $_[0]\n";
  if($_[1]){ print STDOUT "$time $_[0]\n"; }
}

sub getinfo(){
  #############################################################################
  ## this grabs the system information for reporting at the begining of logs ##
  #############################################################################

  my $nname=`hostname`; chomp($nname);
  my $nos=`ver`; $nos =~ s/\W*(\w.*\])\s.*/$1/;
  my $nip=`ipconfig`; $nip =~ s/\D+([\d\.]{7,15})[\D\d]*/$1/;

  my $info= <<EOD;
Program Information:
Script: $0
Last Updated: $revdat          Version $rev

System Information:
       Node Name: $nname
       Node OS: $nos
       Node IP: $nip

EOD
  return $info;
}

sub dbg{
  # this is a debugging subroutine that prints things to screen if the
  # verbosity level is high enough
  my $item=$_[1];
  my $dbgpt=$_[0];

  while(1){
    print "Debug Point: $dbgpt\nItem value:\n$item
\n\t\tShall I continue? (y or n)\n\n";
    if(<STDIN> =~ m/^n$/i){ die "\t\t\tyou chose to leave at $dbgpt\n\n"; }
    elsif(<STDIN> =~ m/^y$/i){ print "Continuing to next point\n\n"; last; }
    else{ print "response not understood\n"; }
  }
}

# start the log
open LOG, ">>$log" or die "cannot open log file";
&rep(&getinfo,$verb);
############################ Below is non-templeted ############################
# make the computer list an array
@comps=split ',', $comp;

# get the date
my @rdate=split ' ', localtime();
$date=$rdate[4].'-'.$rdate[1].'-'.$rdate[2];
if($verb>1){ &rep("$date\n",$verb); }

foreach $comp (@comps){
  # for each computer in the array
  # get the following information placed into a file
  # titled by the computer name and date
  # or tell use why there is an error and exit

  my $err=0; # error indicator

  # this should be 0 on error and 1 on success
  my $cmd="psinfo -h -s \\\\$comp > $comp-$date.txt";
  my $res=system "$cmd";

  if($res){
    my $err_rep="\t\tinformation gathering error!";
    $err_rep.=" $res Information may not be complete.
\t\tcommand: $cmd\n$!\n$^E\n\n";
    &rep($err_rep,$verb);
  }else{
    &rep("Cataloged $comp to $comp-$date.txt\n",$verb);
  }

}

#######
## OLE interaction upon the saved files follows
#######

# get an active Excel or create a new one
my $Excel = Win32::OLE -> GetActiveObject('Excel.Application')
  || Win32::OLE -> new('Excel.Application', 'Quit');

my $labrep=`cd`;
chomp($labrep);
if (substr($labrep, -1) ne '\\'){ $labrep.='/'; }
$labrep.="Lab_Report_$date.xls";

continued...

**m3rajk** · Mar 9 '06, 05:25 PM

Code:

# does the file exit?
if(-e "$labrep"){
  #we are just adding to it, so open it
  my $report = $Excel->Workbooks->Open("$labrep");
  my $ws = $report -> Worksheets(1);

  my $ldate = localtime();
  &rep("Examining information gathered from lab computers @ $ldate\n", $verb);

  # start row counter
  my $row=2;
  # set row counter to continue at the end of what's there
  my $ncell = $ws -> Range("A$row") -> {'Value'};
  while($ncell =~ m/\w+/i){
    $row++; # increment row & V- grab next row's data -V
    $ncell = $ws -> Range("A$row") -> {'Value'};
  }

  foreach my $node (@comps){
    # for each node we check the information returned
    # if there is no information we only have two items to place on the row,
    # otherwise we have A-V items

    # set the first cell since that's always going to be the same
    $ws -> Cells($row, "A") -> ('Value') = "$node";

    # try to open the file with the information
    open LNINFO, "<$comp-$date.txt" or $err=1;

    if($err){
      # there was an error on the file, so note that in the report
      $ws -> Cells($row, "B") -> ('Value') =
	"$comp-$date.txt could not be opened to be put into the lab report. $! $^E\n";

    }else{
      # there was no error, so now we check the file,
      # first reading in the file
      my @examine;
      while(<LNINFO>){
	push @examine, $_;
      }

      # then noting which couldnt be connected to
      if($examine[6] =~ m/The network path was not found./i ){
	$ws -> Cells($row, "B") -> ('Value') = "The network path was not found.";

      }else{
	# we need to run through the file for the information to put into the rows

	# drop what's before what we need
	while (!($examine[0] =~ m/Uptime.*/i)){ shift(@examine); }

	while (!($examine[0] =~ m/OS Hot Fix.*/i)){
	  # now go through a series of if/elsif sections for the rest
	  # hot fixes and programs will be handled slightly differently though

	  if($examine[0] =~ m/Uptime:\s+(\w.*)/i){
	    $ws -> Cells($row, "E") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Kernel version:\s+(\w.*)/i){
	    $ws -> Cells($row, "F") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Product type:\s+(\w.*)/i){
	    $ws -> Cells($row, "G") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Product version:\s+(\w.*)/i){
	    $ws -> Cells($row, "H") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Service pack:\s+(\w.*)/i){
	    $ws -> Cells($row, "I") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Kernel build number:\s+(\w.*)/i){
	    $ws -> Cells($row, "J") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Registered organization:\s+(\w.*)/i){
	    $ws -> Cells($row, "K") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Registered owner:\s+(\w.*)/i){
	    $ws -> Cells($row, "L") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Install date:\s+(\w.*)/i){
	    $ws -> Cells($row, "M") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Activation status:\s+(\w.*)/i){
	    $ws -> Cells($row, "N") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/IE version:\s+(\w.*)/i){
	    $ws -> Cells($row, "O") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/System root:\s+(\w.*)/i){
	    $ws -> Cells($row, "P") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processors:\s+(\w.*)/i){
	    $ws -> Cells($row, "Q") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processor speed:\s+(\w.*)/i){
	    $ws -> Cells($row, "R") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processor type:\s+(\w.*)/i){
	    $ws -> Cells($row, "S") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Physical memory:\s+(\w.*)/i){
	    $ws -> Cells($row, "T") -> ('Value') = "$1";
	  }
	}

	#  remove the line starting "OS Hot Fix..."
	shift(@examine);
	# create a variable for holding the hotfixes
	my $hotfixes="\"";

	while (!($examine[0] =~ m/Applications.*/i)){
	  # while hot fixes need to be added. skip blank lines
	  if($examine[0] =~ m/\w/){
	    chomp($examine[0]);
	    $hotfixes .="$examine[0]\n";
	  }
	}

	# add the hotfixes
	$ws -> Cells($row, "U") -> ('Value') = "$hotfixes";

	#  remove the line starting "Applications..."
	shift(@examine);

	my $apps='';
	foreach my $app (@examine){
	  # the rest should all be applications to add
	  # so we're using a foreach to put them in
	  # only add lines that are not blank
	  # there are 3 special lines: NAV, Alarms, and SNMP
	  if($app =~ m/Symantec AntiVirus (\w+)/i){
	    $ws -> Cells($row, "B") -> ('Value') = "ver: $1";
	  }elsif($app =~ m/Alarm/i){
	    $ws -> Cells($row, "C") -> ('Value') = "Installed";
	  }elsif($app =~ m/SNMP Informant Agent \(([^\)]+)\) (\w.*)/i){
	    $ws -> Cells($row, "D") -> ('Value') = "$1 : $2";
	  }elsif($app =~ m/\s+(\w.*)/i){
	    $apps .= "$1\n";
	  }
	}

	# add the other applications
	$ws -> Cells($row, "V") -> ('Value') = "$apps";
      }
    }

    # end the loop increasing the row number
    $row++;
  }
}else{

continued...

**m3rajk** · Mar 9 '06, 05:25 PM

Code:

  # we have to create it, including make the first row
  $Excel -> {'Visible'} = 1;
  $Excel -> { 'SheetsInNewWorkBook' } = 1;
  my $workbook = $Excel -> Workbooks -> Add();
  my $ws = $workbook -> Worksheets(1);
  $ws -> { 'Name' } = "Lab Report $date";

  # set first row titles
  $ws -> Cells(1, "A") -> ('Value') = "Node";
  $ws -> Cells(1, "B") -> ('Value') = "NAV";
  $ws -> Cells(1, "C") -> ('Value') = "Alarms";
  $ws -> Cells(1, "D") -> ('Value') = "SNMP";
  $ws -> Cells(1, "E") -> ('Value') = "Uptime";
  $ws -> Cells(1, "F") -> ('Value') = "Kernel Version";
  $ws -> Cells(1, "G") -> ('Value') = "Product Type";
  $ws -> Cells(1, "H") -> ('Value') = "Product Version";
  $ws -> Cells(1, "I") -> ('Value') = "Service Pack";
  $ws -> Cells(1, "J") -> ('Value') = "Kernel Build Number";
  $ws -> Cells(1, "K") -> ('Value') = "Registered Organization";
  $ws -> Cells(1, "L") -> ('Value') = "Registered Owner";
  $ws -> Cells(1, "M") -> ('Value') = "Install Date";
  $ws -> Cells(1, "N") -> ('Value') = "Activation Status";
  $ws -> Cells(1, "O") -> ('Value') = "IE Version";
  $ws -> Cells(1, "P") -> ('Value') = "System Root";
  $ws -> Cells(1, "Q") -> ('Value') = "Processors";
  $ws -> Cells(1, "R") -> ('Value') = "Processor Speed";
  $ws -> Cells(1, "S") -> ('Value') = "Processor Type";
  $ws -> Cells(1, "T") -> ('Value') = "Physical Memory";
  $ws -> Cells(1, "U") -> ('Value') = "Installed OS Hotfixes";
  $ws -> Cells(1, "V") -> ('Value') = "Other Applications";

  my $ldate = localtime();
  &rep("Examining information gathered from lab computers @ $ldate\n", $verb);

  # start row counter
  my $row=2;

  foreach my $node (@comps){
    # for each node we check the information returned
    # if there is no information we only have two items to place on the row,
    # otherwise we have A-V items

    # set the first cell since that's always going to be the same
    $ws -> Cells($row, "A") -> ('Value') = "$node";

    # try to open the file with the information
    open LNINFO, "<$comp-$date.txt" or $err=1;

    if($err){
      # there was an error on the file, so note that in the report
      $ws -> Cells($row, "B") -> ('Value') =
	"$comp-$date.txt could not be opened to be put into the lab report. $! $^E\n";

    }else{
      # there was no error, so now we check the file,
      # first reading in the file
      my @examine;
      while(<LNINFO>){
	push @examine, $_;
      }

      # then noting which couldnt be connected to
      if($examine[6] =~ m/The network path was not found./i ){
	$ws -> Cells($row, "B") -> ('Value') = "The network path was not found.";

      }else{
	# we need to run through the file for the information to put into the rows

	# drop what's before what we need
	while (!($examine[0] =~ m/Uptime.*/i)){ shift(@examine); }

	while (!($examine[0] =~ m/OS Hot Fix.*/i)){
	  # now go through a series of if/elsif sections for the rest
	  # hot fixes and programs will be handled slightly differently though

	  if($examine[0] =~ m/Uptime:\s+(\w.*)/i){
	    $ws -> Cells($row, "E") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Kernel version:\s+(\w.*)/i){
	    $ws -> Cells($row, "F") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Product type:\s+(\w.*)/i){
	    $ws -> Cells($row, "G") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Product version:\s+(\w.*)/i){
	    $ws -> Cells($row, "H") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Service pack:\s+(\w.*)/i){
	    $ws -> Cells($row, "I") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Kernel build number:\s+(\w.*)/i){
	    $ws -> Cells($row, "J") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Registered organization:\s+(\w.*)/i){
	    $ws -> Cells($row, "K") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Registered owner:\s+(\w.*)/i){
	    $ws -> Cells($row, "L") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Install date:\s+(\w.*)/i){
	    $ws -> Cells($row, "M") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Activation status:\s+(\w.*)/i){
	    $ws -> Cells($row, "N") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/IE version:\s+(\w.*)/i){
	    $ws -> Cells($row, "O") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/System root:\s+(\w.*)/i){
	    $ws -> Cells($row, "P") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processors:\s+(\w.*)/i){
	    $ws -> Cells($row, "Q") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processor speed:\s+(\w.*)/i){
	    $ws -> Cells($row, "R") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Processor type:\s+(\w.*)/i){
	    $ws -> Cells($row, "S") -> ('Value') = "$1";
	  }elsif($examine[0] =~ m/Physical memory:\s+(\w.*)/i){
	    $ws -> Cells($row, "T") -> ('Value') = "$1";
	  }
	}

	#  remove the line starting "OS Hot Fix..."
	shift(@examine);
	# create a variable for holding the hotfixes
	my $hotfixes="\"";

	while (!($examine[0] =~ m/Applications.*/i)){
	  # while hot fixes need to be added. skip blank lines
	  if($examine[0] =~ m/\w/){
	    chomp($examine[0]);
	    $hotfixes .="$examine[0]\n";
	  }
	}

	# add the hotfixes
	$ws -> Cells($row, "U") -> ('Value') = "$hotfixes";

	#  remove the line starting "Applications..."
	shift(@examine);

	my $apps='';
	foreach my $app (@examine){
	  # the rest should all be applications to add
	  # so we're using a foreach to put them in
	  # only add lines that are not blank
	  # there are 3 special lines: NAV, Alarms, and SNMP
	  if($app =~ m/Symantec AntiVirus (\w+)/i){
	    $ws -> Cells($row, "B") -> ('Value') = "ver: $1";
	  }elsif($app =~ m/Alarm/i){
	    $ws -> Cells($row, "C") -> ('Value') = "Installed";
	  }elsif($app =~ m/SNMP Informant Agent \(([^\)]+)\) (\w.*)/i){
	    $ws -> Cells($row, "D") -> ('Value') = "$1 : $2";
	  }elsif($app =~ m/\s+(\w.*)/i){
	    $apps .= "$1\n";
	  }
	}

	# add the other applications
	$ws -> Cells($row, "V") -> ('Value') = "$apps";
      }
    }

    # end the loop increasing the row number
    $row++;
  }

  $workbook -> SaveAs($labrep); # save active sheet
}

# save and exit
$Excel -> Workbooks -> Save(); # save file
$Excel -> Workbooks -> Quit(); # leave excel
my $et=locatime();
&rep("program completed at $et.",$verb); # wrap up log
close LOG; # close log
################################################################################
__END__

Active State Perl 5.8.x, OLE, and Excel not creating files

Active State Perl 5.8.x, OLE, and Excel not creating files

Comment

Comment

Comment