検索
特集／連載

Excelの「XLOOKUP」でログの生データを「人間が読める情報」に変換：ログ分析で“最も面倒な”作業を効率化

各種セキュリティ製品から収集するログ情報をどう「読む」かが、セキュリティを強化する上での鍵を握る。Excelの「XLOOKUP関数」を使ってログ情報を読みやすくするには。

[TechTargetジャパン] PC用表示 関連情報
Share
Tweet
LINE
Hatena

関連キーワード

サイバー攻撃 | マルウェア | セキュリティ


　ファイアウォールや「EDR」（Endpoint Detection and Response）ツールなど、セキュリティ製品のログ情報には多くの場合、IPアドレスや端末名、ユーザーID、イベントコードポート番号といった「生データ」だけが入っている。しかし、セキュリティ担当者が調査したいのは、「誰の端末か」や「ユーザーは退職済か」「通信先は不正なものか」などの“ログ情報の意味付け”だ。

　実は「Microsoft Excel」の「XLOOKUP」関数を使えば、セキュリティログの生データを「人間が読める情報」に変えることができる。本稿はそのための具体的な手順を簡潔に紹介する。

XLOOKUP関数でログ情報に意味付け　その方法とは

　XLOOKUPは、指定した「検索範囲」の中から検索値を探し、それと同じ行にある値を「戻り範囲」（結果として表示させたい列）から返す関数だ。基本構造は以下の通りで、覚えておくと便利だろう。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合)


　では、XLOOKUPを使ってセキュリティのログ情報に意味付けする手順を、ケースごとに見てみよう。

　今回は、データを表として扱いやすくするために、Excelの「テーブル化」機能を使う。テーブル化によって、行や列を追加した際に自動で書式が適用されたり、1つのセルに数式を入力すると他のセルにも自動で反映されたりする。表をテーブル化する方法は以下の通りだ。

  • テーブル化したい範囲を選択
  • 「Ctrl＋T」を押す
  • 「テーブルの作成」ダイアログで
    • 範囲が正しいかを確認
    • 「先頭行をテーブルの見出しとして使用する」にチェック
  • 「OK」をクリック

ケース1：IPアドレスからデバイス所有者（ユーザー、部署）を特定

　このケースでは、以下の2つのシートが同一のExcelファイル内に存在することを前提とする。

  1. EDRなどのログ情報（シート名：「ログ情報」）
    • セキュリティ製品ごとにログ情報を管理するシート。
    • タイムスタンプ（A列）、IPアドレス（B列）、「マルウェア発見」といったイベント（C列）を含む。
  2. IPアドレス台帳（シート名：「IP台帳」）
    • IPアドレス（A列）、ホスト名（B列）、所有者（C列）、部署（D列）を含む。
  • 所有者を取得するXLOOKUP式

=XLOOKUP([@IP],IP台帳!A:A,IP台帳!C:C,"不明")


  • 部署を取得するXLOOKUP式

=XLOOKUP([@IP],IP台帳!A:A,IP台帳!D:D,"不明")


　これらの式を使うことによって、ログの行に「誰の端末で起きたアラートか」が自動で表示される。そのため、IPアドレスを見て台帳を手探り検索する作業をなくし、調査の速度を上げられる。

ケース2：ユーザーIDから氏名、部署、管理者権限を表示

　前提となる2つのシートは以下の通りだ。

  1. ログ情報（シート名：「ログ情報」）
    • タイムスタンプ（A列）、ユーザーID（B列）、イベント（C列）を含む。
  2. ユーザー台帳（シート名：「AD一覧」）
    • ユーザーID（A列）、氏名（B列）、部署（C列）、「一般」や「管理者」といった権限（D列）を含む。
  • 氏名を表示させるXLOOKUP式

=XLOOKUP([@UserID],AD一覧!A:A,AD一覧!B:B,"不明")


  • 部署を表示させるXLOOKUP式

=XLOOKUP([@UserID],AD一覧!A:A,AD一覧!C:C,"不明")


  • 権限を表示させるXLOOKUP式

=XLOOKUP([@UserID],AD一覧!A:A,AD一覧!D:D,"不明")


　管理者権限を持つユーザーが「ログオン失敗」したイベントを発見したら、不正アクセスの試みの可能性があるので、すぐにアクションが必要だ。この式を使えば、多数の異常ログを出しているユーザーが誰かも瞬時に把握できる。

ケース3：イベントコードに説明文を付与

　「4625」や「4688」などのコードで表示されるイベントは読みづらいので、「ログオン失敗」や「プロセス作成」といった説明を付けることで調査しやすくなる。ケース1やケース2で用いたログ情報のシートに加え、A列にコード、B列に意味を記載した「イベント一覧」という名前のシートを用意すれば、以下のXLOOKUP式を用いてコードに説明を付けられる。

=XLOOKUP([@Event],イベント一覧!A:A,イベント一覧!B:B,"不明")


Copyright © ITmedia, Inc. All Rights Reserved.

ページトップに戻る