[精华] 写了个把IIS日志导入到mysql的程序

来源 chinaunix.net kuqin整理

表结构：
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{h}) {Usage();} # display help, see below for Usage() sub

my $VERBOSE;
if ( $opt{v} ) { $VERBOSE = 1; } else { $VERBOSE = 0; }  # set how noisy we are

my @FileList;
if (-d $opt{t}) {
    @FileList = GetFileList($opt{t});
}
elsif (-f $opt{t}) {
    push (@FileList,$opt{t});
}

if ($opt{col}){
    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: $0 [-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;
}

原文链接：http://bbs.chinaunix.net/viewthread.php?tid=15879
转载请注明作者名及原文出处

收藏本页到：　　　　　