SQL Server を使用して Web ログを解析する方法

概要

Internet Information Server およびインターネット インフォメーション サービス (以下 IIS) には、データを Web ログの形で収集するための多数の形式が用意されています。トラフィックの多いサイトでは、これらのテキスト ベースのフラットなファイルは、内容が見づらいために無視される傾向にあります。データが見やすいほど、これらのログは管理者や Web マスターにとってより貴重な資源となります。


この資料では、W3C (World Wide Web Consortium) 拡張ログ形式の IIS のログを Microsoft SQL Server にインポートして、IIS のログ ファイルを見やすくする方法を説明します。ここで説明する手法は、その他の形式のログ ファイル用に変更して使用することもできます。

詳細

Web ログは、RFC 2616「Hypertext Transfer Protocol -- HTTP/1.1」 (http://www.rfc-editor.org/rfc/rfc2616.txt) により指定された区切り記号付きテキスト ファイルです。


W3C 拡張ログ形式では、フィールドは、それ自体で意味がわかるようになっています。[date] と [time] は日付と時刻、[c-ip] はクライアントの IP アドレス、[cs-method] は処理した要求の HTTP メソッド、[cs-uri-stem] は要求されたドキュメント、[cs-uri-query] はログに記録される要求の一部として送信されたクエリ文字列、[sc-status] はサーバーにより返されたステータス コード、[sc-bytes] はユーザーに返されたバイト数、[time-taken] はサーバーが要求の処理を完了させるまでに要した時間 (ミリ秒)、[cs(Cookie)] は cookie または要求に入っている固定データ、[cs(Referer)] はユーザーが 1 つ前に訪れたサイトの URL です。W3C 拡張ログ形式では、他にも選択できる多くのフィールドがあります。これらについては、IIS のヘルプおよび次のマイクロソフト Web サイトを参照してください。

ログの形式は次のとおりです。


date
time
c-ip
cs-method
cs-uri-stem
cs-uri-query
sc-status
sc-bytes
time-taken
cs(User-Agent)
cs(Cookie)
cs(Referrer)


ログ ファイルのヘッダーは、Web サイトの [プロパティ] の [Web サイト] タブで選択されているフィールドに対応し、W3C 拡張ログの場合は [拡張プロパティ] タブで選択されているフィールドに対応します。Web ログが既に Microsoft SQL Server のテーブルに保存されている場合、ODBC ログ収集が使用されているためと思われます。しかし、ODBC ログ収集を使用する場合、フィールドは構成できません。IIS のヘルプには、ODBC のログ収集をセットアップする操作手順が記載されており、Logtemp.sql を使用して、ログに対応した構造でテーブルを作成する方法について説明しています。


Enterprise Manager を使用してテーブルを作成することもできますが、作業時間を短縮したり、処理の自動化に役立てたりする場合には、Enterprise Manager ではなくクエリ アナライザで次のようなスクリプトを実行して、テーブルを作成します。

CREATE TABLE [dbo].[tablename] (
[date] [datetime] NULL,
[time] [datetime] NULL ,
[c-ip] [varchar] (50) NULL ,
[cs-method] [varchar] (50) NULL ,
[cs-uri-stem] [varchar] (255) NULL ,
[cs-uri-query] [varchar] (2048) NULL ,
[sc-status] [int] NULL ,
[sc-bytes] [int] NULL ,
[time-taken] [int] NULL ,
[cs(User-Agent)] [varchar] (255) NULL ,
[cs(Cookie)] [varchar] (2048) NULL ,
[cs(Referer)] [varchar] (2048) NULL
)

これらのフィールドの中には、かなり大きなものや、ログ ファイルで調査する内容によっては必要ないものもあります。


テーブルが作成された後は、インポート ウィザードを使用して *.log ファイルをデータベースとテーブルにマッピングすることで、データをインポートできます。


ウィザードを使用すると、操作手順が単調で面倒になりますが、次のコードを使用すると、Web ログを効率的にインポートできます。

BULK INSERT [dbo].[tablename] FROM 'c:\weblog.log'
WITH (
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
)

一括挿入は、"#" で始まる行がログにあると失敗するため、注意してください。Web ログの場合、最初の 4 行、およびサーバーが停止して起動したときの行 (サービスが再起動されるとヘッダー行が書き込まれるため) がこれに該当します。次の「サポート技術情報」 (Microsoft Knowledge Base) に、これらの行を削除し、SQL Server に一括挿入できるようにログを前処理するためのユーティリティとソース コードがあります。

296093
[IIS] FILE: SQL 一括挿入用の IIS ログを準備するための PrepWebLog ユーティリティ
インポートが完了したら、クエリ アナライザでクエリを実行してデータを整理できます。次に例を示します。

Select [cs-uri-stem], [time-taken] from tablename where [time-taken] >= 20000 and time between '1899-12-30 16:30:00.000' and '1899-12-30 17:30:00.000' 
このクエリでは、処理時間が 20 秒間を超え、4:30 ~ 5:30 PM の間に処理されたすべてのページの名前が、それぞれの処理時間 (ミリ秒) と共に表示されます。


Web ログはデフォルトでグリニッジ標準時で記録されます。このため、ログがローカル時刻で記録されるように変更していない限り、時刻はローカル時刻に読み替える必要があります。


サンプル クエリをもう 1 つ示します。
Select distinct [cs-uri-stem], [time-taken] from tablename where [time-taken] > (select avg([time-taken]) from tablename)order by [time-taken] desc
このクエリは、ログ ファイル内の各エントリの [time-taken] の平均値を算出し、[cs-uri-stem] と [time-taken] を選択し、[time-taken] の降順で並べ替えます。"time between" 句を追加すれば、このクエリの結果を分離することができます。

関連情報

: 一括挿入は、SQL Server 2000 では行えますが、SQL Server 7.0 では行えません。
関連情報を参照するには、以下の「サポート技術情報」 (Microsoft Knowledge Base) をクリックしてください。

272292 [BUG] numeric 型または decimal 型の列を含むテーブルに一括挿入するとエラー 7399 が発生する
IIS ログの LocalTimeRollover および LogFileLocaltimeRollover の設定値の詳細については、次の URL を参照してください。

関連情報

この資料は米国 Microsoft Corporation から提供されている Knowledge Base の Article ID 296085 (最終更新日 2004-07-13) を基に作成したものです。


この資料に含まれているサンプル コード/プログラムは英語版を前提に書かれたものをありのままに記述しており、日本語環境での動作は確認されておりません。
プロパティ

文書番号:296085 - 最終更新日: 2005/09/28 - リビジョン: 1

フィードバック