懸案だったApache Access Log を RDB に突っ込んで SQL で解析ってのもやっと片付いた。

調べてはみたんだがまさにそのもの、っていうのは見つからなかった。
その代り見つけたのがこれ。Apache の combined 形式のログをブッた切る perl の正規表現。

[ぴ] - Apache Combined Log を効率的にパースする正規表現メモ , Apache Combined Log 解析正規表現ベンチマークの補足 (2007/10/05) ,..

ここまであれば、あとは未だに perl の知識 0 な俺にでも書ける。

#!/usr/bin/perl
#
# al2sql.pl: apache combind log to sql insert command translator
#
# usage:
# al2sql.pl [tablename] < apache_log_file > sql_command_file
#
# table should be created as follows
# create table MYAPACHELOG (HOST text, IDENT char(4), USER char(16), METHOD char(8), RESOURCE text, PROTO char(8), STATUS char(3), REFERER text, AGENT text, SEARCHSTR text, DOMAIN text, TIME datetime, BYTES int unsigned);
"MYAPACHELOG"'Jan''Feb''Mar''Apr''May''Jun''Jul''Aug''Sep''Oct''Nov''Dec''/\'\'/g;
  ($host, $ident, $user, $time, $method, $resource, $proto, $status, $bytes, $re
ferer, $agent)
        = ($_ =~ /^([^ ]*) ([^ ]*) ([^ ]*) \[([^]]*)\] "([^ ]*)(?: *([^ ]*) *([^
 ]*))?" ([^ ]*) ([^ ]*) "(.*?)" "(.*?)"/);
  $searchstr = "";
  if ($referer =~ /search\?/) {
        $S2 = $referer;
        $S2 =~ s/cache:[^\+]*\+//g;
        $S2 =~ s/＆/ /g;
        $S2 =~ /[pq]=([^ ]*)/;
        $S3 = $1;
        $S3 =~ s/\+/ /g;
        $searchstr = nkf("-w --url-input", $S3);
  }
  $domain = "";
  if ($host !~ /^[0-9\.]*$/) {
        $domain = $host;
        $domain =~ s/^[^\.]*\.//;
  }
  $time =~ /(.*)\/(.*)\/([^:]*)(.*)/;
  $time = "$3-$month{$2}-$1$4";
  print "INSERT INTO $MYTABLENAME ";
  print "(HOST, IDENT, USER, TIME, METHOD, RESOURCE, PROTO, STATUS, BYTES, REFERER, AGENT, SEARCHSTR, DOMAIN) ";
  print "VALUES ('', '$ident', '', '', '', '', '$proto', '', '', '', '$agent', '', '') ";
  print ";\n";
} 

注意：31行目に&というのが見えたら、それは blog エンジン側が勝手に & を変換してしまったものである。変換を抑制できなそうなので全角で書いておく。

search stringと hostname から domain の分離だけはここで頑張ってみた(時刻と日付も分離しといた方がいいかもだが…)。
これで insert 文が生成されるので、あとは db の方に流し込んでやればいい。 table は作り方は念の為冒頭のコメントに書いておいた。 db は sqlite でも mysql でもなんでも。 blog が mysql/postgresql 使ってたりすればそこに同居すればあとは sql 投げる php でも書けば終了。

念の為、この日記エントリについては creative commons ライセンスにしておくので再利用は御自由に。

This work is licensed under a Creative Commons Attribution-Share Alike 2.1 Japan License.

既に読み込んだログとの重複チェックは考えていない。日々 log rotate されているとして、前日分として切り分けられた分をその都度(cronででも) db に追加するスタイルなので、 log rotate よりも短いインターバルで db に登録したい人は下のエントリを参考に工夫してみて欲しい。

ApacheのログをDBで解析する - KamoLand