忍者ブログ

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

2020年01月14日
アパッチのWEBアクセスログをエクセルVBAで集計したい。
step1．フォルダ内の全ファイルを対象としたループを作る
step2．各ファイル内のデータを日付別にシート出力
step3．各シートで、ログデータを区切る
step4．区切られたデータを集計

前回step3を力技でやったけど、納得いかなかったので作り直した。

元々スペースで区切られたデータがあって、""や[]で挟まれたデータ内では
スペースで区切りたくないというのがやりたいこと。

検索するとこんなページなどはあるんだけど・・・。
https://tonari-it.com/vba-csv-double-quotation/

置換！！！

引用符の中のスペースは別の文字（〓とか）に置換しておいて、
区切った後に戻せばいいのか。

ってことで作ったソースがこちら。

Sub アパッチログのスプリットFixed()
Dim tictoc As Double
tictoc = Timer
Dim Gyo As Long, GyoEnd As Long, i As Long
GyoEnd = Cells(Rows.Count, 1).End(xlUp).Row
Dim oiArr() As String ' "split出力用
Dim tmpArr() As String
Application.StatusBar = "配列作成中"
tmpArr = Text2tmpArr2(Cells(2, 1))   '列数確認のために2行目を処理（見易さ優先）
Dim oArr() As String    'セル出力用
ReDim oArr(GyoEnd - 1, UBound(tmpArr))
Gyo = 1  'タイトル行
    oArr(Gyo - 1, 0) = "IP"
    oArr(Gyo - 1, 1) = "--"
    oArr(Gyo - 1, 2) = "--"
    oArr(Gyo - 1, 3) = "[t]"
    oArr(Gyo - 1, 4) = "Method"
    oArr(Gyo - 1, 5) = "URL"
    oArr(Gyo - 1, 6) = "プロトコル"
    oArr(Gyo - 1, 7) = "ステータス"
    oArr(Gyo - 1, 8) = "その他"
    oArr(Gyo - 1, 9) = "訪問"
    oArr(Gyo - 1, 10) = "UA"
For Gyo = 2 To GyoEnd
'関数呼び出しでtmpArr
    tmpArr = Text2tmpArr2(Cells(Gyo, 1))  '  速度が遅いけど、保守性を重視
    For i = 0 To UBound(tmpArr)
        tmpArr(i) = Replace(tmpArr(i), "〓", " ")
        oArr(Gyo - 1, i) = tmpArr(i)    '出力配列に格納
    Next i
Next Gyo
    Application.StatusBar = "配列出力中"
    Cells(1, 3).Resize(GyoEnd, UBound(tmpArr) + 1).Value = oArr
    Application.StatusBar = ""
Debug.Print "[" & Now & "] "; Format(GyoEnd - 1, "#,##0行を") & Format(Timer - tictoc, "0.00秒")
End Sub

Function Text2tmpArr2(TextLine As String)
    Dim oiArr() As String
    Dim tmpArr() As String
    Dim i As Long
    ''[t]を"t"に置換
    ''"splitでOI配列を作る
    oiArr = Split(Replace(Replace(TextLine, "[", """"), "]", """"), """")
    ''Iに対して､SPを〓に置換
    For i = 1 To UBound(oiArr) Step 2
        If i <> 3 Then  'oiArr(3)は区切るので例外（method URL Protcol）
            oiArr(i) = Replace(oiArr(i), " ", "〓")
        End If
    Next i
    ''OI配列を1行データに戻す
    ''SP splitで配列に
    tmpArr = Split(Join(oiArr, ""), " ")
    Text2tmpArr2 = tmpArr
End Function

ソースに関するコメント
  1. []を""と同様に処理するために置換
  2. 「"」でsplit。 要素数が奇数（０から始まるので）なら"の内側
  3. 内側のスペースを「〓」に置換
  4. 配列を１行のデータに戻す
  5. 本命のスペースでsplit
  6. 「〓」をスペースに戻す
  7. 出力配列に格納
  8. １～５をText2tmpArr2 関数で処理
  9. 出力配列のサイズがわからないので、1つ目のログを処理してから決定
結果
[2020/01/13 23:37:25] 100,000行を10.21秒
  • 汎用性を持たせた結果、遅くなったｗ
  • 各行での処理を関数にまとめて見やすくなったが、２５％くらい遅くなった
（これを作っていたときは、比較対象のモジュールに不要なDoEventsが残っていたのでこちらのほうが速かった。）

    PR
    Category・VBA | Comments(0)

    アパッチの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を取り出したい。
    基本はスペース区切りだけど、引用符がついている。

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

    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), """")    ' "で区切る　出力は「"」の数より１つ多い配列
       
        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秒
    まずまずの結果だけど、力技過ぎて汎用性がないなぁ。


    Category・VBA | Comments(0)

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

    2020年01月13日
     
    アパッチのWEBアクセスログの集計をエクセルでやりたい。
    対象のデータが膨大で、手作業では時間がかかる。
    過去に作ったツールも重くて動かないので新たに集計ツールを作ることにした。
    １．やりたいこと
    以下の形式のログデータからURL内の文字列を日付ごとに集計する。SVは３台あるけど、合算した件数を対象としたい。
    対象ログデータ
    ログファイル
    以下のURLよりサンプルを拝借。
    https://inokara.hateblo.jp/entry/2015/06/21/225143
    ２．やりかた
    以下のステップで処理。開発は逆順で。
    step1．フォルダ内の全ファイルを対象としたループを作る
    step2．各ファイル内のデータを日付別にシート出力
    step3．各シートで、ログデータを区切る
    step4．区切られたデータを集計

    ３．実装
    stepごとに別の記事で。
    Category・VBA | Comments(0)
    « 前のページ | HOME |