[道具箱]アドホックなアクセスログの集計

説明

Webサーバの移行を行う際に、「利用機能・利用していない機能の洗い出し」、「DNS切り替り後のアクセス有無の確認」に通常アクセスログを利用する。ただし、生ログをOSで見ている作業は分析とはいい難い。かといって既存システムにFluentdを導入して、Elasticsearchに流し込むような作業もしたくない。そんな場合は、自分のPCにSQL ServerとPythonをインストールしておけば、簡単にアドホックな分析を行う事ができる。

免責事項

著者の使い慣れたツールの組み合わせの紹介であり、他にももっといい方法があるかもしれないし、データ量が多い場合などはもっと別の方法があるだろう。また、最近はFluentdなどを利用してログ集約を行う事が多く、LTSV形式でログ出力するケースも増えてきた。タグの付いたレコードを全てvarchar型で取り込んでの集計もできるが、そういう環境ではすでにログ基盤が整備されているため、この手法を使う必要もないだろう。

使用ツール

  • SQL ServerのDeveloper Edition
  • Python 3
  • bcpユーティリティ

手順

  • Webサーバのアクセスログを自分のPCにすべてダウンロードする。(同一ディレクトリ内にローテートされたファイルが入っていると想定)

  • アクセスログに出力している項目に合わせて、SQL Serverにテーブルを作成する。一時的な利用であるため、ここで精緻なデータ型や桁数の設計は考えずに、全てvarchar型で取り込んでしまって問題ないだろう。

  • 1日分のレコードをbcpコマンドで取り込んでみて動作確認を行う。一発で取り込めることはまれである。生ログ自体がタブであるべきはずのところがスペースであったり、改行コードがLFだったり、サーバ毎に出力するカラム数が違ったり、きっと何かが発生するだろう。ここで、テーブルのカラムとbcpのコマンドオプションを微修正していく。bcpのオプションで区切り文字を指定するのが、-tオプション、改行コードを指定するのが-rオプションである。

①区切り文字がスペース、改行文字がCRLFの場合
bcp テーブル名 in C:/Users/user1/Desktop/IISアクセスログ/*.log -T -c -t" "

②区切り文字がタブ、改行文字コードがLFの場合
bcp テーブル名 in C:/Users/user1/Desktop/nginxアクセスログ/*.log -T -c -t"\t" -r"\r"
  • bcpでインポートを行う。ここの効率化にPythonを利用。
import os, glob
dir = 'C:/Users/user1/Desktop/IISアクセスログ/W3SVC1/*.log'
db = 'ACCESSLOG.dbo.w3svc1'
for file_path in glob.glob(dir):
cmd = 'bcp '+ db + ' in '+ file_path + ' -T -c -t" " '
check = os.system(cmd)
  • SQLで自由に解析する。
SELECT [cs-uri-stem] ,COUNT([cs-uri-stem]) AS total
FROM [ACCESSLOG].[dbo].[w3svc1]
WHERE date LIKE '2018%' AND [sc-status] = '200' AND [cs-uri-stem] LIKE '%.asp'
GROUP BY [cs-uri-stem]
ORDER BY total DESC

まとめ

前日分のログがgzで圧縮されていたりして、前処理が必要だったりもするが、簡単なSQL、Python、Linuxコマンドを押さえておけば、日頃の業務を効率化できるだろう。