Geek at Christmas
So, as is traditional I spend my christmas holidays playing with epistula. Now I have referer tracking turned working again.
The problem with referer tracking is extracting the data from log files. When the server had mod_log_sql it was easy (I have an entire log stats suite built for mod_log_sql), but since log_sql doesn’t support Apache 2 yet (A patch to make it do so was released yesterday. It remains untested) I had to brush off my extremely limited perl skillz to create this, a perl program to send apache logs to mysql:
#!/usr/bin/perl
use DBD::mysql;
#Database options:
$dbUser = "username";
$dbPass = "password";
$dbName = "database";
$database = DBI->connect(
"dbi:mysql:$dbName:localhost:1114",
$dbUser, $dbPass
);
while (<>) {
my ($client, $identuser, $authuser, $date, $method,
$url, $protocol, $status, $bytes, $referer,$agent) =
/^(S+) (S+) (S+) [(.*?)] "(S+) (.*?) (S+)" (S+) (S+) "(.*?)" "(.*?)"$/;
$q = "insert into apachelogs
(remote_host, remote_user, request_time, request_method,
request_uri, request_protocol, status, bytes_sent, referer, agent)
values
(".$database->quote($client).", ".$database->quote($authuser).", '"
.$date."', ".$database->quote($method).", ".$database->quote($url)
.", ".$database->quote($protocol).", ".$database->quote($status)
.", ".$database->quote($bytes).", ".$database->quote($referer)
.", ".$database->quote($agent).")";
my $sth = $database->prepare($q);
$sth->execute();
}
![[RSS Icon]](/assets/images/buttons/rss2.gif){kind=small}
![[ESF Icon]](/assets/images/buttons/esf.gif){kind=small}
![[CDF Icon]](/assets/images/buttons/cdf.gif){kind=small}
paul:
I took a look at this and found the regex didn’t quite work: I came up with this one.
(I would have wrapped this in pre tags, but I read the warning: no innocent pair of angle brackets deserves that).
while () {
my ($client, $identuser, $authuser, $date, $method, $url, $protocol, $status,$bytes, $referer, $agent) =/(S+).*? (S+) (S+) [(.*?)] “(S*) (S+) (S+)” (S+) (S+) ”(S+)” ”(.*)?”/;