Apacheのアクセスログをタブ区切りのTSV形式に変換

概要

　さくらインターネットのレンタルサーバでウェブサイトを運用していると、 アクセス分析できるツールが標準提供されているのですが、 もうちょっと自由な分析がやりたいなと考えました。 さくらインターネットではApacheのアクセスログを生ログファイルのまま保管・提供してくれているので、 そのファイルを変換してPostgreSQLのテーブルに入れることにしました。 今回は項目がスペース区切りで出力されているApacheのアクセスログから必要な項目だけを抽出して、 タブ区切りのTSV形式に変換したファイルを作成するところまでやります。 変換処理はさくらインターネットのレンタルサーバにTeraTermで接続してもできると思いますが、 共有サーバであまり負荷をかけちゃいけない気がしたので、ファイルをFTPでダウンロードして AWSのEC2インスタンスに転送してからやりました。

構成

想定環境

　サーバにはAmazonのEC2インスタンスを利用しました。 AmazonマシンイメージはAWS標準のものではなく、AWS Marketplaceから取得しています。 サーバのスペックは以下のとおりです。

サーバ構成

OSバージョン

CentOS Linux release 7.5.1804 (Core)

手順

準備

アクセスログの解凍

　さくらのレンタルサーバで取得されているApacheのアクセスログは、 前日以前のファイルがzip圧縮されています。 以下の例では2019年9月1日のアクセスログを対象に処理します。 さくらのレンタルサーバからダウンロードしたアクセスログファイル(access_log_20190901.gz)を AWSのEC2インスタンス上に転送します。細かい転送方法は割愛します。 zipファイルをgunzipで解凍します。

# cd /data/log/
# ls -l access_log_20190901.gz
-rw-r--r-- 1 ftpuser ftpuser 166044 Sep  1 15:05 access_log_20190901.gz
# gunzip access_log_20190901.gz
# ls -l access_log_20190901
-rw-r--r-- 1 ftpuser ftpuser 2454264 Sep  1 15:05 access_log_20190901

アクセスログのフォーマット確認

　さくらのレンタルサーバのアクセスログファイルの中身を表示してみると、 例えば以下のようなログが出力されています。

# cat access_log_20190901
　（前略）
ossfan.net 12-345-67-890f1.shg1.eonet.ne.jp - - [01/Sep/2019:00:03:17 +0900] "GET /images/nav/nav-news.gif HTTP/1.1" 200 2048 "http://ossfan.net/setup/linux-17.html" "Mozilla/5.0 (iPhone; CPU iPhone OS 12_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.1.2 Mobile/15E148 Safari/604.1"
　（後略）

ログフォーマットの正規表現を確認

　出力されたログファイルをTSV形式に変換する際に ログフォーマットを一意に表すことができる正規表現を確認します。 ログファイルをsedコマンドでTSV形式に変換する際に、 ログの中の思わぬところにスペースが入っているとか、 ダブルクォートがあるはずのところにないとか、 出力項目が足りないなどのようなケースがあると、 その行は変換されずに欠落してしまいます。 そこで、変換に使う正規表現がログファイルの全行とマッチするかを事前に確認します。 今回は正規表現として以下の文字列を使います。

　^.* .* .* .* \[.*\] ".* .* .*" .* .* ".*" ".*"$

　見てわかる通り、元のログの各項目がスペース区切りであることを前提で考えています。

　なお、この手順はやや冗長なので毎回やる必要はありませんが、 今回は初めてだったのでちょっと念入りにやりました。 アクセスログ access_log_20190901 に対して正規表現がマッチした行を、 一時ファイル access_log_20190901.tmp1 に出力します。 以下の例では10,369行のログに対して、正規表現がマッチした行が10,369行あり、 行数が同じだったので、想定外のフォーマットのログがないことが分かりました。

# cat access_log_20190901 | sed -e '/^.* .* .* .* \[.*\] ".* .* .*" .* .* ".*" ".*"$/!d' > access_log_20190901.tmp1
# wc -l access_log_20190901
10369 access_log_20190901
# wc -l access_log_20190901.tmp1
10369 access_log_20190901.tmp1
# diff access_log_20190901 access_log_20190901.tmp1

変換

アクセスログをTSV形式に変換

　sedコマンドを使ってアクセスログをTSV形式に変換します。 変換する際には先ほど確認した正規表現を使ってスペース区切りをタブ区切りに変換します。 やり始めて気づいたのですが、sedで正規表現をマッチさせてTSV形式に変換するやり方の場合、 12項目あるうち9項目しか抽出できないようです（勘違いだったらごめんなさい）。 ログの各行に正規表現を適用し、マッチした項目順に\1、\2、\3、・・・、\8、\9と 表現して変換後のファイルに出力するのですが、\10以降が使えないようなので、 変換後の形式に9個までの項目しか使えません。 変換後のタブ区切りのログは以下のように表現します。

　\1\t\2\t\3\t\4\t\5\t\6\t\7\t\8\t\9

　実際にsedコマンドで変換する際には、正規表現も上で確認したものから少しだけ変わります。 表記上の話だけなのですが、フォーマット変換後に使いたい項目はカッコ「()」で囲います。 カッコで囲ったものが変換後の\1、\2、・・・に渡されて9個まで使用できます。 正規表現ではカッコは「\(\)」とエスケープして書かなければなりませんが、 見た目がゴチャゴチャして分かりづらいので、今回は拡張正規表現を使って見た目をシンプルにします。 正規表現では書けないわけではなく見た目をスッキリさせる目的だけです。

# cat access_log_20190901.tmp1 | sed -r 's/^(.*) (.*) .* .* \[(.*)\] "(.*) (.*) .*" (.*) (.*) "(.*)" "(.*)"$/\1\t\2\t\3\t\4\t\5\t\6\t\7\t\8\t\9/g' > access_log_20190901.tmp2

レスポンスサイズのハイフン「-」を「0」に変換

　アクセスログをTSV形式に変換するだけなら上記の処理で終わりですが、 今回はTSV形式に変換した後にPostgreSQLのテーブルに挿入する前提なので、 数値データ型（例えばinteger型）のカラムに数字以外の文字が使われているのは都合が悪いです。 レスポンスサイズを数値データ型にしない手もありますが、それは置いとくことにします。 そこで、7番目のレスポンスサイズの項目がハイフン「-」の場合は「0」に変換する処理を行います。

# cat access_log_20190901.tmp2 | sed -r 's/^(.*)\t(.*)\t(.*)\t(.*)\t(.*)\t(.*)\t-\t(.*)\t(.*)$/\1\t\2\t\3\t\4\t\5\t\6\t0\t\7\t\8/g' > access_log_20190901.tsv

確認

作成されたファイルの確認

　今回の操作ではアクセスログを3ステップでTSV形式に変換しました。 各ステップ毎に別のファイルに結果を出力したので、最終的なTSVファイル以外に 一時ファイルが2つ作成されています。 ディスクに空き領域が少ない場合などは一時ファイルを1つにして上書きするとか、 sedコマンドをつなげて1ステップで終わるようにしても良いかもしれません。 なお、ログのフォーマット確認とレスポンスサイズの変換が必要なければそもそも1ステップで終わりです。

# ls -l access_log_20190901*
-rw-r--r-- 1 ftpuser ftpuser 2454264 Sep  1 15:05 access_log_20190901
-rw-r--r-- 1 root    root    2454264 Sep 30 16:40 access_log_20190901.tmp1
-rw-r--r-- 1 root    root    2236515 Sep 30 16:41 access_log_20190901.tmp2
-rw-r--r-- 1 root    root    2236515 Sep 30 16:44 access_log_20190901.tsv

TSV形式のファイルの中身の確認

　最終的に出来上がったTSV形式のファイルの中身を確認します。

# cat access_log_20190901.tsv
　（前略）
ossfan.net      12-345-67-890f1.shg1.eonet.ne.jp        01/Sep/2019:00:03:17 +0900      GET     /images/nav/nav-news.gif        200     2048    http://ossfan.net/setup/linux-17.html   Mozilla/5.0 (iPhone; CPU iPhone OS 12_4 like Mac OS X) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/12.1.2 Mobile/15E148 Safari/604.1
　（後略）

参考にしたサイト