I have two issues with the following script:
1: it wont create a file when there is none
2: i don't know how to jump to the final row when the file exists so as to
not overwrite information
i spent several hours yesterday scouring google on variations of "perl ole
excel" with other modifiers to try to get the answers and have not found
them.
thank you in advance for your help.
-josh
#! /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 and reports informationon lab 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="./Lab_Report_$date.xls";
# 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;###################<----------------------------bad!!!!!!!!!!!
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{
# 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__
# # line used to help keep lines under 80 characters
-----------------------------------------
PLEASE NOTE:
SeaChange International headquarters in Maynard, MA is moving!
Effective March 1, 2006, our new headquarters address will be:
SeaChange International
50 Nagog Park
Acton, MA 01720 USA
All telephone numbers remain the same:
Main Corporate Telephone: 978-897-0100
Customer Service Telephone: 978-897-7300
_______________________________________________
ActivePerl mailing list
[email protected]
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
