‹‹ 上一主题 | 下一主题 ››
发新话题
打印

[转载]用perl写了个把IIS日志导入到mysql的程序

冰血封情

老林

团队决策人

Rank: 9Rank: 9Rank: 9

E.S.T运营责任人

帖子
6819 
精华
834 
积分
36070 
阅读权限
255 
性别
男 
来自
中国 
在线时间
586 小时 
注册时间
2004-6-25 
最后登录
2008-9-6 

优秀管理奖 资料收集奖 原创技术奖 核心建议奖

楼主 大 中 小 发表于 2004-8-22 21:45  只看该作者

[转载]用perl写了个把IIS日志导入到mysql的程序

文章出处: Swords Home In Net
复制内容到剪贴板
代码:

表结构：
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&#39;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(&#39;/&#39;,$_[$i]);
my ($path,$filename);
  
$filename = pop(@file);
$path = join(&#39;/&#39;,@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&#39;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(&#39; &#39;,$_[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/\&#39;/\&#39;\&#39;/g;
  $query = $log[10];
  $query =~ s/\&#39;/\&#39;\&#39;/g;
  $status = $log[11];
  $domain = $log[12];
  $system = $log[13];
  $system =~ s/\&#39;/\&#39;\&#39;/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&#39;t a file.\n";
exit;
  }
  my @file = split(&#39;/&#39;,$file);
  my ($path,$filename);
  $filename = pop(@file);
  $path = join(&#39;/&#39;,@file);
  $path .= &#39;/&#39;;
  @file = ($path,$filename);
  return @file;
}

TOP

‹‹ 上一主题 | 下一主题 ››
发新话题