生ログを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\"" になります

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

%h 126.148.35.75
%l
%u
%t 28/Feb/2017:06:49:23 +0900
%r /fb/index.php?option=com_ajax&format=json
%>s  200
%b 596
%{Referer} http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23
%{User-agent} Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:51.0) Gecko/20100101 Firefox/51.0

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の作成は検索時のスピードアップようです。

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

ip 接続元IPアドレス
netname プロバイダ
client_add 接続元名
visit_time 訪問日時
request_page 訪問ページ
refer 参照元(一つ前)
agent ブラウザの種別

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

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

ip netname client_add visit_time request_page refer agent
101.143.X.X K-Opticom XXX.sekisuijushi.co.jp 2017-02-16 16:05:21   http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23   https://www.google.co.jp/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&ved=0ahUKEwjzpoLZhpTSAhUMy7wKHcA5A0MQFggaMAA&url=http%3A%2F%2Ffu-base.heteml.jp%2Ffb%2Findex.php%2Fblog%2F2014-08-20-05-31-46%2F306-2016-08-09-03-46-23&usg=AFQjCNEMfDlnekyRzqGOgKCf05nENOVISw&bvm=bv.147134024,d.dGc   Mozilla/5.0 (Windows NT 6.1; Trident/7.0; rv:11.0) like Gecko
 101.50.X.X  CCV  XXX-101-050-034-231.XXX.ne.jp  2017-02-16 15:34:45  http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23  http://search.yahoo.co.jp/  Mozilla/5.0 (Windows NT 6.3; WOW64; Trident/7.0; rv:11.0) like Gecko
 106.158.X.X  KDDI   XXX.ppp-bb.dion.ne.jp  2017-02-28 12:12:08   http://fu-base.heteml.jp/fb/index.php/blog/2014-08-20-05-31-46/306-2016-08-09-03-46-23  https://www.google.co.jp/  Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36

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

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

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

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