生ログをMySQLに蓄積する — 集客の準備 —

生ログ(Apache log)を活用してサイト分析を弊社では行っています。Google　Analyticsと組み合わせ、いわゆるダブルダギング分析です。 このエントリーではそのままでは扱いにくい生ログ（Apache log)をMySQLに格納し検索・抽出が出来る事を紹介します。

生ログ(Apache log)とは

WEBサイトには、訪問者（ボットを含む）が足跡を残していきます。その足跡の全ての情報を記録したのが「ログ」「生ログ」です。

レンタルサーバーでは、サイトから手元に「生ログ」をダウンロードする事が出来ます。

弊社ではクライアントさんのWEBサイト運営をお手伝いしています。「ヘテムル」、「さくらインターネット」を利用していますが、「生ログのダウンロード」は可能です。

★ヘテムルはこちらを参考にしてください。 —> アクセス解析の生ログのチェック方法

★さくらインターネットはこちらを参考にしてください。  –>アクセスログの設定と管理 (FTPツールでダウンロード可能）

生ログ(Apache log)の中身

ダウンロードした生ログは下記な感じになります。弊社http://www.futurebase.co.jpの生ログの一部（数行）が下記になります

———————————

126.148.35.75 – – [28/Feb/2017:06:49:23 +0900] “GET /fb/index.php?option=com_ajax&format=json HTTP/1.1” 200 596 “http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23” “Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:51.0) Gecko/20100101 Firefox/51.0”
126.209.1.20 – – [28/Feb/2017:06:49:43 +0900] “GET /fb/index.php HTTP/1.1” 200 7741 “https://www.google.co.jp/” “Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36”
126.209.1.20 – – [28/Feb/2017:06:49:43 +0900] “GET /fb/templates/fbtemplate/css/bootstrap.css HTTP/1.1” 304 124 “http://fu-base.heteml.jp/fb/index.php” “Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36”
126.209.1.20 – – [28/Feb/2017:06:50:05 +0900] “GET /fb/index.php HTTP/1.1” 200 7644 “https://www.google.co.jp/” “Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36”
126.209.1.20 – – [28/Feb/2017:06:50:51 +0900] “GET /fb/index.php HTTP/1.1” 200 7644 “https://www.bing.com/” “Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36”
126.209.1.20 – – [28/Feb/2017:06:51:13 +0900] “GET /fb/index.php HTTP/1.1” 200 7644 “https://www.google.co.jp/” “Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36”

———————————-

なんだこりゃ～～～　ですよね。。。。

生ログ(Apache log)にはフォーマット（書式）がある

フォーマットの説明はこちらのサイトを参照–> Apache HTTP サーバ バージョン 2.4

フォーマットテーブルを引用します。

フォーマット文字列	説明
%%	パーセント記号
%a	リモート IP アドレス
%A	ローカル IP アドレス
%B	レスポンスのバイト数。HTTP ヘッダは除く。
%b	レスポンスのバイト数。HTTP ヘッダは除く。CLF 書式。 すなわち、1 バイトも送られなかったときは 0 ではなく、 ‘-‘ になる
%{Foobar}C	サーバに送られたリクエスト中のクッキー Foobar の値
%D	リクエストを処理するのにかかった時間、マイクロ秒単位
%{FOOBAR}e	環境変数 FOOBAR の内容
%f	ファイル名
%h	リモートホスト
%H	リクエストプロトコル
%{Foobar}i	サーバに送られたリクエストの Foobar: ヘッダの内容
%l	(identd からもし提供されていれば) リモートログ名。 これは mod_ident がサーバに存在して、 IdentityCheck ディレクティブが On に設定されていない限り、 - になります。
%m	リクエストメソッド
%{Foobar}n	他のモジュールからのメモ Foobar の内容
%{Foobar}o	応答の Foobar: ヘッダの内容
%p	リクエストを扱っているサーバの正式なポート
%{format}p	サーバがリクエストを処理しているポートの公式 (訳注: canonical) のポート番号か、 サーバの実際のポート番号か、クライアント側の実際のポート番号かです。 format に使える文字列は canonical, local, remote になります。
%P	リクエストを扱った子プロセスのプロセス ID
%{format}P	リクエストを扱ったワーカーのプロセス ID かスレッド ID。 format として有効な値は pid, tid, hextid です。hextid を使うには APR 1.2.0 以降が必要です。
%q	問い合せ文字列 (存在する場合は前に ? が追加される。 そうでない場合は空文字列)
%r	リクエストの最初の行
%s	ステータス。内部でリダイレクトされたリクエストは、元々の リクエストのステータス — 最後のステータスは %>s
%t	リクエストを受付けた時刻。 CLF の時刻の書式 (標準の英語の書式)
%{format}t	format で与えられた書式による時刻。format は strftime (3) の 書式である必要がある。(地域化されている可能性がある)
%T	リクエストを扱うのにかかった時間、秒単位
%u	リモートユーザ (認証によるもの。ステータス (%s) が 401 のときは意味がないものである可能性がある)
%U	リクエストされた URL パス。クエリ文字列は含まない
%v	リクエストを扱っているサーバの正式な ServerName
%V	UseCanonicalName の設定によるサーバ名
%X	応答が完了したときの接続ステータス: X = 応答が完了する前に接続が異常終了 + = 応答が送られた後に接続を持続することが可能 - = 応答が送られた後に接続が切られる (このディレクティブは Apache 1.3 の後期のバージョンでは %c に割り当てられて いましたが、これは歴史的に ssl が使用している %{var}c 構文と衝突していました。)
%I	リクエストとヘッダを含む、受け取ったバイト数。 0 にはならない。 これを使用するためには mod_logio が必要
%O	ヘッダを含む、送信したバイト数。0 にはならない。 これを使用するためには mod_logio が必要

抜粋した生ログ

—————————

 126.148.35.75 - - [28/Feb/2017:06:49:23 +0900] "GET /fb/index.php?option=com_ajax&format=json HTTP/1.1" 200 596 "http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23" "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:51.0) Gecko/20100101 Firefox/51.0"

---------------

の書式は

NCSA extended/combined ログ書式

"%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-agent}i\""　になります

書式に照らし合わせると下のテーブルになります。

MySQLに生ログ(Apache log)を格納してみよう、テーブル定義

生ログをMySQLに格納出来るようにパース(parse)するにはPython Apache moduleを使用します。apache-log-parser 使用方法はググルと沢山出てきますので(^O^)

調べてみてくださいね。（そのうちプログラムについても書いて行きたいと思います。）

MySQLのテーブルはこんな感じにしてみました。

Create table t_access_log ( 	log_no Bigint NOT NULL AUTO_INCREMENT, 	ip Varchar(20), 	inetnum Varchar(100), 	netname Varchar(255), 	descr Varchar(255), 	client_add Varchar(100), 	visit_time Datetime, 	request_type Varchar(10), 	request_page Varchar(255), 	request_prot Varchar(20), 	refer Varchar(300), 	agent Varchar(255), 	UNIQUE (log_no),  Primary Key (log_no)) ENGINE = InnoDB;  CREATE INDEX idx_request_page ON t_access_log(request_page); CREATE INDEX idx_request_ip ON t_access_log(ip); CREATE INDEX idx_request_netname ON t_access_log(netname); CREATE INDEX idx_request_visit_time ON t_access_log(visit_time);  CREATE INDEX idx_request_refer ON t_access_log(refer);

Indexの作成は検索時のスピードアップようです。

テーブルのフィールドの意味は下記です。

MySQLに格納した生ログ（Apache log）を見てみる

前に書いたエントリー「サイトに訪れるロボット？？」にアクセスした方のログの抜粋です。

生ログ（Apache log)データを見ることによりサイトに訪れるユーザの個々の動きを追うことが出来ます。

もちろん、集計したり、統計を取ることも出来ます。

次回以降は生ログ分析と集客、コンバージョンをテーマに具体的なケースを紹介して行きたいと思います。

弊社に興味があるかたはこちらからお問い合わせください。