SQLiteとFlotr2でアクセスログ解析

アクセスログ解析を行うための方法をまとめました。

  1. ログの用意
  2. SQLiteファイルへ変換
  3. レポートの出力
  4. グラフ付きレポートの出力

ログ解析の方式

PerlでApacheログ形式のテキストファイルからSQLiteデータベースを作成します。
その後、ログ解析用のHTMLレポートを出力します。

1. ログの用意

ログを用意します。フォーマットは以下の様な形です。

Apacheから取得したログ

100.200.300.400 - - [07/Feb/2014:04:24:39 +0900] "GET /waza/ HTTP/1.1" 200 5221 "-" "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0)"
400.300.200.100 - - [07/Feb/2014:04:24:40 +0900] "GET /waza/ HTTP/1.1" 200 5221 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:26.0) Gecko/20100101 Firefox/26.0"

2. SQLiteファイルへ変換

以下の様なスクリプトでログ(log.txt)からlog.sqliteを作成します。
#!/usr/bin/perl -w

use DBI;

# log.sqliteを読込(なければ作成)
my $dbh = DBI->connect("dbi:SQLite:dbname=log.sqlite");

# テーブルがなければ作成
$dbh->do("CREATE TABLE IF NOT EXISTS log (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, time TEXT DEFAULT (DATETIME('now','localtime')), remote_addr TEXT, user_agent TEXT, url TEXT, referer TEXT)");


my $sth = $dbh->prepare("INSERT INTO log (remote_addr, time, url, referer, user_agent) VALUES(?, DATETIME(? ,'localtime'), ?, ?, ?)");
$dbh->{AutoCommit} = 0; # トランザクションの開始

open my $fh, "<", "log.txt";
for my $line (<$fh>) {
if ($line =~ m/^(.*?) .*? .*? \[(.*?)\] ".* (.*?) .*?" .*? .*? "(.*?)" "(.*?)"/o){
# 上の正規表現で値を取り出す
# $1 : IP
# $2 : date
# $3 : url (ホストからの相対パス)
# $4 : referer
# $5 : user_agent

$sth->execute($1, toDatetime($2), $3, $4, $5);
}
} close $fh;
$dbh->commit;

sub toDatetime{ # Apacheの時間形式をSQLiteのDATETIME形式へ変換
my $str = shift;
my $month = {
'Jan' => '01',
'Feb' => '02',
'Mar' => '03',
'Apr' => '04',
'May' => '05',
'Jun' => '06',
'Jul' => '07',
'Aug' => '08',
'Sep' => '09',
'Oct' => '10',
'Nov' => '11',
'Dec' => '12',
};
$str =~ /(\d{2})\/(\w{3})\/(\d{4}):(.{8}).*/o;
my $day = $1;
my $mon = $2;
my $year = $3;
my $time = $4;
return "$year-$month->{$mon}-$day $time";
}

3. レポートの出力

ここまでくれば後はレポートを書き出すだけです。
SQLiteで集計を行いましょう。

なるべくSQL文だけで必要なデータを取り出せるようにSQL文を書いてみました。

アクセス数の多いページ順に並べる (上から30件)

SELECT url, COUNT(*) as count FROM log GROUP BY url ORDER BY count DESC limit 30

レポートを出力するスクリプト

#!/usr/bin/perl -w

use DBI;

my $dbh = DBI->connect("dbi:SQLite:dbname=log.sqlite");
my $sth = $dbh->prepare("SELECT url, COUNT(*) as count FROM log GROUP BY url ORDER BY count DESC limit 30");
$sth->execute();

my ($url, $count);
$sth->bind_columns(\$url, \$count);

while ($sth->fetch){
$html .= "<tr><td>$count</td><td>$url</td></tr>";
}

open(FH ,"> report1.html") || die "Can't make file : $!\n";
print FH <<HTML;
<!DOCTYPE html>
<html>
<head>
<title>report1</title>
</head>
<body>
<h1>レポート1 累計ランキング</h1>
<table>
<tr>
<td>アクセス数</td><td>URL</td>
</tr>
$html
</table>
</body>
</html>
HTML

close(FH);

4. グラフ付きレポートの出力

グラフ出力はFlotr2を使うことにします。
Flotr2はグラフ描画のためのJavaScriptライブラリ(ライセンスはMIT)です。
コードの記述は以下のサンプルを参考にしてみてください。

  • 折れ線グラフ
  • 棒グラフ
  • 円グラフ
  • バブルチャート
  • ローソク足チャート
  • 今回はHTML出力処理をPerlで直書きしていますが、
    $などの文字が含まれる場合などエスケープ等が面倒なのでTemplateなどのパッケージを使用しましょう。
    グラフごとのテンプレートを作っておき、流し込むデータを切り替えることで複数のケースに対応できます。

    4-1. 折れ線グラフレポートのサンプル

    アクセス数の推移を取り出すSQL (集計日から過去30日分)

    SELECT STRFTIME('%Y/%m/%d',time) as dt,COUNT(*) as count FROM log GROUP BY dt having time > datetime('now','localtime','-30 days')

    レポートを出力するスクリプト

    #!/usr/bin/perl -w

    use DBI;

    my $dbh = DBI->connect("dbi:SQLite:dbname=log.sqlite");
    my $sth = $dbh->prepare("SELECT STRFTIME('%Y/%m/%d',time) as dt,COUNT(*) as count FROM log GROUP BY dt having time > datetime('now','localtime','-30 days')");
    $sth->execute();

    my ($time, $count);
    $sth->bind_columns(\$time, \$count);

    my $data1 = [];
    my $data2 = [];
    my $cnt = 0;
    while ($sth->fetch){
    push @$data1 , "[$cnt, $count]";
    push @$data2 , "[$cnt, \"$time\"]";
    $cnt++;
    }
    my $result1 = join ",", @$data1;
    my $result2 = join ",", @$data2;


    open(FH ,"> report2.html") || die "Can't make file : $!\n";
    print FH <<HTML;
    <!DOCTYPE html>
    <html>
    <head>
    <title>report2</title>
    <script type="text/javascript" src="flotr2.min.js"></script>
    </head>
    <body>
    <h1>レポート2 アクセス推移</h1>
    <div id="graph" style="width:500px;height:400px;"></div>
    <p style="text-decoration:underline;cursor:pointer" onclick="f.download.saveImage('png')">結果の画像化(Data URL)</p>
    <script type="text/javascript">
    <!--
    f = Flotr.draw(document.getElementById("graph"),
    [
    {
    data: [
    $result1
    ],
    label: "過去30日分のアクセス"
    }
    ],
    {
    legend: {
    position: "ne",
    backgroundColor: "#D2E8FF"
    },
    xaxis: {
    title: "時間",
    ticks:[
    $result2
    ],
    labelsAngle: 45
    },
    yaxis: {
    title: "アクセス数"
    },
    grid: {
    verticalLines: false,
    horizontalLines: true
    },
    HtmlText: false
    }
    );
    -->
    </script>
    </body>
    </html>
    HTML

    close(FH);

    4-2. 円グラフレポートのサンプル

    特定のアクセス元をピックアップして抽出するSQL

    SELECT count(*),
    CASE
    WHEN referer like "https://www.google.co.jp%" then 'Google'
    WHEN referer like "http://search.yahoo.co.jp%" then 'Yahoo!'
    WHEN referer like "http://t.co%" then 'Twitter'
    ELSE "Other"
    END as ref
    FROM log group by ref
    #!/usr/bin/perl -w

    use DBI;

    my $dbh = DBI->connect("dbi:SQLite:dbname=log.sqlite");

    my $sql =<<SQL;
    SELECT count(*),
    CASE
    WHEN referer like "https://www.google.co.jp%" then 'Google'
    WHEN referer like "http://search.yahoo.co.jp%" then 'Yahoo!'
    WHEN referer like "http://t.co%" then 'Twitter'
    ELSE "Other"
    END as ref
    FROM log group by ref
    SQL

    my $sth = $dbh->prepare($sql);
    $sth->execute();

    my ($count, $ref);
    $sth->bind_columns(\$count, \$ref);

    my $data = {};

    while ($sth->fetch){
    $data{$ref} = $count;
    }
    my $count_google = $data{'Google'};
    my $count_yahoo = $data{'Yahoo!'};
    my $count_twitter = $data{'Twitter'};
    my $count_other = $data{'Other'};


    open(FH ,"> report3.html") || die "Can't make file : $!\n";
    print FH <<HTML;
    <!DOCTYPE html>
    <html>
    <head>
    <title>report</title>
    <script type="text/javascript" src="flotr2.min.js"></script>
    </head>
    <body>
    <h1>レポート3 アクセス元</h1>
    <div id="graph" style="width:500px;height:400px;"></div>
    <p style="text-decoration:underline;cursor:pointer" onclick="f.download.saveImage('png')">結果の画像化(Data URL)</p>
    <script type="text/javascript">
    <!--
    f = Flotr.draw(document.getElementById("graph"),
    [
    {
    data: [
    [0,$count_google]
    ],
    label: "Google"
    },
    {
    data: [
    [0,$count_yahoo]
    ],
    label: "Yahoo!"
    },
    {
    data: [
    [0,$count_twitter]
    ],
    label: "Twitter"
    },
    {
    data: [
    [0,$count_other],
    ],
    label: "Other"
    }
    ],
    {
    legend: {
    position: "ne",
    backgroundColor: "#D2E8FF"
    },
    pie: {
    show: true,
    explode: 0
    },
    grid: {
    verticalLines: false,
    horizontalLines: false
    },
    xaxis: {
    showLabels: false
    },
    yaxis: {
    showLabels: false
    }, HtmlText: false
    }
    );
    -->
    </script>
    </body>
    </html>
    HTML

    close(FH);