grep考(2) ＜Excelで検索してみた＞

grep考　＜認識をアップデートする＞ (2025/12/1)
(https://yoshi-s.cocolog-nifty.com/YoshiNashi/2025/11/post-f2a0df.html)

で、「認識とスキルを現状に合わせてアップデートしなければならない」と書いた。
ログを解析する際に、従来から使われている grep より、今時は、Excelの方が良いのではないか、という趣旨だが、
憶測はよくないと思い、本当にExcelで検索してみた。

【結論を先に】
・検索対象が1048576行以下のファイル、読み込み時に1048576行以下に絞り込めるなら、Excelは便利
・grepをインストールするくらいなら、PowerShellで良くネ?

【サンプルデータ】
　ApacheのAccesslog 4837202レコード(1.1GB)
【PC】
　Windows11 RAM32GB (起動時に約18GB使用)
【Excel】
　Microsoft 365 Apps for enterprise
　Ver 2510(Build 19328.20190)

【手順】
「データ」→「テキストまたはCSVから」
「元ファイル：区切り記号」→「スペース」
　・""で括られたデータは分割されない
　・[]で括られたデータは分割されるので、時刻が日時とタイムゾーンに分割される

【制限】
・1シートの最大行は1048576行しか読み込めない
　読込む際にPowerQueryで日付などで絞り込んで 1048576行以下にすることは可能

【その他】
・PowerQueryのMコードで、タイムスタンプをExcelのシリアル値に変換することはできるが、テキストで読み込んでから関数で変換した方が簡単。
・自動的にテーブルに変換されるので、フィールド名(列名)を変更し、テーブル名を分かりやすい名前に変更する(access_log,access_log)と関数で参照する際に分かりやすくなる。

【抽出】
別シートで、抽出先の左上のセルに FILTER関数を入力することで、レコードが抽出される。
例： status列が200のデータを抽出、　2025/6/30 23:59:00～2025/7/1 00:00:59までのデータを抽出する例は、

=FILTER(access_log,access_log[status]=200) 
=FILTER(access_log, REGEXTEST(access_log[time], "(30/Jun/2025:23:59)|(1/Jul/2025:00:00)")) 

【メリット】
時間当たりのページごとのアクセス数の推移などの表やグラフが、Excelの機能を使用すると簡単に作成できる。

ところで、
ファイル内の文字列を正規表現で検索して出力するコマンドレットはPowerShellにもある。

【PowerShell】

Select-String -Pattern '<regex>'  -Path '<File path>'
[-CaseSensitive]
[-NoMatch]
[-Recurse]


PowerShellの正規表現はREPC2だから grep -E '<regex>' と同等の機能だ。
規定値で、Select-Stringは大文字小文字を同一文字として扱い、grepは異なる文字として扱う。
また、grepでよく使用するオプションと同じ機能のオプションスイッチがある。

-CaseSensitive → grep --no-ignore-case
-NoMatch       → grep -v
-Recurse       → grep -r


【オンメモリ処理】
PowerShellでオブジェクトとしてすべてのログをメモリーに読込こんでおくと、検索が速くなるのではないかと考えた。
PowerShellの正規表現で -matchを使用すると、名前付きキャプチャが使えるので、レコードをオブジェクトに変換できる。

$pattern ='^(?<host>\S+)\s+(?<ident>\S+)\s+(?<user>\S+)\s+\[(?<time>[^\]]+)\]\s+"(?<request>[^"]*)"\s+(?<status>\d{3})\s+(?<size>\S+)\s+"(?<referer>[^"]*)"\s+"(?<agent>[^"]*)"\s+"(?<extra>[^"]*)"'
$log = get-content "<File path>" | foreach { $_ -match $pattern; $log.add($matches)}


で変数 $logに全てのデータを読み込むと、

$log | where {$_.status -eq "200"}
$log | where {$_.time -match "(Jun/2025:23:59)|(Jul/2025/00:00)"}

で検索・抽出できる。

一旦すべてのデータをメモリに読み込むので、検索は速くなる…と思いきや、速くならない。
タスクマネージャーでメモリ使用量を見ると、実メモリが足りずスワップしているようだ。
スワップしていると、select-stringでログファイルを毎回検索する方法と時間は変わらない。
(スワップしなければ速いはず）

【結論】
・検索対象が1048576行以下のファイル、読み込み時に1048576行以下に絞り込めるなら、Excelは便利
・grepをインストールするくらいなら、PowerShellで良くネ?

---

• grep考　＜認識をアップデートする＞ (2025/12/1)

---

最近の投稿
【Yoshiのよしなしごと】【Yoshiのブログ】【よしなしごと】【ほぼ1人情シス】