さまざまな形式のアクセスログファイルから、
分析集計しやすいようにCSVファイルへの変換、さらには簡易データベースであるSQLiteへの取り込みを解説する。

CSVファイルにする

テキスト形式のログファイルを扱いやすいCSVに変換する。

NCSA combined形式

テキストファイルにすればExcelなどの表計算ソフトやPostgreSQLなどのデータベースにも取り込むことができる。自由自在になる。

バーチャルホスト名を含まないNCSA combined logから以下の8項目をタブ区切りテキストで書き出す。

アクセス元のアドレス
ユーザー名
日時
ページのパス
ステータスコード
送信バイト数
参照元
ブラウザ

sed -e 's/^\(.*\) .* \(.*\) \[\(.*\)\] "\(.* \(.*\) .*\)\?" \(.*\) \(.*\) "\(.*\)" "\(.*\)"$/\1\t\2\t\3\t\5\t\6\t\7\t\8\t\9/g' /var/log/httpd/access_log > /tmp/log.csv

複数の圧縮されたログファイルがある場合は

for f in `find /var/log/httpd/ -type f -name "access_*.gz"`; do gzip $f -dc | 
sed -e 's/^\(.*\) .* \(.*\) \[\(.*\)\] "\(.* \(.*\) .*\)\?" \(.*\) \(.*\) "\(.*\)" "\(.*\)"$/\1\t\2\t\3\t\5\t\6\t\7\t\8\t\9/g' >> /tmp/log.csv; done

簡単にデータの中身を見る

簡単にデータの中身を見るのであれば、テキストファイルを直接SQLの形式で集計できるPythonスクリプト「q」が便利
http://harelba.github.io/q/

タブ区切りテキストなら

# ファイルから
./q -t "select c1, ... from ファイル名 where ..."

# 標準入力から
cat ... | ./q -t "select c1, ... from ファイル名 where ..."

でSQLを実行できる（内部ではSQLiteを使っている）

処理の具体例

Googlebotのアクセスについてアクセス数を集計

./q -t "select * from /tmp/log.csv where c8 like '%Googlebot%'" > Googlebot.txt    # 一度Googlebotのヒットのみ別ファイルに抽出
./q -t "select c1, c4, count(0) pv from ./Googlebot.txt group by c1, c4 order by pv desc"

特定の階層へのボット以外のアクセスについてボット以外のページビュー数（画像やCSSなどのスクリプトは含まない）を集計
パイプを使ってまとめて1回のコマンドで実行する例

cat /var/log/httpd/access_log | 
grep -E '\s/list/' | grep -Ev '\.(jpg|gif|png|css|js)\s' |    # ここでレコードを絞り込んでおくと速い
sed -e 's/^\(.*\) .* \(.*\) \[\(.*\)\] "\(.* \(.*\) .*\)\?" \(.*\) \(.*\) "\(.*\)" "\(.*\)"$/\1\t\2\t\3\t\5\t\6\t\7\t\8\t\9/g' | 
./q -t 'select c4, count(0) pv from -  where c8 not like "%bot%" group by c4 order by pv desc limit 20'

Pythonさえインストールされていれば大概機能するので大変便利なツールだが、その都度ファイルを読み込むので処理が低速（内部でSQLiteを使用している）。
やはりデータベースに取り込んだほうが高速にデータ処理できる。

W3C拡張形式（AmazonのCloudFront）

NCSA形式とは異なりフィールドの区切り文字が決まってる（タブ）ので処理自体は簡単である。
半角スペース文字のみデコードすればいい。

全カラムを一旦インポート用のCSVファイルとして書き出す。

for f in `find . -type f -name "*.gz"`; do gzip $f -dc | 
sed -r -e '/^#/d' -e 's/%2520/ /g' $f >> /tmp/log.csv; done

アクセスログをデータベースに取り込む

SQLiteに取り込む（NCSA拡張形式）

まずは簡単なデータベース（RDB）ということでSQLiteに取り込んでみる。

データ加工

SQLiteでは日付の形式があらかじめ厳格に定められているので、その形式にあわせてCSVの日付の列を変更する必要がある。
先のsedコマンドだけではできないのでawkなどを使うか、あるいはここではログからの抽出処理をまとめてPerlスクリプトで行う。

for f in `find /var/log/httpd/ -type f -name "access_*.gz"`; do gzip $f -dc | 
perl -wln log2csv.pl >> /tmp/log_sqlite.csv; done

この「log2csv.pl」の中身は

log2csv.pl

my $sep = "\t";
my %months = (
    'Jan' => '01', 'Feb' => '02', 'Mar' => '03', 'Apr' => '04',
    'May' => '05', 'Jun' => '06', 'Jul' => '07', 'Aug' => '08',
    'Sep' => '09', 'Oct' => '10', 'Nov' => '11', 'Dec' => '12',
);

my $months = join('|', map { quotemeta } keys %months);

if ( my @row = $_ =~ /^.* (.*) .* (.*) \[(.*)\] ".* (.*) .*" (.*) (.*) "(.*)" "(.*)"$/ ) {

    if ( my @time = $row[2] =~ m#(\d+)/(\w+)/(\d+):(\d+):(\d+):(\d+).*# ) {
        $time[1] =~ s/($months)/$months{$1}/eg;
        $row[2] = $time[2]."/".$time[1]."/".$time[0]." ".$time[3].":".$time[4].":".$time[5];
    }

    map { s/^-$// } @row;
    $_ = join($sep, @row);
    print $_;
}

このままコピペして使えばいい。

テーブルの作成～インポート

SQLiteを実行する。

sqlite3 /tmp/log.sqlite

テーブルを作成し、CSVファイルを取り込む。先のフォーマットであれば

-- 他のRDBとの互換性のためにVARCHAR(N)で指定
create table log (
    remote_host VARCHAR(100),
    remote_user VARCHAR(100),
    str_time VARCHAR(100),
    request_path VARCHAR(10000),
    status INTEGER NULL,
    bytes INTEGER NULL,
    referer VARCHAR(10000),
    user_agent VARCHAR(10000)
);

.separator "\t"
.import '/tmp/log_sqlite.csv' log

処理の具体例・Googlebotのクローラ分析

Googlebotのクローラ分析のみであれば実は多くのケースでSQLiteだけで十分。

for f in `find /var/log/httpd/ -type f -name "access_*.gz"`; do gzip $f -dc | 
perl -wln log2csv_googlebot.pl >> /tmp/log_sqlite_googlebot.csv; done

log2csv_googlebot.pl

my $sep = "\t";
my %months = (
    'Jan' => '01', 'Feb' => '02', 'Mar' => '03', 'Apr' => '04',
    'May' => '05', 'Jun' => '06', 'Jul' => '07', 'Aug' => '08',
    'Sep' => '09', 'Oct' => '10', 'Nov' => '11', 'Dec' => '12',
);

my $months = join('|', map { quotemeta } keys %months);

if ( my @row = $_ =~ /^.* (.*) .* (.*) \[(.*)\] ".* (.*) .*" (.*) (.*) "(.*)" "(.*)"$/ ) {
    if ( $row[7] !~ /Googlebot/i ) { next; } # User-AgentがGooglebotでなければ除外

    if ( my @time = $row[2] =~ m#(\d+)/(\w+)/(\d+):(\d+):(\d+):(\d+).*# ) {
        $time[1] =~ s/($months)/$months{$1}/eg;
        $row[2] = $time[2]."/".$time[1]."/".$time[0]." ".$time[3].":".$time[4].":".$time[5];
    }

    map { s/^-$// } @row;
    $_ = join($sep, @row);
    print $_;
}

このlog_sqlite_googlebot.csvをSQLiteの.importコマンドで取り込めばいい。