SQLiteでApacheのログを解析する ~Delphiソースコード集
SQLiteは1つのDLLファイルだけでローカルデータベースとして動作するのでとても便利です。
これを使ってApacheのログを簡易に解析してみます。
プロジェクトの作成と画面設計
プロジェクトを新規作成(VCLアプリケーション)します。
フォーム(Form1)上に、
TOpenDialog×1個、 TFDConnection×1個、 TFDPhysSQLiteDriverLink×1個、 TFDQuery×2個、
TFDTable×1個、 TDataSource×1個、 TDBGrid×1個、 TChart×1個、 TButton×4個
を以下のようにドラッグ&ドロップで配置配置します。
「すべて保存」をクリックして、任意のディレクトリを作成し、ユニット(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アドレスとエージェントを一意なユーザーとして日別ユーザー数がグラフ表示されます。
