irishmanjb
asked on
Help with adjusting perl script
Originally Kavar wrote this script for me and it worked great up until the data format was changed slightly.
This script parses an ftplog and imports the applicable data into log.mbd.
This is the current data format
10/10/04-12:15:15 PM - C:\InetPub\FTPRoot\302812\ 302812TLOG 10102004.z ip 47037 Copied
When I launch the script the box opens and closes nothing happens this is because the filename does not include the time.
This is the old format 1359 represents the time.
4/15/04-2:49:00 PM - c:\Inetpub\FTPRoot\335009\ 335009TLOG 0414200413 59.zip 78719 Copied
I need to do is adjust this script so I am not parsing the time from the filename and importing it.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x 5cFTPRoot\ x5c';
$reLogdate='\d{1,2}\x2f\d{ 1,2}\x2f\d {2}';
$reLogtime='\d{1,2}:\d{1,2 }:\d{2}\s[ PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV ER={Micros oft Access Driver (*.mdb)};DBQ=$dbFilename;" );
$adoCommand->{ActiveConnec tion} = $adoConnection;
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($reLogdate)-($reLogtime) \s-\s$reFt pdir($reSe rvernumber )\x5c($reF ilename)\s ($reFilesi ze).+/ )
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if (/($Servernumber)(TLOG)(\d {2})(\d{2} )(\d{4})(\ d{2})(\d{2 })(\.zip)/ )
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6;
$FileMin=$7;
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Fil eHour,File Min) Values (#$Logdate#,#$Logtime#,\'$ Servernumb er\',\'$Fi lename\',$ Filesize,$ FileDay,$F ileMonth,$ FileYear,$ FileHour,$ FileMin)";
print "$databasestring\n";
$adoCommand->{CommandText} =$database string;
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
This script parses an ftplog and imports the applicable data into log.mbd.
This is the current data format
10/10/04-12:15:15 PM - C:\InetPub\FTPRoot\302812\
When I launch the script the box opens and closes nothing happens this is because the filename does not include the time.
This is the old format 1359 represents the time.
4/15/04-2:49:00 PM - c:\Inetpub\FTPRoot\335009\
I need to do is adjust this script so I am not parsing the time from the filename and importing it.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x
$reLogdate='\d{1,2}\x2f\d{
$reLogtime='\d{1,2}:\d{1,2
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV
$adoCommand->{ActiveConnec
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($reLogdate)-($reLogtime)
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if (/($Servernumber)(TLOG)(\d
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6;
$FileMin=$7;
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam
print "$databasestring\n";
$adoCommand->{CommandText}
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
if( /($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2}?)(\d{2 }?)(\.zip) / )
($LogHour,$LogMin,$LogPM) = $Logtime=~/(\d+):(\d+)(.*P M)?/;
$LogHour += 12 if $LogPM;
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Log Hour,LogMi n) Values (#$Logdate#,#$Logtime#,\'$ Servernumb er\',\'$Fi lename\',$ Filesize,$ FileDay,$F ileMonth,$ FileYear,$ LogHour,$L ogMin)";
$LogHour += 12 if $LogPM;
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam
ASKER
Ozo
Where am I inserting these changes?
Thanks for your help.
Where am I inserting these changes?
Thanks for your help.
# if (/($Servernumber)(TLOG)(\d {2})(\d{2} )(\d{4})(\ d{2})(\d{2 })(\.zip)/ )
if( /($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2}?)(\d{2 }?)(\.zip) / )
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
if( /($Servernumber)(TLOG)(\d{
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
ASKER
Ozo thanks
I am not having any luck this is what I have.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x 5cFTPRoot\ x5c';
$reLogdate='\d{1,2}\x2f\d{ 1,2}\x2f\d {2}';
$reLogtime='\d{1,2}:\d{1,2 }:\d{2}\s[ PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV ER={Micros oft Access Driver (*.mdb)};DBQ=$dbFilename;" );
$adoCommand->{ActiveConnec tion} = $adoConnection;
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2}?)(\d{2 }?)(\.zip) / )
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if (/($Servernumber)(TLOG)(\d {2})(\d{2} )(\d{4})(\ d{2})(\d{2 })(\.zip)/ )
if( /($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2}?)(\d{2 }?)(\.zip) / )
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Fil eHour,File Min) Values (#$Logdate#,#$Logtime#,\'$ Servernumb er\',\'$Fi lename\',$ Filesize,$ FileDay,$F ileMonth,$ FileYear,$ FileHour,$ FileMin)";
print "$databasestring\n";
$adoCommand->{CommandText} =$database string;
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
I am not having any luck this is what I have.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x
$reLogdate='\d{1,2}\x2f\d{
$reLogtime='\d{1,2}:\d{1,2
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV
$adoCommand->{ActiveConnec
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($Servernumber)(TLOG)(\d{
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if (/($Servernumber)(TLOG)(\d
if( /($Servernumber)(TLOG)(\d{
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam
print "$databasestring\n";
$adoCommand->{CommandText}
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
remove or comment out the line
# if (/($Servernumber)(TLOG)(\d {2})(\d{2} )(\d{4})(\ d{2})(\d{2 })(\.zip)/ )
the next line was meant to replace it
# if (/($Servernumber)(TLOG)(\d
the next line was meant to replace it
ASKER
I would assume that I need to remove all of the references to file hour and file minute correct?
Thanks
Thanks
If you leave them in, they will both be 00 unless you get an old format line in your $logfile
ASKER
This is very strange I think I may be barking up the wrong tree
Old log example
8/1/04-2:41:38 PM - c:\Inetpub\FTPRoot\306406\ 306406TLOG 0801200412 36.zip 88733 Copied
8/1/04-2:41:52 PM - c:\Inetpub\FTPRoot\306424\ 306424TLOG 0731200413 58.zip 48261 Copied
Processes and imports into the database correctly
New Log example
10/17/04-12:10:08 PM - C:\InetPub\FTPRoot\302812\ 302812TLOG 1016200412 8.zip 79057 Copied
10/17/04-1:10:08 PM - C:\InetPub\FTPRoot\302984\ 302984TLOG 1017200412 57.zip 73890 Copied
does not process
The time thing is not an issue some files have the time and some don't that is how it was with the old log and this did not cause an issue in the past. I don't see any difference but the top works and the bottom doesn't.
Any ideas?
Old log example
8/1/04-2:41:38 PM - c:\Inetpub\FTPRoot\306406\
8/1/04-2:41:52 PM - c:\Inetpub\FTPRoot\306424\
Processes and imports into the database correctly
New Log example
10/17/04-12:10:08 PM - C:\InetPub\FTPRoot\302812\
10/17/04-1:10:08 PM - C:\InetPub\FTPRoot\302984\
does not process
The time thing is not an issue some files have the time and some don't that is how it was with the old log and this did not cause an issue in the past. I don't see any difference but the top works and the bottom doesn't.
Any ideas?
ASKER
I tried combining 2 lines from the old log and 2 lines from the new log.
The script simply hops over the new log lines and processes the old lines.
INSERT INTO "DataTable" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Fil eHour,File Min) Values (#8/1/04#,#2:41:38 PM#,'306406','306406TLOG08 0120041236 .zip',8873 3,01,08,20 04,12,36)
INSERT INTO "DataTable" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Fil eHour,File Min) Values (#8/1/04#,#2:41:52 PM#,'306424','306424TLOG07 3120041358 .zip',4826 1,31,07,20 04,13,58)
The script simply hops over the new log lines and processes the old lines.
INSERT INTO "DataTable" (LogDate,LogTime,ServerNam
INSERT INTO "DataTable" (LogDate,LogTime,ServerNam
#what happened to
if( /($reLogdate)-($reLogtime) \s-\s$reFt pdir($reSe rvernumber )\x5c($reF ilename)\s ($reFilesi ze).+/ )
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if( /($reLogdate)-($reLogtime)
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
ASKER
Here is what I have still no luck.
That line was missing but did not make a difference.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x 5cFTPRoot\ x5c';
$reLogdate='\d{1,2}\x2f\d{ 1,2}\x2f\d {2}';
$reLogtime='\d{1,2}:\d{1,2 }:\d{2}\s[ PA]M';
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV ER={Micros oft Access Driver (*.mdb)};DBQ=$dbFilename;" );
$adoCommand->{ActiveConnec tion} = $adoConnection;
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($reLogdate)-($reLogtime) \s-\s$reFt pdir($reSe rvernumber )\x5c($reF ilename)\s ($reFilesi ze).+/ )
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if( /($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2}?)(\d{2 }?)(\.zip) / )
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam e,FileName ,FileSize, FileDay,Fi leMonth,Fi leYear,Fil eHour,File Min) Values (#$Logdate#,#$Logtime#,\'$ Servernumb er\',\'$Fi lename\',$ Filesize,$ FileDay,$F ileMonth,$ FileYear,$ FileHour,$ FileMin)";
print "$databasestring\n";
$adoCommand->{CommandText} =$database string;
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
That line was missing but did not make a difference.
################begin perl script
use Win32::OLE;
#your vars
$dbFilename='c:/log.mdb';
$logfile='c:/log.txt';
$table='DataTable';
#reg exp vars
$reFtpdir='c:\x5cInetpub\x
$reLogdate='\d{1,2}\x2f\d{
$reLogtime='\d{1,2}:\d{1,2
$reServernumber='\d+';
$reFilename='.+\.zip';
$reFilesize='\d+';
my $FileDay=0;
my $FileMonth=0;
my $FileYear=0;
my $FileHour=0;
my $FileMin=0;
open(INFO, $logfile); # Open the file
my $adoConnection = CreateObject Win32::OLE "ADODB.Connection";
my $adoCommand = CreateObject Win32::OLE "ADODB.Command";
$adoConnection->Open("DRIV
$adoCommand->{ActiveConnec
my $databasestring="";
while ($lines=<INFO>)
{
$_=$lines;
if( /($reLogdate)-($reLogtime)
{
$Logdate=$1;
$Logtime=$2;
$Servernumber=$3;
$Filename=$4;
$Filesize=$5;
$_=$Filename;
if( /($Servernumber)(TLOG)(\d{
{
$FileMonth=$3;
$FileDay=$4;
$FileYear=$5;
$FileHour=$6||'00';
$FileMin=$7||'00';
}
else
{
$FileDay=0;
$FileMonth=0;
$FileYear=0;
$FileHour=0;
$FileMin=0;
}
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerNam
print "$databasestring\n";
$adoCommand->{CommandText}
$adoCommand->Execute();
if (Win32::OLE->LastError())
{
print Win32::OLE->LastError();
}
}
}
###################End Perl script
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked great thanks!
Actually,
/($Servernumber)(TLOG)(\d{ 2})(\d{2}) (\d{4})(\d {2})?(\d{2 })?\d*(\.z ip)/
should work better for
302812TLOG10162004128.zip
which is not like either
335009TLOG041420041359.zip
or
302812TLOG10102004.zip
(so how should 2004128 be treated?)
/($Servernumber)(TLOG)(\d{
should work better for
302812TLOG10162004128.zip
which is not like either
335009TLOG041420041359.zip
or
302812TLOG10102004.zip
(so how should 2004128 be treated?)
ASKER
Different program versions have developed three different filenames as you have noted.
I am only concerened with:
the first 6 numbers is the site identifier
then month, date and year after the tlog reference
I don't need time. When the script was written is was incorporated but I never used it.
Thanks
Ok, but you'll still need a \d* to match any possible digits between the year and the .zip, even if you don't use it.
ASKER
Thanks very much for your help!