Jan et al-
Jan, I have this to you as well as the list because I have noticed your
name on a LARGE number of sites talking about WIN32::OLE and the packages
within it.
I keep getting errors saying that no matter WHAT i have, range (xy) or
Cells (y,x) that Range and Cells errors out as not being a function.
Could you explain to me what is wrong with my headers and or script? I
need to gather information on a large number of systems on a weekly basis
and populate an Excel spread sheet and this would help GREATLY if i could
get it working. when I made something quick and dirty using perl to create
a CSV that was able to give me what i needed to tweak the sorting and
pulling information out, so if you need to get something else to show me
the errors and explain that is ok.
let me know if you would like me to find a host to link psinfo onto. I
believe it is freeware
Thank you in advance,
-Josh
-----code follows-----
#! /usr/bin/perl
use strict;
use warnings;
use Getopt::Long; # used in getting unix-style options
use Win32::OLE qw(in with); # use base OLE
use Win32::OLE::Const 'Microsoft Excel'; # use OLE/Excel
$Win32::OLE::Warn = 3; # die on errors...
use Win32::OLE::Enum;
use Win32::OLE::Variant;
################################################################################
# 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/09/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.4"; $revdat="03/09/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); }
# set up OLE interaction
# 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";
# 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'};
}
#####
# for each node we need to get the information
# and parse it into the excel file
####
foreach my $node (@comps){
# a sub routine defined later in the script is used for simplicity
&populate($node,$row,$ws);
# 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 -> Range("A1") -> ('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
# a sub routine defined later in the script is used for simplicity
&populate($node,$row,$ws);
# 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
sub populate{
# this is a subroutine to populate cells in a microsoft excel
spreadsheet
# this particular one is made for the compinfo.pl script/executable but
# shuold be easily modified for other scripts
# this routine does ONE row at a time
my $node=$_[0]; # node who's info sheet needs to be picked apart
my $row=$_[1]; # row to add info too
my $ws=$_[2]; # worksheet link
my $err=0; # error marker
# this expects the following row set up:
# A -> Node; B -> NAV; C -> Alarms; D -> SNMP; E -> Uptime;
# F -> Kernel Version; G -> Product Type; H -> Product Version;
# I -> Service Pack; J -> Kernel Build Number; K -> Registered
Organization;
# L -> Registered Owner; M -> Install Date; N -> Activation Status;
# O -> IE Version; P -> System Root; Q -> Processors; R -> Processor
Speed;
# S -> Processor Type; T -> Physical Memory; U -> Installed OS Hotfixes;
# V -> Other Applications;
# set the first cell since that's always going to be the same
$ws -> Cells($row, "A") -> ('Value') = "$node";
my $cmd = "psinfo -h -s \\\\$node";
my @examine = split "\n", `$cmd`;
# 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 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__
-----------------------------------------
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
