SQLiteでApacheのログを解析する ～Delphiソースコード集
SQLiteは1つのDLLファイルだけでローカルデータベースとして動作するのでとても便利です。
これを使ってApacheのログを簡易に解析してみます。
プロジェクトの作成と画面設計
プロジェクトを新規作成（VCLアプリケーション）します。
フォーム(Form1)上に、
TOpenDialog×１個、 TFDConnection×１個、 TFDPhysSQLiteDriverLink×１個、 TFDQuery×２個、
TFDTable×１個、 TDataSource×１個、 TDBGrid×１個、 TChart×１個、 TButton×４個
を以下のようにドラッグ＆ドロップで配置配置します。
「すべて保存」をクリックして、任意のディレクトリを作成し、ユニット(Unit1.pas)とプロジェクト(Project1.dproj)を保存します。
SQLiteのダウンロードと配置
https://www.sqlite.org/download.html
から32Bit版「sqlite-dll-win32-x86-3430100.zip(580.14 KiB)」(最新バージョンでOKです)を
ダウンロードして解凍し「sqlite3.dll」ファイルを
上記で作成したプロジェクトディレクトリの「\Win32\Debug」と「\Win32\Release」に配置します。
64Bit版も必要な場合は「sqlite-dll-win64-x64-3430100.zip(1.18 MiB)」(最新バージョンでOKです)を
ダウンロードして解凍し「sqlite3.dll」ファイルを
上記で作成したプロジェクトディレクトリの「\Win64\Debug」と「\Win64\Release」に配置します。
ソースコードの記述
Form1.OnCreate、Button1.OnClick、Button2.OnClick、Button3.OnClick、Button4.OnClickのイベントプロパティなどに以下ソースコードを記述します。
unit Unit1;
interface
uses
Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
Vcl.Controls, Vcl.Forms, Vcl.Dialogs, FireDAC.Stan.Intf, FireDAC.Stan.Option,
FireDAC.Stan.Error, FireDAC.UI.Intf, FireDAC.Phys.Intf, FireDAC.Stan.Def,
FireDAC.Stan.Pool, FireDAC.Stan.Async, FireDAC.Phys, FireDAC.VCLUI.Wait,
Data.DB, FireDAC.Comp.Client, FireDAC.Stan.ExprFuncs, FireDAC.Phys.SQLiteDef,
FireDAC.Phys.SQLite, FireDAC.Stan.Param, FireDAC.DatS, FireDAC.DApt.Intf,
FireDAC.DApt, FireDAC.Comp.DataSet, Vcl.StdCtrls, Vcl.Grids, Vcl.DBGrids,
VclTee.TeeGDIPlus, VCLTee.TeEngine, Vcl.ExtCtrls, VCLTee.TeeProcs,
VCLTee.Chart, VCLTee.Series;
type
TForm1 = class(TForm)
OpenDialog1: TOpenDialog;
FDConnection1: TFDConnection;
FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink;
FDQuery1: TFDQuery;
FDQuery2: TFDQuery;
FDTable1: TFDTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Chart1: TChart;
Button1: TButton;
Button2: TButton;
Button3: TButton;
Button4: TButton;
procedure FormCreate(Sender: TObject);
procedure Button1Click(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
procedure Button4Click(Sender: TObject);
private
{ Private 宣言 }
public
{ Public 宣言 }
end;
var
Form1: TForm1;
implementation
{$R *.dfm}
function getYMDH(d:String):TDateTime;
const
MonthArr:array[0..11] of string=
('Jan','Feb','Mar','Apr','May','Jun',
'Jul','Aug','Sep','Oct','Nov','Dec');
var
v:Array Of String;
i:Integer;
yyyy,mm,dd:Integer;
hh,nn,ss:Integer;
begin
// '17/Sep/2023:04:03:42'のような文字列を 日付型に変換する
v:=d.Split(['/']);
yyyy:=StrToInt(v[2].Substring(0,4));
mm:=1;
for i := Low(MonthArr) to High(MonthArr) do
begin
if MonthArr[i]=v[1] then
begin
mm:=i+1;
break;
end;
end;
dd:=strtoint(v[0]);
hh:=StrToInt(d.Substring(12,2));
nn:=StrToInt(d.Substring(15,2));
ss:=StrToInt(d.Substring(18,2));
Result:=EncodeDate(yyyy,mm,dd)+EncodeTime(hh,nn,ss,0);
end;
procedure TForm1.Button1Click(Sender: TObject);
var st,st2,st3:String;
i,j:Integer;
stl1,stl2:TStringList;
dt:TDateTime;
begin
//ApacheのCombined Logの読み込み
st:=OpenDialog1.FileName;
OpenDialog1.InitialDir:=ExtractFileDir(st);
OpenDialog1.FileName:=ExtractFileName(st);
if not OpenDialog1.Execute then exit;
stl1:=TStringList.Create;
stl2:=TStringList.Create;
try
stl2.StrictDelimiter:=True;
stl2.Delimiter:=' ';
stl2.QuoteChar:='"';
for i:=0 to OpenDialog1.Files.Count-1 do
begin
stl1.LoadFromFile(OpenDialog1.Files[i]);
for j:=0 to stl1.Count-1 do
begin
stl2.DelimitedText:=stl1[j];
//ステータスコードが300未満のみ取り込む
if StrToIntDef(stl2[6],200)<300 then
begin
//UserAgentがクローラーロボットらしきログは取り込まない
if (Pos('http',LowerCase(stl2[9]))=0) and
(Pos('bot',LowerCase(stl2[9]))=0) and
(Pos('mediapartners-google',LowerCase(stl2[9]))=0) and
(Pos('crawler',LowerCase(stl2[9]))=0) and
(Pos('spider',LowerCase(stl2[9]))=0) then
begin
//URLの拡張子確認
st:=stl2[5].Split([' '])[1];
st2:=st.Split(['?'])[0];
st3:=LowerCase(ExtractFileExt(st2));
//解析対象を.html又は.php又は/のみにする
if (st3='.html') or (st3='.php')or (st2.Substring(Length(st2)-1)='/') then
begin
dt:=getYMDH(stl2[3].Substring(1,20));
FDQuery1.ExecSQL(
'INSERT INTO t_log(ip,date,hour,min,url,status,referer,agent)'+
' VALUES(:ip,:date,:hour,:min,:url,:status,:referer,:agent)',
[
stl2[0],
FormatDateTime('yyyy/mm/dd',dt),
FormatDateTime('hh',dt),
FormatDateTime('nn',dt),
stl2[5].Split([' '])[1],
StrToIntDef(stl2[6],200),
stl2[8], stl2[9]
],
[
ftString,ftString,
ftInteger,ftInteger,
ftString,ftString,ftString
]
);
end;
end;
end;
end;
end;
finally
stl1.Free;
stl2.Free;
end;
FDTable1.Refresh;
end;
procedure TForm1.Button2Click(Sender: TObject);
begin
//全期間の日別ビュー数をグラフ表示する
Chart1.Title.Text.Text:='日別ビュー数';
if FDQuery2.Active then FDQuery2.Active:=False;
FDQuery2.SQL.Text:=
'SELECT date,COUNT(date) AS ct FROM t_log GROUP BY date ORDER BY date';
FDQuery2.Active:=True;
Chart1.Series[0].Clear;
//棒グラフ1のXの値を日時に設定する
Chart1.Series[0].XValues.DateTime:=True;
FDQuery2.First;
while not FDQuery2.Eof do
begin
Chart1.Series[0].AddXY(
StrToDate(FDQuery2.Fields[0].AsString),FDQuery2.Fields[1].AsInteger
);
FDQuery2.Next;
end;
FDQuery2.Active:=False;
end;
procedure TForm1.Button3Click(Sender: TObject);
begin
//全期間のURL別ビュー数をグラフ表示する
Chart1.Title.Text.Text:='全期間のURL別ビュー数';
if FDQuery2.Active then FDQuery2.Active:=False;
FDQuery2.SQL.Text:=
'SELECT url,COUNT(url) AS ct FROM t_log GROUP BY url ORDER BY ct DESC';
FDQuery2.Active:=True;
Chart1.Series[0].Clear;
//棒グラフ1のXの値を日時に設定する
Chart1.Series[0].XValues.DateTime:=False;
FDQuery2.First;
while not FDQuery2.Eof do
begin
Chart1.Series[0].Add(
FDQuery2.Fields[1].AsInteger,
FDQuery2.Fields[0].AsString
);
FDQuery2.Next;
end;
FDQuery2.Active:=False;
end;
procedure TForm1.Button4Click(Sender: TObject);
begin
//IPアドレスとエージェントを一意なユーザーとして日別ユーザー数をグラフ表示する
Chart1.Title.Text.Text:='日別訪問ユーザー数';
if FDQuery2.Active then FDQuery2.Active:=False;
FDQuery2.SQL.Text:=
'SELECT date,COUNT(date) AS ct FROM('+
'SELECT date,ip,agent AS ct FROM t_log GROUP BY ip,agent'+
') AS tb '+
' GROUP BY date ORDER BY date';
FDQuery2.Active:=True;
Chart1.Series[0].Clear;
//棒グラフ1のXの値を日時に設定する
Chart1.Series[0].XValues.DateTime:=False;
FDQuery2.First;
while not FDQuery2.Eof do
begin
Chart1.Series[0].Add(
FDQuery2.Fields[1].AsInteger,
FDQuery2.Fields[0].AsString
);
FDQuery2.Next;
end;
FDQuery2.Active:=False;
end;
procedure TForm1.FormCreate(Sender: TObject);
var ct:Integer;
i:Integer;
series:TCustomSeries;
begin
FDConnection1.DriverName:='SQLite';
FDConnection1.Params.Clear;
FDConnection1.Params.Text:=
'Database=apache.sdb'+#13#10+
'LockingMode=Normal'+#13#10+
'DriverID=SQLite';
FDConnection1.Connected:=True;
FDQuery1.Connection:=FDConnection1;
//t_logテーブルがあるかどうか調べる
FDQuery1.Open(
'SELECT COUNT(*) AS ct FROM sqlite_master WHERE tbl_name=''t_log'''
);
ct:=FDQuery1.FieldByName('ct').AsInteger;
FDQuery1.Close;
if ct=0 then
begin
//t_logテーブルがないので作成する
FDQuery1.ExecSQL(
'CREATE TABLE t_log('+
' ip TEXT, date TEXT, hour INTEGER,'+
' min INTEGER, url TEXT, status INTEGER,'+
' referer TEXT, agent TEXT'+
')'
);
end;
FDQuery2.Connection:=FDConnection1;
FDTable1.Connection:=FDConnection1;
FDTable1.TableName:='t_log';
DataSource1.DataSet:=FDTable1;
DBGrid1.DataSource:=DataSource1;
FDTable1.Open();
for i:=0 to FDTable1.FieldCount-1 do
begin
if FDTable1.Fields[i].IsBlob then
TBlobField(FDTable1.Fields[i]).DisplayValue:=dvFull;
if (i=0) then //IP
DBGrid1.Columns[i].Width:=-Form1.Font.Height*8
else if (i=1) then //Date
DBGrid1.Columns[i].Width:=-Form1.Font.Height*6
else if (i=2) or (i=3) then
DBGrid1.Columns[i].Width:=trunc(-Form1.Font.Height*2.5)
else if (i=4) then //URL
DBGrid1.Columns[i].Width:=-Form1.Font.Height*15
else if (i=5) then //Status
DBGrid1.Columns[i].Width:=trunc(-Form1.Font.Height*3.5)
else
DBGrid1.Columns[i].Width:=-Form1.Font.Height*12;
end;
OpenDialog1.Filter:='Apache Log|*.log';
OpenDialog1.Options:=OpenDialog1.Options+[ofAllowMultiSelect];
series:=TLineSeries.Create(Chart1);
Chart1.AddSeries(series);
end;
end.
実行する
実行ボタンを押して実行します。（デバッグ実行でもOK）
Apacheのログを取り込む
「Button1」ボタンを押して、Apacheのログファイルを選択し、開くボタンを押します。
全期間の日別ビュー数をグラフ表示する
「Button2」をクリックすると日別のビュー数がグラフ表示されます。
全期間のURL別ビュー数をグラフ表示する
「Button3」をクリックするとURL別ビュー数がグラフ表示されます。
日別ユーザー数をグラフ表示する
「Button4」をクリックすると、 IPアドレスとエージェントを一意なユーザーとして日別ユーザー数がグラフ表示されます。
