Webサーバのアクセスログで統計が取りたいのでデータベースに投入しようと思って足掻いてみた

はじめに

　(元々統計とか取る気も無いので)アクセスログを普通にテキストでアーカイブしてただけのようなWebサーバのアクセスログの詳細な解析が急に必要になってしまった…みたいなことはありませんか? 私はあります。
　極めて泥臭い作業ではありますが、何かの参考になれば。
　(※MySQLに依存した命令/機能を使っていますので他DBの方は適宜同等の機能に読み替えてください)

前準備

CREATE TABLE `LOGDUMP` (
  `a` varchar(255) DEFAULT NULL,
  `b` varchar(255) DEFAULT NULL,
  `c` varchar(255) DEFAULT NULL,
  `d` varchar(255) DEFAULT NULL,
  `e` varchar(255) DEFAULT NULL,
  `f` varchar(255) DEFAULT NULL,
  `g` varchar(255) DEFAULT NULL,
  `h` varchar(255) DEFAULT NULL,
  `i` varchar(255) DEFAULT NULL,
  `j` varchar(255) DEFAULT NULL,
  `k` varchar(255) DEFAULT NULL,
  `l` varchar(255) DEFAULT NULL
); 

とりあえずこういう(とてもやっつけな)テーブルを作っておきます。

データ投入

コンソールから

mysql -u 《ユーザID》-h 《ホストIP》 "-p《パスワード》" 《スキーマ名》 -e "LOAD DATA LOCAL INFILE '《ログファイル》' INTO TABLE LOGDUMP FIELDS TERMINATED BY '\"'"

…とやると、ログファイルの内容が先程のテーブルに投入されます。ですが、投入されただけでこのままでは使い物にならないのでSQLで整形します。

SELECT
    b AS IP,
    SUBSTRING_INDEX(TRIM(SUBSTRING(c, 1, LOCATE('[', c) - 1)), ' ', 1) as CRIENTUSER,
    SUBSTRING_INDEX(TRIM(SUBSTRING(c, 1, LOCATE('[', c) - 1)), ' ', -1) as AUTHUSERID
    STR_TO_DATE(SUBSTRING(c, LOCATE('[', c) + 1), '%d/%M/%Y') AS DATE,
    TIME(STR_TO_DATE(SUBSTRING(c, LOCATE('[', c) + 1), '%d/%M/%Y:%H:%i:%s')) AS TIME
    SUBSTRING_INDEX(d, ' ', 1) AS METHOD,
    RTRIM(SUBSTRING(SUBSTRING_INDEX(d, ' ' ,- 2),1,LOCATE(' ',SUBSTRING_INDEX(d, ' ' ,- 2)))) AS FILE,
    SUBSTRING_INDEX(d, ' ' , -1) AS PROTOCOL,
    SUBSTRING_INDEX(TRIM(e), ' ', 1) AS STATUSCODE,
    SUBSTRING_INDEX(TRIM(e), ' ' , -1) AS SIZE,
    f AS REFERRER,
    h AS UA
FROM
    LOGDUMP 

　世にもおぞましい吐き気のするクエリですが、用は成します。
　(※これはApacheの標準のログフォーマットに合わせてありますので、フィールドをカスタマイズされている方はLOGDUMPテーブルの中身を見ながら適宜調整してください)
　いちいちこんなクエリを発行したくは無いので、頭にCREATE TABLE文でも付けて別テーブルに流したほうが良いと思います。
　後は時間帯でGROUP BYするなり如何様にでも調理出来るでしょう。