Solved

Help with adjusting perl script

Posted on 2004-10-15
18
251 Views
Last Modified: 2010-04-17
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\302812TLOG10102004.zip 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\335009TLOG041420041359.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\x5cFTPRoot\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("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $adoConnection;

my $databasestring="";
while ($lines=<INFO>)
{
     $_=$lines;
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $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,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
Comment
Question by:irishmanjb
  • 9
  • 9
18 Comments
 
LVL 84

Expert Comment

by:ozo
ID: 12324579
if( /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/ )
0
 
LVL 84

Expert Comment

by:ozo
ID: 12324658
($LogHour,$LogMin,$LogPM) =  $Logtime=~/(\d+):(\d+)(.*PM)?/;
$LogHour += 12 if $LogPM;
$databasestring="INSERT INTO \"$table\" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,LogHour,LogMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$LogHour,$LogMin)";
0
 

Author Comment

by:irishmanjb
ID: 12341085
Ozo

Where am I inserting these changes?
 
Thanks for your help.
0
 
LVL 84

Expert Comment

by:ozo
ID: 12342460
#       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;
          }
0
 

Author Comment

by:irishmanjb
ID: 12346536
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\x5cFTPRoot\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("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $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,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
 
LVL 84

Expert Comment

by:ozo
ID: 12346626
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
0
 

Author Comment

by:irishmanjb
ID: 12346627
I would assume that I need to remove all of the references to file hour and file minute correct?

Thanks
0
 
LVL 84

Expert Comment

by:ozo
ID: 12346785
If you leave them in, they will both be 00 unless you get an old format line in your $logfile
0
 

Author Comment

by:irishmanjb
ID: 12346872
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\306406TLOG080120041236.zip 88733 Copied
8/1/04-2:41:52 PM - c:\Inetpub\FTPRoot\306424\306424TLOG073120041358.zip 48261 Copied
Processes and imports into the database correctly

New Log example
10/17/04-12:10:08 PM - C:\InetPub\FTPRoot\302812\302812TLOG10162004128.zip 79057 Copied
10/17/04-1:10:08 PM - C:\InetPub\FTPRoot\302984\302984TLOG101720041257.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?




0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:irishmanjb
ID: 12346906
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,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#8/1/04#,#2:41:38 PM#,'306406','306406TLOG080120041236.zip',88733,01,08,2004,12,36)
INSERT INTO "DataTable" (LogDate,LogTime,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#8/1/04#,#2:41:52 PM#,'306424','306424TLOG073120041358.zip',48261,31,07,2004,13,58)
0
 
LVL 84

Expert Comment

by:ozo
ID: 12347022
#what happened to
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $Logdate=$1;
          $Logtime=$2;
          $Servernumber=$3;
          $Filename=$4;
          $Filesize=$5;
          $_=$Filename;
0
 

Author Comment

by:irishmanjb
ID: 12347215
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\x5cFTPRoot\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("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=$dbFilename;");
$adoCommand->{ActiveConnection} = $adoConnection;

my $databasestring="";
while ($lines=<INFO>)
{
     $_=$lines;
     if( /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/ )
     {
          $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,ServerName,FileName,FileSize,FileDay,FileMonth,FileYear,FileHour,FileMin) Values (#$Logdate#,#$Logtime#,\'$Servernumber\',\'$Filename\',$Filesize,$FileDay,$FileMonth,$FileYear,$FileHour,$FileMin)";
          print "$databasestring\n";
          $adoCommand->{CommandText}=$databasestring;
          $adoCommand->Execute();
          if (Win32::OLE->LastError())
          {
               print Win32::OLE->LastError();
          }
     }
}
 
###################End Perl script
0
 
LVL 84

Accepted Solution

by:
ozo earned 500 total points
ID: 12347532
Sorry,
    /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2}?)(\d{2}?)(\.zip)/
should have been
    /($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})?(\d{2})?(\.zip)/
also,
  $reFtpdir='c:\x5cInetpub\x5cFTPRoot\x5c';
is matching only lower case "c"
try
  /($reLogdate)-($reLogtime)\s-\s$reFtpdir($reServernumber)\x5c($reFilename)\s($reFilesize).+/i
0
 

Author Comment

by:irishmanjb
ID: 12347650
That worked great thanks!
0
 
LVL 84

Expert Comment

by:ozo
ID: 12347690
Actually,
/($Servernumber)(TLOG)(\d{2})(\d{2})(\d{4})(\d{2})?(\d{2})?\d*(\.zip)/
should work better for
302812TLOG10162004128.zip
which is not like either
335009TLOG041420041359.zip
or
302812TLOG10102004.zip

(so how should 2004128 be treated?)
0
 

Author Comment

by:irishmanjb
ID: 12347978

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


0
 
LVL 84

Expert Comment

by:ozo
ID: 12348083
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.
0
 

Author Comment

by:irishmanjb
ID: 12349783
Thanks very much for your help!
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
triangle challenge 4 79
topping1 challenge 7 53
word0 challenge 3 58
index Out OF Range Exception error 4 32
Purpose To explain how to place a textual stamp on a PDF document.  This is commonly referred to as an annotation, or possibly a watermark, but a watermark is generally different in that it is somewhat translucent.  Watermark’s may be text or graph…
A short article about problems I had with the new location API and permissions in Marshmallow
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now