summaryrefslogtreecommitdiffstats
path: root/contrib
diff options
context:
space:
mode:
Diffstat (limited to 'contrib')
-rw-r--r--contrib/mysql_import_combined_log.pl74
1 files changed, 46 insertions, 28 deletions
diff --git a/contrib/mysql_import_combined_log.pl b/contrib/mysql_import_combined_log.pl
index 3384b77..1c801f2 100644
--- a/contrib/mysql_import_combined_log.pl
+++ b/contrib/mysql_import_combined_log.pl
@@ -1,8 +1,8 @@
1#!/usr/bin/perl 1#!/usr/bin/perl -w
2use strict; 2use strict;
3use Getopt::Std; 3use Getopt::Long qw(:config bundling);
4use DBI; 4use DBI;
5use Time::ParseDate; 5use Date::Parse;
6 6
7my %options = (); 7my %options = ();
8my $i = 0; 8my $i = 0;
@@ -17,14 +17,15 @@ my $TIMESTAMP = 3;
17my $REQUEST_LINE = 4; 17my $REQUEST_LINE = 4;
18my @cols = ( 18my @cols = (
19 'remote_host', ## 0 19 'remote_host', ## 0
20 'remote_user', ## 1 20 'remote_logname', ## 1
21 '', ## 2 21 'remote_user', ## 2
22 'time_stamp', ## 4 22 'request_time', ## 3.string
23 'request_line', ## 5 23 'time_stamp', ## 3.posix
24 'request_line', ## 5
24 'request_method', ## 6 25 'request_method', ## 6
25 'request_uri', ## 7 26 'request_uri', ## 7
26 'request_args', ## 8 27 'request_args', ## 8
27 'request_protocol', ## 9 28 'request_protocol', ## 9
28 'status', ## 10 29 'status', ## 10
29 'bytes_sent', ## 11 30 'bytes_sent', ## 11
30 'referer', ## 12 31 'referer', ## 12
@@ -32,9 +33,18 @@ my @cols = (
32); 33);
33my $col = ''; 34my $col = '';
34 35
35$Getopt::Std::STANDARD_HELP_VERSION = 1; ## if we show the help, exit afterwards. 36%options = (
36getopts('h:u:p:d:t:f:', \%options); 37 "version" => sub { VERSION_MESSAGE(); exit 0; },
38 "help|?" => sub { HELP_MESSAGE(); exit 0; },
39 );
37 40
41GetOptions (\%options,
42 "h|host=s",
43 "d|database=s",
44 "t|table=s",
45 "u|username=s",
46 "p|password=s",
47 "f|logfile=s");
38 48
39$options{h} ||= 'localhost'; 49$options{h} ||= 'localhost';
40$options{d} ||= ''; 50$options{d} ||= '';
@@ -44,12 +54,14 @@ $options{f} ||= '';
44 54
45if( ! $options{d} ) 55if( ! $options{d} )
46{ 56{
57 HELP_MESSAGE();
47 print "Must supply a database to connect to.\n"; 58 print "Must supply a database to connect to.\n";
48 exit 1; 59 exit 1;
49} 60}
50 61
51if( ! $options{t} ) 62if( ! $options{t} )
52{ 63{
64 HELP_MESSAGE();
53 print "Must supply table name.\n"; 65 print "Must supply table name.\n";
54 exit 1; 66 exit 1;
55} 67}
@@ -65,6 +77,9 @@ if( $options{f} )
65} 77}
66 78
67$dbh = Connect(); 79$dbh = Connect();
80if (! $dbh) {
81 exit 1;
82}
68 83
69$sql = "INSERT INTO $options{t} ("; 84$sql = "INSERT INTO $options{t} (";
70foreach $col (@cols) 85foreach $col (@cols)
@@ -73,11 +88,12 @@ foreach $col (@cols)
73} 88}
74chop($sql); 89chop($sql);
75$sql .= ') VALUES ('; 90$sql .= ') VALUES (';
76 91my ($linecount,$insertcount) = (0,0);
77while($line = <STDIN>) 92while($line = <STDIN>)
78{ 93{
94 $linecount++;
79 @parts = SplitLogLine( $line ); 95 @parts = SplitLogLine( $line );
80 next if( $parts[$TIMESTAMP] == 0 ); 96 next if( $parts[$TIMESTAMP+1] == 0 );
81 $valuesSql = ''; 97 $valuesSql = '';
82 for( $i = 0; $i < @cols; ++$i ) 98 for( $i = 0; $i < @cols; ++$i )
83 { 99 {
@@ -91,10 +107,14 @@ while($line = <STDIN>)
91 if( ! $sth->execute() ) 107 if( ! $sth->execute() )
92 { 108 {
93 print "Unable to perform specified query.\n$sql$valuesSql\n" . $sth->errstr() . "\n"; 109 print "Unable to perform specified query.\n$sql$valuesSql\n" . $sth->errstr() . "\n";
110 } else {
111 $insertcount++;
94 } 112 }
95 $sth->finish(); 113 $sth->finish();
96} 114}
97 115print "Parsed $linecount Log lines\n";
116print "Inserted $insertcount records\n";
117print "to table '$options{t}' in database '$options{d}' on '$options{h}'\n";
98 118
99# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 119# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
100# Connects to a MySQL database and returns the connection. 120# Connects to a MySQL database and returns the connection.
@@ -117,7 +137,7 @@ sub SplitLogLine
117 my $char = ''; 137 my $char = '';
118 my $part = ''; 138 my $part = '';
119 my @parts = (); 139 my @parts = ();
120 my $count; 140 my $count = 0;
121 chomp($line); 141 chomp($line);
122 for( $i = 0; $i < length($line); ++$i ) 142 for( $i = 0; $i < length($line); ++$i )
123 { 143 {
@@ -127,7 +147,8 @@ sub SplitLogLine
127 ## print "Found part $part.\n"; 147 ## print "Found part $part.\n";
128 if( $count == $TIMESTAMP ) 148 if( $count == $TIMESTAMP )
129 { 149 {
130 $part = parsedate($part, WHOLE => 1, DATE_REQUIRED => 1, TIME_REQUIRED => 2); 150 push(@parts, "[".$part."]");
151 $part = str2time($part);
131 } 152 }
132 push(@parts, $part); 153 push(@parts, $part);
133 if( $count == $REQUEST_LINE ) 154 if( $count == $REQUEST_LINE )
@@ -176,14 +197,14 @@ sub HELP_MESSAGE
176 print<<EOF; 197 print<<EOF;
177Imports an Apache combined log into a MySQL database. 198Imports an Apache combined log into a MySQL database.
178Usage: mysql_import_combined_log.pl -d <database name> -t <table name> [-h <hostname>] [-u <username>] [-p <password>] [-f <filename] 199Usage: mysql_import_combined_log.pl -d <database name> -t <table name> [-h <hostname>] [-u <username>] [-p <password>] [-f <filename]
179 -h <host name> The host to connect to. Default is localhost. 200 --host|-h <host name> The host to connect to. Default is localhost.
180 -d <database name> The database to use. Required. 201 --database|-d <database name> The database to use. Required.
181 -u <username> The user to connect as. 202 --username|-u <username> The user to connect as.
182 -p <password> The user's password. 203 --password|-p <password> The user's password.
183 -t <table name> The name of the table in which to insert data. 204 --table|-t <table name> The name of the table in which to insert data.
184 -f <file name> The file to read from. If not given, data is read from stdin. 205 --logfile|-f <file name> The file to read from. If not given, data is read from stdin.
185 --help Print out this help message. 206 --help|-? Print out this help message.
186 --version Print out the version of this software. 207 --version Print out the version of this software.
187EOF 208EOF
188} 209}
189 210
@@ -194,10 +215,7 @@ EOF
194# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # 215# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #
195sub VERSION_MESSAGE 216sub VERSION_MESSAGE
196{ 217{
197 print "mysql_import_combined_log.pl version 1.0\n"; 218 print "mysql_import_combined_log.pl version 1.1\n";
198} 219}
199 220
2001; 2211;
201
2021;
203