忍者ブログ

アパッチのWEBアクセスログの集計②

2020年01月13日
前回の続き。
step1.フォルダ内の全ファイルを対象としたループを作る
step2.各ファイル内のデータを日付別にシート出力
step3.各シートで、ログデータを区切る
step4.区切られたデータを集計

step1,2は手打ちで何とかなっているので、ひとまずstep3に着手。




ログデータのイメージはこんな感じ。

108.81.70.158 - - [16/Jun/2015:13:59:34 +0000] "GET /item/electronics/3717 HTTP/1.1" 200 86 "-" "Mozilla/5.0 (Windows NT 6.0) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11"
176.33.96.225 - - [16/Jun/2015:13:59:35 +0000] "GET /category/cameras HTTP/1.1" 200 88 "-" "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.56 Safari/535.11"
32.129.29.109 - - [16/Jun/2015:13:59:35 +0000] "GET /item/toys/2278 HTTP/1.1" 200 129 "/search/?c=Toys" "Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.1) Gecko/20100101 Firefox/10.0.1"
サンプルデータ出展元
https://inokara.hateblo.jp/entry/2015/06/21/225143

Apacheのアクセスログの見方を検索。
このページとかを参考に、区切り方法を検討。
http://ossfan.net/setup/httpd-06.html

要するにこういうことか。
IP - - [日時 +時差] "コマンド URL プロトコル" ST Byte "Referer" "UA"

このデータから、IP、日時、コマンド、URL、プロトコル、Referer、UAを取り出したい。
基本はスペース区切りだけど、引用符がついている。


「"」の位置や半角スペースの位置を表計算でゴリゴリ計算させたものの、
10万行を超えるデータじゃ計算がおわらなそうなので断念。

VBAを使って区切りを入れることにした。

最初にスペース区切りは収集がつかなくなるので、split関数を使って「"」で区切り。
VBAで「"」を指定する場合は、「""""」と指定する。

高速化するため、以下を注意した。
  1. エクセルの表計算は使わず、すべて配列上で計算する。
  2. 再帰処理はなるべく行わない。
  3. エクセルへの出力も、出力用配列を用意してまとめてドン。


できたソースはこちら。
Sub アパッチログのスプリット()
Dim tictoc As Double
tictoc = Timer    '開始時と終了時のTimerの差分を取って、ストップウォッチ。

Dim Gyo As Long, GyoEnd As Long
GyoEnd = Cells(Rows.Count, 1).End(xlUp).Row ' 最終行を取得

Dim Arr() As String ' "split出力用
Dim htArr() As String   'H,T split出力用
Dim rArr() As String    'R split出力用

Dim oArr() As String    'セル出力用(動的配列で宣言)
ReDim oArr(GyoEnd - 1, 9)    '動的配列のサイズ変更

Gyo = 1  'タイトル行
    oArr(Gyo - 1, 1) = "IP"
    oArr(Gyo - 1, 2) = "[t]"
    oArr(Gyo - 1, 3) = "Method"
    oArr(Gyo - 1, 4) = "URL"
    oArr(Gyo - 1, 5) = "プロトコル"
    oArr(Gyo - 1, 6) = "ステータス"
    oArr(Gyo - 1, 7) = "その他"
    oArr(Gyo - 1, 8) = "訪問"
    oArr(Gyo - 1, 9) = "UA"
    Application.StatusBar = "配列作成中"    ' これ以上細かいと遅くなる 
For Gyo = 2 To GyoEnd    '1行目はタイトル 2~GyoEndがログデータの行
    Arr = Split(Cells(Gyo, 1), """")    ' "で区切る 出力は「"」の数より1つ多い配列
   
    htArr = Split(Arr(0), " - - [")
    oArr(Gyo - 1, 1) = htArr(0) '1: IP
    oArr(Gyo - 1, 2) = Left(htArr(1), 20)  '2: [t]   +0900までなら26字 不要なら20字 日付のみ11字

    rArr = Split(Arr(1), " ")

    oArr(Gyo - 1, 3) = rArr(0) '3: Method
    oArr(Gyo - 1, 4) = rArr(1) '4: URL
    oArr(Gyo - 1, 5) = rArr(2) '5: プロトコル
    oArr(Gyo - 1, 6) = Mid(Arr(2), 2, 3) '6: ステータス
    oArr(Gyo - 1, 7) = Right(Arr(2), Len(Arr(2)) - 4) '7: その他

    oArr(Gyo - 1, 8) = Arr(3) '8: 訪問
    oArr(Gyo - 1, 9) = Arr(5) '9: UA
Next Gyo
    Application.StatusBar = "配列出力中"
    Cells(1, 2).Resize(GyoEnd, 10).Value = oArr    ' セルに配列を出力
    Application.StatusBar = ""
Debug.Print "[" & Now & "] "; Format(GyoEnd - 1, "#,##0行を") & Format(Timer - tictoc, "0.00秒")
End Sub

実行結果
[2020/01/13 23:03:32] 100,000行を6.95秒
まずまずの結果だけど、力技過ぎて汎用性がないなぁ。


PR
Comment
  Vodafone絵文字 i-mode絵文字 Ezweb絵文字