diff options
Diffstat (limited to 'contrib')
-rw-r--r-- | contrib/mysql_import_combined_log.pl | 74 |
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 |
2 | use strict; | 2 | use strict; |
3 | use Getopt::Std; | 3 | use Getopt::Long qw(:config bundling); |
4 | use DBI; | 4 | use DBI; |
5 | use Time::ParseDate; | 5 | use Date::Parse; |
6 | 6 | ||
7 | my %options = (); | 7 | my %options = (); |
8 | my $i = 0; | 8 | my $i = 0; |
@@ -17,14 +17,15 @@ my $TIMESTAMP = 3; | |||
17 | my $REQUEST_LINE = 4; | 17 | my $REQUEST_LINE = 4; |
18 | my @cols = ( | 18 | my @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 | ); |
33 | my $col = ''; | 34 | my $col = ''; |
34 | 35 | ||
35 | $Getopt::Std::STANDARD_HELP_VERSION = 1; ## if we show the help, exit afterwards. | 36 | %options = ( |
36 | getopts('h:u:p:d:t:f:', \%options); | 37 | "version" => sub { VERSION_MESSAGE(); exit 0; }, |
38 | "help|?" => sub { HELP_MESSAGE(); exit 0; }, | ||
39 | ); | ||
37 | 40 | ||
41 | GetOptions (\%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 | ||
45 | if( ! $options{d} ) | 55 | if( ! $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 | ||
51 | if( ! $options{t} ) | 62 | if( ! $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(); |
80 | if (! $dbh) { | ||
81 | exit 1; | ||
82 | } | ||
68 | 83 | ||
69 | $sql = "INSERT INTO $options{t} ("; | 84 | $sql = "INSERT INTO $options{t} ("; |
70 | foreach $col (@cols) | 85 | foreach $col (@cols) |
@@ -73,11 +88,12 @@ foreach $col (@cols) | |||
73 | } | 88 | } |
74 | chop($sql); | 89 | chop($sql); |
75 | $sql .= ') VALUES ('; | 90 | $sql .= ') VALUES ('; |
76 | 91 | my ($linecount,$insertcount) = (0,0); | |
77 | while($line = <STDIN>) | 92 | while($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 | 115 | print "Parsed $linecount Log lines\n"; | |
116 | print "Inserted $insertcount records\n"; | ||
117 | print "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; |
177 | Imports an Apache combined log into a MySQL database. | 198 | Imports an Apache combined log into a MySQL database. |
178 | Usage: mysql_import_combined_log.pl -d <database name> -t <table name> [-h <hostname>] [-u <username>] [-p <password>] [-f <filename] | 199 | Usage: 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. |
187 | EOF | 208 | EOF |
188 | } | 209 | } |
189 | 210 | ||
@@ -194,10 +215,7 @@ EOF | |||
194 | # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # | 215 | # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # |
195 | sub VERSION_MESSAGE | 216 | sub 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 | ||
200 | 1; | 221 | 1; |
201 | |||
202 | 1; | ||
203 | |||