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

猪猪

文章出处: Swords Home In Net

1. 表结构：
   
2. drop table if exists weblog;
   
3. create table weblog (
   
4. id int unsigned auto_increment PRIMARY KEY not null,
   
5. l_date date,
   
6. l_time time,
   
7. c_ip varchar(15),
   
8. s_ip varchar(15),
   
9. s_port varchar(5),
   
10. method varchar(10),
    
11. path varchar(255),
    
12. query varchar(255),
    
13. status varchar(3),
    
14. domain varchar(50),
    
15. system varchar(200)
    
16. );
    
17. 程序（import.pl）：
    
18. 参数为 -t -h -v --col
    
19. -t 指定需要导入的日志文件或者存放日志文件的目录
    
20. -h 打印帮助
    
21. -v 将会在程序运行时打印一些详细信息
    
22. --col 因为某些人从WIN上传文本文件到UNIX下时，文本的换行的地方总是会被加上一个^M的字符，使用--col可以过滤掉这些字符，如果你没有这种情况就不用这个参数
    
23. 
    
24. 
    
25. #! /usr/bin/perl -w
    
26. 
    
27. use strict;
    
28. use Getopt::Long;
    
29. use FileHandle;
    
30. use DBI;
    
31. 
    
32. my %opt;     # holder for command line options
    
33. GetOptions (\%opt,"-t=s","-v","-h","--col");
    
34. 
    
35. if ($opt) # display help, see below for Usage() sub
    
36. 
    
37. my $VERBOSE;
    
38. if ( $opt ) { $VERBOSE = 1; } else { $VERBOSE = 0; } # set how noisy we are
    
39. 
    
40. my @FileList;
    
41. if (-d $opt) {
    
42.   @FileList = GetFileList($opt);
    
43. }
    
44. elsif (-f $opt) {
    
45.   push (@FileList,$opt);
    
46. }
    
47. 
    
48. if ($opt){
    
49.   ColFilter (@FileList);
    
50.   if ($VERBOSE == 1) {print "Done! Continue to insert log into database.\n";}
    
51. }
    
52. 
    
53. my ($i,$dsn,$dbh,$sth,$database,$hostname,$port,$user,$password);
    
54. 
    
55. $database = "";           #input the database name that you want import log into
    
56. $hostname = "192.168.211.221";
    
57. $port = "3306";
    
58. $user = "";#input your mysql user name
    
59. $password = "";#input your mysql user passowrd
    
60. 
    
61. $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";
    
62. $dbh = DBI->connect($dsn, $user, $password,{
    
63. PrintError => 1,
    
64. RaiseError => 1
    
65. });
    
66. 
    
67. $sth = $dbh->prepare("insert into weblog (l_date,l_time,c_ip,s_ip,s_port,method,path,query,status,domain,system) values (?,?,?,?,?,?,?,?,?,?,?);");
    
68. 
    
69. for ($i=0;$i<scalar(@FileList);$i++){
    
70.   my ($log,@log);
    
71.   
    
72.   if ($VERBOSE){print "Import $FileList[$i] into database ...... ";}
    
73.   open (LOG,$FileList[$i]) or die "Can't open $FileList[$i]: $!\n";
    
74.   while ($log=<LOG>){
    
75. if ($log !~ m/^#/){
    
76.   my ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system,$sql);
    
77.   @log = ExtractInfo($log);
    
78.   
    
79.   $date = $log[0];
    
80.   $sth->bind_param(1,$date);
    
81.   
    
82.   $time = $log[1];
    
83.   $sth->bind_param(2,$time);
    
84.   
    
85.   $c_ip = $log[2];
    
86.   $sth->bind_param(3,$c_ip);
    
87.   
    
88.   $s_ip = $log[3];
    
89.   $sth->bind_param(4,$s_ip);
    
90.   
    
91.   $s_port = $log[4];
    
92.   $sth->bind_param(5,$s_port);
    
93.   
    
94.   $method = $log[5];
    
95.   $sth->bind_param(6,$method);
    
96.   
    
97.   $path = $log[6];
    
98.   $sth->bind_param(7,$path);
    
99.   
    
100.   $query = $log[7];
     
101.   $sth->bind_param(8,$query);
     
102.   
     
103.   $status = $log[8];
     
104.   $sth->bind_param(9,$status);
     
105.   
     
106.   $domain = $log[9];
     
107.   $sth->bind_param(10,$domain);
     
108.   
     
109.   $system = $log[10];
     
110.   $sth->bind_param(11,$system);   
     
111.   
     
112.   $sth->execute();
     
113.   $sth->finish;
     
114. }
     
115.   }
     
116.   close (LOG);
     
117.   if ($VERBOSE){print "done.\n";}
     
118. }
     
119. 
     
120. $dbh->disconnect();
     
121. 
     
122. #############################################################################################
     
123. 
     
124. sub Usage {     # print help information
     
125.   my $error = shift;
     
126.   print "Error: $error\n" if $error;
     
127.   print "Usage: [-t] [-h] [-v|-q]\n";
     
128.   print "Where: -h       prints this screen\n";
     
129.   print "     -v       verbose mode\n";
     
130.   print "     -t       specify the target for import, it can be a path or filename\n";
     
131.   print "     --col     use \"col\" command del the ^ character befor import\n";
     
132.   exit;
     
133. }
     
134. 
     
135. sub ColFilter {
     
136.   my $i;
     
137.   print "There are scalar(@_) files for filter, please wait a moment ... \n\n";
     
138.   for ($i=0;$i<scalar(@_);$i++){
     
139. my @file = split('/',$_[$i]);
     
140. my ($path,$filename);
     
141.   
     
142. $filename = pop(@file);
     
143. $path = join('/',@file);
     
144. 
     
145. if ($VERBOSE) {print "Filtering $_[$i] ...... ";}
     
146. system ("cat $_[$i] | col -b > /tmp/$filename");
     
147. unlink $_[$i];
     
148. system ("mv /tmp/$filename $path/");
     
149. if ($VERBOSE) {print "done.\n";}
     
150.   }
     
151. }
     
152. 
     
153. sub GetFileList {         #If target is a directory, get a file-list include all files in the target
     
154.   my $target = $_[0];
     
155.   opendir(TARGET,$target) or die "can't opendir $target: $!\n";
     
156.   my (@FileList,@file,$i);
     
157.   @file = readdir(TARGET);
     
158. 
     
159.   for ($i=0;$i<scalar(@file);$i++){
     
160. push (@FileList,"$target$file[$i]");
     
161.   }
     
162.   closedir(TARGET);
     
163.   shift(@FileList);
     
164.   shift(@FileList);
     
165.   @FileList = sort(@FileList);
     
166.   return @FileList;
     
167. }
     
168. 
     
169. sub PrintList {
     
170.   my $i;
     
171.   for ($i=0;$i<scalar(@_);$i++){
     
172. print "$_[$i]: $_[$i]\n";
     
173.   }
     
174. }
     
175. 
     
176. sub ExtractInfo {
     
177.   my @log = split(' ',$_[0]);
     
178.   my ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system,@sql);
     
179. 
     
180.   $date = $log[0];
     
181.   $time = $log[1];
     
182.   $c_ip = $log[2];
     
183.   $s_ip = $log[6];
     
184.   $s_port = $log[7];
     
185.   $method = $log[8];
     
186.   $path = $log[9];
     
187.   $path =~ s/\'/\'\'/g;
     
188.   $query = $log[10];
     
189.   $query =~ s/\'/\'\'/g;
     
190.   $status = $log[11];
     
191.   $domain = $log[12];
     
192.   $system = $log[13];
     
193.   $system =~ s/\'/\'\'/g;
     
194.   $system =~ s/\+//g;
     
195.   
     
196.   @sql = ($date,$time,$c_ip,$s_ip,$s_port,$method,$path,$query,$status,$domain,$system);
     
197.   return @sql;
     
198. }
     
199. 
     
200. sub PathAndFilename {
     
201.   my $file = $_[0];
     
202.   if (not -f $file) {
     
203. print "$file isn't a file.\n";
     
204. exit;
     
205.   }
     
206.   my @file = split('/',$file);
     
207.   my ($path,$filename);
     
208.   $filename = pop(@file);
     
209.   $path = join('/',@file);
     
210.   $path .= '/';
     
211.   @file = ($path,$filename);
     
212.   return @file;
     
213. }

复制代码