[转载]用perl写了个把IIS日志导入到mysql的程序
文章出处: Swords Home In Net[code]
表结构：
drop table if exists weblog;
create table weblog (
id int unsigned auto_increment PRIMARY KEY not null,
l_date date,
l_time time,
c_ip varchar(15),
s_ip varchar(15),
s_port varchar(5),
method varchar(10),
path varchar(255),
query varchar(255),
status varchar(3),
domain varchar(50),
system varchar(200)
);
程序（import.pl）：
参数为 -t -h -v --col
-t 指定需要导入的日志文件或者存放日志文件的目录
-h 打印帮助
-v 将会在程序运行时打印一些详细信息
--col 因为某些人从WIN上传文本文件到UNIX下时，文本的换行的地方总是会被加上一个^M的字符，使用--col可以过滤掉这些字符，如果你没有这种情况就不用这个参数
#! /usr/bin/perl -w
use strict;
use Getopt::Long;
use FileHandle;
use DBI;
my %opt; # holder for command line options
GetOptions (\%opt,"-t=s","-v","-h","--col");
if ($opt) # display help, see below for Usage() sub
my $VERBOSE;
if ( $opt ) { $VERBOSE = 1; } else { $VERBOSE = 0; } # set how noisy we are
my @FileList;
if (-d $opt) {
@FileList = GetFileList($opt);
}
elsif (-f $opt) {
push (@FileList,$opt);
}
if ($opt){
ColFilter (@FileList);
if ($VERBOSE == 1) {print "Done! Continue to insert log into database.\n";}
}
my ($i,$dsn,$dbh,$sth,$database,$hostname,$port,$user,$password);
$database = ""; #input the database name that you want import log into
$hostname = "192.168.211.221";
$port = "3306";
$user = "";#input your mysql user name
$password = "";#input your mysql user passowrd
$dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
$dbh = DBI->connect($dsn, $user, $password,{
PrintError => 1,
RaiseError => 1
});
$sth = $dbh->prepare("insert into weblog (l_date,l_time,c_ip,s_ip,s_port,method,path,query,status,domain,system) values (?,?,?,?,?,?,?,?,?,?,?);");
for ($i=0;$i<scalar(@FileList);$i++){
my ($log,@log);
if ($VERBOSE){print "Import $FileList[$i] into database ...... ";}
open (LOG,$FileList[$i]) or die "Can't open $FileList[$i]: $!\n";
while ($log=<LOG>){
if ($log !~ m/^#/){
my ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system,$sql);
@log = ExtractInfo($log);
$date = $log[0];
$sth->bind_param(1,$date);
$time = $log[1];
$sth->bind_param(2,$time);
$c_ip = $log[2];
$sth->bind_param(3,$c_ip);
$s_ip = $log[3];
$sth->bind_param(4,$s_ip);
$s_port = $log[4];
$sth->bind_param(5,$s_port);
$method = $log[5];
$sth->bind_param(6,$method);
$path = $log[6];
$sth->bind_param(7,$path);
$query = $log[7];
$sth->bind_param(8,$query);
$status = $log[8];
$sth->bind_param(9,$status);
$domain = $log[9];
$sth->bind_param(10,$domain);
$system = $log[10];
$sth->bind_param(11,$system);
$sth->execute();
$sth->finish;
}
}
close (LOG);
if ($VERBOSE){print "done.\n";}
}
$dbh->disconnect();
#############################################################################################
sub Usage { # print help information
my $error = shift;
print "Error: $error\n" if $error;
print "Usage: [-t] [-h] [-v|-q]\n";
print "Where: -h prints this screen\n";
print " -v verbose mode\n";
print " -t specify the target for import, it can be a path or filename\n";
print " --col use \"col\" command del the ^ character befor import\n";
exit;
}
sub ColFilter {
my $i;
print "There are scalar(@_) files for filter, please wait a moment ... \n\n";
for ($i=0;$i<scalar(@_);$i++){
my @file = split('/',$_[$i]);
my ($path,$filename);
$filename = pop(@file);
$path = join('/',@file);
if ($VERBOSE) {print "Filtering $_[$i] ...... ";}
system ("cat $_[$i] | col -b > /tmp/$filename");
unlink $_[$i];
system ("mv /tmp/$filename $path/");
if ($VERBOSE) {print "done.\n";}
}
}
sub GetFileList { #If target is a directory, get a file-list include all files in the target
my $target = $_[0];
opendir(TARGET,$target) or die "can't opendir $target: $!\n";
my (@FileList,@file,$i);
@file = readdir(TARGET);
for ($i=0;$i<scalar(@file);$i++){
push (@FileList,"$target$file[$i]");
}
closedir(TARGET);
shift(@FileList);
shift(@FileList);
@FileList = sort(@FileList);
return @FileList;
}
sub PrintList {
my $i;
for ($i=0;$i<scalar(@_);$i++){
print "$_[$i]: $_[$i]\n";
}
}
sub ExtractInfo {
my @log = split(' ',$_[0]);
my ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system,@sql);
$date = $log[0];
$time = $log[1];
$c_ip = $log[2];
$s_ip = $log[6];
$s_port = $log[7];
$method = $log[8];
$path = $log[9];
$path =~ s/\'/\'\'/g;
$query = $log[10];
$query =~ s/\'/\'\'/g;
$status = $log[11];
$domain = $log[12];
$system = $log[13];
$system =~ s/\'/\'\'/g;
$system =~ s/\+//g;
@sql = ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system);
return @sql;
}
sub PathAndFilename {
my $file = $_[0];
if (not -f $file) {
print "$file isn't a file.\n";
exit;
}
my @file = split('/',$file);
my ($path,$filename);
$filename = pop(@file);
$path = join('/',@file);
$path .= '/';
@file = ($path,$filename);
return @file;
}[/code]
页:
[1]