If its a small company or a midsize project,this simple post might help you out…
This is what it does (code snippets added at the end of the post):
Download logs from your FTP -> Scan them and put the relevant info in database -> Create a webpage to query database and display the output.
Resources:
1> Perl Script
- Connects to FTP
- Downloads Access Log files
- Pattern matching to extract the relevant data
- Insert the relavant data into database.
2> MYSQL Database
- Stores the data send by Perl script
- SQL queries as requested by PHP page
3> PHP Page
- Displays the data requested by firing queries on MYSQL database.
So here we go:
Perl Script:
-Mac users be happy, perl is preinstalled on mac but for windows you have to follow the whole procedure of setting up perl,apache,mysql.. which can be painful at times.
Here is my Google search:
- Code
http://www.goldb.org/remotebackup.html
http://perl.about.com/od/filesystem/qt/perlglob.htm
http://www.cs.cf.ac.uk/Dave/PERL/node241.html
-MYSQL Database
Perl uses DBI and DBD connectors to connect to MySql Database.MySql is not preinstalled on Mac so you can follow these links… might help you out.
http://www.abbeyworkshop.com/howto/osx/osxMysqlInstall/index.html
http://developer.apple.com/internet/opensource/osdb.html
http://www.quicomm.com/apm_dbddbi.html
http://www.cpan.org/modules/by-category/07_Database_Interfaces/DBD/
- PHP
http://maestric.com/doc/mac/apache_php_mysql_snow_leopard
#!/usr/bin/perl
use DBI;
use Net::FTP;
# connect
my $dbh = DBI->connect("DBI:mysql:database=log_db;host=localhost", "root","", {'RaiseError' => 1})or die "Cannot connect to the DB: $DBI::errstr\n" ;
# Get the maximum date
my $sth = $dbh->prepare("select max(logDate) from iphonePHP");
$sth->execute;
while(@row = $sth->fetchrow_array())
{
print "$row[0]";
$year_db = substr($row[0],0,4);
print "year db:".$year_db."\n";
$month_db = substr($row[0],5,2);
print "month_db:".$month_db."\n";
$date_db = substr($row[0],8,2);
print "day:".$date_db."\n";
}
$date_db = ($date_db+1);
if($date_db = 10 )
{
$LOGFILE = "access_log.".$year_db."-".$month_db."-".$date_db;
}
if($date_db == 29 && ($month ==2) && !($year_db %400 ==0 || ($year_db %100 != 0 && $year_db %4 == 0)))
{
$date_db = 1;
$month_db = $month_db + 1;
if($month_db < 10)
{
$LOGFILE = "access_log.".$year_db."-"."0".$month_db."-"."0".$date_db;
}
else
{
$LOGFILE = "access_log.".$year_db."-".$month_db."-"."0".$date_db;
}
}
if($date_db == 30 && ($month == 2)&& ($year_db %400 ==0 || ($year_db %100 != 0 && $year_db %4 == 0)))
{
$date_db = 1;
$month_db = $month_db + 1;
if($month_db < 10)
{
$LOGFILE = "access_log.".$year_db."-"."0".$month_db."-"."0".$date_db;
}
else
{
$LOGFILE = "access_log.".$year_db."-".$month_db."-"."0".$date_db;
}
}
if($date_db == 31 && (($month_db == 4)|| ($month_db == 6) || ($month_db == 9)|| ($month_db == 1)))
{
$date_db = 1;
$month_db = $month_db + 1;
if($month_db < 10)
{
$LOGFILE = "access_log.".$year_db."-"."0".$month_db."-"."0".$date_db;
}
else
{
$LOGFILE = "access_log.".$year_db."-".$month_db."-"."0".$date_db;
};
}
if($date_db == 32)
{
$date_db = 1;
$month_db = $month_db + 1;
if($month_db new("02e6818.netsolhost.com", Debug => 0)
or die "Cannot connect to some.host.name: $@";
$ftp->login("$login","$password")
or die "Cannot login ", $ftp->message;
$ftp->get($LOGFILE)
or die "get failed ", $ftp->message;
$ftp->quit;
print "filename: downloaded".$LOGFILE."\n";
$log_date = substr($LOGFILE,11,10);
print "LOG DATE:".$log_date."\n";
open(LOGFILE) or die("Could not open log file.");
foreach $line ()
{
$w = "(.+?)";
$line =~ m/^$w $w $w \[$w:$w $w\] "$w $w $w" $w $w "$w" "$w"/;
$site = $1;
$logName = $2;
$fullName = $3;
$date = $4;
$time = $5;
$gmt = $6;
$req = $7;
$file = $8;
$proto = $9;
$status = $10;
$length = $11;
$referal = $12;
$user_ag = $13;
# do line-by-line processing.
if ($file =~ m/NylonLive\/new.php/)
{
#print $req.$file."\n";
my $sth = $dbh->prepare(qq{insert into iphonePHP SET ip=?, getRequest=?,magzine_name="NYLON",logDate=?,logTime=?,status=?,length=?,referal=?,user_link=?});
$sth->execute($site,$req.$file,$log_date,$time,$status,$length,$referal,$user_ag) or $dbh->errstr;
}
elsif ($file =~ m/NylonRIM\/index.php/)
{
#print $req.$file."\n";
my $sth = $dbh->prepare(qq{insert into rimPHP SET ip=?, getRequest=?,magzine_name="NYLON",logDate=?,logTime=?,status=?,length=?,referal=?,user_link=?});
$sth->execute($site,$req.$file,$log_date,$time,$status,$length,$referal,$user_ag) or $dbh->errstr;
}
elsif ($file =~ m/NylonLive\/AndroidMZN.php/)
{
#print $req.$file."\n";
my $sth = $dbh->prepare(qq{insert into androidPHP SET ip=?, getRequest=?,magzine_name="NYLON",logDate=?,logTime=?,status=?,length=?,referal=?,user_link=?});
$sth->execute($site,$req.$file,$log_date,$time,$status,$length,$referal,$user_ag) or $dbh->errstr;
}
elsif ($file =~ m/instant_mozine\/getWWE.php/)
{
# print $req.$file."\n";
my $sth = $dbh->prepare(qq{insert into iphonePHP SET ip=?, getRequest=?,magzine_name="WWE",logDate=?,logTime=?,status=?,length=?,referal=?,user_link=?});
$sth->execute($site,$req.$file,$log_date,$time,$status,$length,$referal,$user_ag) or $dbh->errstr;
}
elsif ($file =~ m/instant_mozine\/getWWEdroid.php/)
{
#print $req.$file."\n";
my $sth = $dbh->prepare(qq{insert into androidPHP SET ip=?, getRequest=?,magzine_name="WWE",logDate=?,logTime=?,status=?,length=?,referal=?,user_link=?});
$sth->execute($site,$req.$file,$log_date,$time,$status,$length,$referal,$user_ag) or $dbh->errstr;
}
}
print $referal."\n";
print $user_ag."\n";
close(LOGFILE);
# clean up
$dbh->disconnect();
p.s: post is not completed yet