PostgreSQLでApacheのログ管理

2017-06-20 (火) 23:23:54 (2495d)

　旧サイトからの転載です。元ページはAug 2001頃に書かれました。

---

注）
　Perlスクリプトに若干不具合があります。変なアクセス（ウィルス等）があると上手く動かないことがあります。

---

　apache のログの集計をする方法はいろいろありますが、ログといえどもデータなのだから、普通にデータベースで管理すればいいじゃないかという安易な考えで、PostgreSQL と組み合わせてみました。

必要なソフト †

• FreeBSD 4.3R
• Apache 1.3.20
• PostgreSQL 7.1.3
• perl 5.005_03
• DBI 1.20
• DBD::Pg 1.01

インストール †

　今回新規にインストールしたのは、DBIとDBD::Pgです。両方とも http://www.cpan.org/ からダウンロードできます。

DBI 1.20 †

http://theoryx5.uwinnipeg.ca/scripts/CPAN/authors/id/T/TI/TIMB/DBI-1.20.tar.gz

%perl Makefile.PL
%make
%make test
#make install

DBD::Pg †

　DBI::Pg では無いので注意。

%su postgres
%perl Makefile.PL
%make
%make test
#su
#make install

　ユーザ postgres である必要はありませんが、

setenv POSTGRES_INCLUDE /usr/local/pgsql/include
setenv POSTGRES_LIB /usr/local/pgsql/lib
ldconfig /usr/local/pgsql/lib

のような設定がされていて、さらにデータベースにアクセスする権限が必要です。

Apacheでのログ設定 †

　Apache のログを PostgreSQL に登録するための方法は、

• ページ単位でCGIやSSIとして１つずつ登録する
• Apacheのログ機能を使って１つずつ登録する
• Apacheのログを定期的に登録する
  あたりが考えられます。1はユーザがログを取りたいときには有効でしょうが、システムでやるのは無駄が多すぎます。２はリアルタイムでデータベースに反映できる利点がありますが、パフォーマンスを落とさないようにするためにはもう一手間かかりそうです。３ではリアルタイムには反映できませんが、この辺が妥協点のような気がします。
  　Apacheのカスタムログを１日単位で収集して、まとめて登録することにします。 　

httpd.conf †

　カスタムログの設定をします。

LogFormat "%h %a %u %t %m \"%U\" \"%q\" %H %>s %B %{Referer}i %{User-Agent}i" mycombined
CustomLog /usr/local/apache/logs/myaccess_log mycombined

%a リモートIPアドレス
%B 転送量
%f ファイル名
%h リモートホスト
%H リクエストプロトコル
%m リクエストメソッド
%q クエリストリング
%s ステータス。内部でのリダイレクトリクエストの場合、オリジナルステータス。
　　%>sで最終リクエストのステータス。
%r リクエストの最初の行（リクエストライン）。つまり、GET ... HTTP/1.1 とかのこと。
%t %{format}t 時刻
%u ユーザ
%U リクエストURL
%v リクエストのサーバ名
%{foobar}i リクエストの特定の行 i.e. %{Referer}i,%{User-Agent}i

リクエストライン（%r） は、Method SP Request-URI SP HTTP-Version CRLF　のことです。
これが、 %m %U %q %H　に分解されます。

newsyslog.conf
　newsyslog を使って１日ごとにログをまとめます。あとで使うことも考えて圧縮はしません。１ヶ月分は保存するようにしましたが、どうせデータベースに登録してしまうので、１日分だけでも大丈夫です。

/usr/local/apache/logs/myaccess_log 644 31 * $D0 -/var/run/httpd.pid 30

PostgreSQLのデータベースを作る †

　hostname と hostip は組み合わせようかと思いましたが、アドレスに変更があったときやDNSで解決できなかったときに問題がありそうなのでやめました。

%/usr/local/pgsql/bin/createdb accesslog
%/usr/local/pgsql/bin/psql accesslog
accesslog=# \i accesslog.sql
accesslog=# \q

accesslog.sql の内容

CREATE TABLE method (
  id INTEGER PRIMARY KEY,
  method TEXT UNIQUE
);
CREATE TABLE requrl (
  id INTEGER PRIMARY KEY,
  requrl TEXT UNIQUE
);
CREATE TABLE reqquery (
  id INTEGER PRIMARY KEY,
  reqquery TEXT UNIQUE
);
CREATE TABLE reqprot (
  id INTEGER PRIMARY KEY,
  reqprot TEXT UNIQUE
);
CREATE TABLE referer (
  id INTEGER PRIMARY KEY,
  referer TEXT UNIQUE
);
CREATE TABLE useragent (
  id INTEGER PRIMARY KEY,
  useragent TEXT UNIQUE
);
CREATE TABLE hostname (
  id INTEGER PRIMARY KEY,
  hostname TEXT UNIQUE
);
CREATE TABLE username (
  id INTEGER PRIMARY KEY,
  username TEXT UNIQUE
);

CREATE TABLE log (
  id INTEGER PRIMARY KEY,
  hostname INTEGER,
  hostip VARCHAR(16),
  username INTEGER,
  accessdate TIMESTAMP,
  reqmethod INTEGER,
  requrl INTEGER,
  reqquery INTEGER,
  reqprot INTEGER,
  resultcode INTEGER,
  transbyte INTEGER,
  referer INTEGER,
  useragent INTEGER
);

登録するスクリプト †

　長いので別ファイルで置きました。Perlのスクリプトです。aplog2db.pl

　データベースサーバに目的のユーザでログイン済みであることを想定しているので、DBI->connect でユーザ名・パスワード名は空になっています。

統計を取る †

　SQLで適当にやってください。PHPなんかでも楽しそうです。

　たとえば、URL毎にどれだけのアクセスがあるか集計したいなら

SELECT (SELECT url from url where url.id = requrl), count(*) from log group by requrl;

で出来ますし、Referer を列挙したいなら

SELECT referer FROM referer GROUP BY referer;

で出来ます。グラフ表示等をするには、手動ならば直接SQLを書いて出力をファイルに出して、それを編集してグラフ表示用のツールに読ませるのが楽でしょう。自動化する場合は、やっぱりPHPでしょうか。

バグ？ †

　リクエストURLのテーブルに 'HTTP/1.0' とか '-' が入っているかもしれません。一見、ログがずれているように見えますが、違います。本当にそういうURLを送ってくるクライアントが居るのです！