優れた分析、パート 2 — X[LOOKUP] Gon' Pivot To Ya

2019 年 12 月に、インシデント対応調査のためのさまざまなデータ セットに対してMicrosoft Excel を使用した拡張分析に関するブログ投稿を公開しました。説明したように、調査には多くの場合、カスタムまたは独自のログ形式と、従来とは異なるさまざまなフォレンジック アーティファクトが含まれます。もちろん、このタスクに取り組むにはさまざまな方法がありますが、Excel は、遭遇するデータ セットの大部分を分析および変換するための信頼できる方法として際立っています。

最初の投稿では、CONCAT 関数を使用して詳細アーティファクトを要約する方法、TIME 関数を使用してタイムスタンプを変換する方法、およびログ ベースラインに COUNTIF 関数を使用する方法について説明しました。この投稿では、Excel の 2 つの追加の汎用機能、LOOKUP 関数とピボットテーブルについて説明します。

このシナリオでは、サンプル Microsoft Office 365 (O365) インスタンスのログオン イベントのデータセットを使用して、アナリストがデータセット内の情報を強化する方法を示します。次に、ピボットテーブルを使用して情報を要約し、データの異常をすばやく強調表示する方法の例をいくつか示します。

データには次の列が含まれています。

• 説明 – イベントの説明
• ユーザー – ユーザーの名前
• ユーザー プリンシパル名 – 電子メール アドレス
• アプリ – Office 365、Sharepoint など
• 場所 – 国
• 日にち
• IPアドレス
• ユーザー エージェント (簡略化)
• 組織 – IP アドレスに関連付けられています (O365 で識別)

データ強化のための LOOKUP

元のログ ソースでは提供されない分析に役立つ情報をデータに追加すると役立つ場合があります。調査中に FireEye Mandiant がよく実行するステップは、すべての一意の IP アドレスを取得し、各 IP アドレスのレピュテーション、WHOIS 情報、既知の脅威アクター アクティビティへの接続などについて、脅威インテリジェンス ソースをクエリすることです。私たちの分析で考慮に入れます。

FireEye Mandiant は過去のエンゲージメント データとMandiant Threat Intelligenceに関与していますが、セキュリティ チームや組織が商用の脅威インテリジェンス フィードにアクセスできない場合は、活用できるオープン ソース インテリジェンス サービスが数多くあります。

また、IP アドレス ジオロケーション サービスを使用して、各ソース IP アドレスに関連する緯度と経度を取得することもできます。この情報は、地理的な場所に基づいて異常なログオンを識別するのに役立つ場合があります。

すべての送信元 IP アドレスを取得し、それらを脅威インテリジェンス フィードに対して実行して位置情報を特定した後、次のデータを Excel ドキュメントの「IP Address Intel」という 2 番目のシートに追加しました。

ログ自体に飛び込む前に、疑わしいアクティビティがあることを確認できます。データ内の 203.0.113.0/24 の範囲にある 5 つの IP アドレスは、TMP として追跡される架空の脅威アクターに関連するアクティビティに関連付けられていることがわかっています。鬼。

元のデータセットを充実させるために、「緯度」、「経度」、「脅威インテル」という 3 つの列をデータに追加して、補足情報を統合します (図 3)。 VLOOKUP または XLOOKUP 関数を使用して、補足データをすばやく取得し、メインの O365 ログ シートに統合できます。

VLOOKUP

別の配列内の特定のデータを検索する従来の方法は、 VLOOKUP 関数を使用することです。次の式を使用して、特定の IP アドレスの「緯度」値を参照します。

この式には 4 つの部分があります。

1. 検索する値:
   • これにより、詳細情報を検索するセルの値が決まります。この場合、それは IP アドレスであるセル G2 です。
2. テーブル・アレイ：
   • これは、値を検索してデータを返す配列全体を定義します。配列の最初の列には、検索対象の値が含まれている必要があります。前述の例では、’IP Address Intel’!$A$2:$D:$15 を検索しています。つまり、以前に作成したこのワークブックの「IP Address Intel」というタイトルの別のシートを見て、そのシートで A2 から D15 のセル範囲を検索します。
     

     

     「$」を使用してこれらが絶対参照であることを確認し、この式を他のセルにコピーしても Excel によって更新されないことに注意してください。

3. 列のインデックス番号:
   • これは、データを返す列番号を識別します。最初の列は列 1 と見なされます。指定された IP アドレスの「緯度」値を返したいので、前述の例では、列 2 からデータを返すように Excel に指示します。
4. 範囲検索 (一致タイプ)
   • 数式のこの部分は、検索対象の値に対して実行する照合の種類を Excel に指示します。 Excel はデフォルトで「近似」一致に設定されています。これは、データが並べ替えられていることを前提としており、最も近い値に一致します。 「正確」なマッチングを行いたいので、ここに「0」を入れます（「FALSE」も可）。

「緯度」データの VLOOKUP 関数が完成したら、フィル ハンドルを使用して、残りのデータ セットのこのフィールドを更新できます。

「Longitude」列と「Threat Intel」列の値を取得するには、同様の関数を使用してプロセスを繰り返し、列インデックス番号を調整して適切な列を参照し、塗りつぶしハンドルを使用して残りの部分を塗りつぶします。 O365 データシートの列:

• 経度の場合:
  • =VLOOKUP(G2,’IP アドレス インテル’!$A$2:$D$15, 3 ,0)
• スレット インテルの場合:
  • =VLOOKUP(G2,’IP アドレス インテル’!$A$2:$D$15, 4 ,0)

ボーナス オプション: XLOOKUP

Excel のXLOOKUP 関数は、脅威インテリジェンス データ シートを参照するより効率的な方法です。 XLOOKUP は、従来の VLOOKUP 関数を置き換えるために Excel に導入された新しい関数であり、この投稿の執筆時点では、Microsoft によると、「月次チャネルの O365 サブスクライバー」のみが利用できます。この例では、列ごとに XLOOKUP 関数を作成する代わりに、Excel の動的配列と「スピル」を利用してこのデータをより効率的に入力します。

注: 動的配列とスピルを利用するには、強化しようとしているデータを「テーブル」オブジェクトの形式にすることはできません。代わりに、[ホーム] リボンの [並べ替えとフィルター] の下にある [フィルター] オプションを選択して、O365 データ セットの一番上の行にフィルターを適用します。

XLOOKUP を使用して脅威インテリジェンス データ シートを参照するには、次の式を使用します。

この XLOOKUP 式には 3 つの部分があります。

1. 検索する値:
   • これにより、詳細情報を検索するセルの値が決まります。この場合、それは IP アドレスであるセル G2 です。
2. 調べる配列:
   • これは、Excel が参照する値を検索するデータの配列になります。 Excel は、既定で XLOOKUP の完全一致を行います。前述の例では、’IP Address Intel’!$A$2:$A:$15 を検索しています。つまり、このワークブックの「IP Address Intel」というタイトルの別のシートを見て、そのシートで A2 から A15 のセル範囲を検索します。
     

     

     「$」を使用してこれらが絶対参照であることを確認し、この式を他のセルにコピーしても Excel によって更新されないことに注意してください。

3. 返すデータの配列:
   • この部分は、Excel がデータを返すデータの配列になります。この場合、Excel は、検索された値の「IP アドレス Intel」シートから B2 から D15 の絶対範囲内に含まれるデータを返します。前述の式の例では、IP アドレス 198.51.100.126 の行の値が返されます。
     

     

     これは動的配列とスピルを利用しているため、図 4 に示すように、返されたデータの 3 つのセルすべてが入力されます。

VLOOKUP または XLOOKUP を使用してデータセットが完全に強化されたので、異常なアクティビティの探索を開始できます。最初の簡単な手順として、少なくとも少数の IP アドレスが悪意のある可能性があることがわかっているため、「TMP.OGRE」に一致するすべての行を「Threat Intel」列でフィルタリングし、既知の IP アドレスに関連するソース IP アドレスでのログオンを明らかにします。攻撃者。これで、時間枠と侵害された疑いのあるアカウントをピボットして、他のデータをさらに探索することができました。

ピボット！ピボット！ピボット！

データを要約し、頻度分析を実行し、特定のデータセットに関する他の統計を迅速に取得することによって異常を強調するための最も便利なツールの 1 つは、Excel のピボットテーブル機能です。

位置異常

ピボットテーブルを利用して、ユーザーがログインした場所で頻度分析を実行しましょう。このタイプの手法では、ユーザー アカウントが通常とは異なる場所からログインしたアクティビティを強調する場合があります。

データのピボットテーブルを作成するには、O365 データの任意のセルを選択し、Ctrl+A で範囲全体を選択します。次に、リボンの [挿入] タブで、[ピボットテーブル] を選択します。

これにより、図 11 に示すようなウィンドウが表示され、ピボットテーブルを作成するデータを確認できます (図 11 のステップ 1)。 O365 ログ データ セットを Ctrl+A で選択したため、これは自動的に入力されます。また、ピボットテーブルを配置する場所も尋ねられます (図 11 のステップ 2)。この例では、ピボットテーブルを配置するために「ピボットテーブル 1」という別のシートを作成しました。

ピボットテーブルが作成されたので、データを使用してピボットテーブルにデータを入力する方法を選択する必要があります。すべてのユーザーがログインした場所を特定しようとしていることを思い出してください。各ユーザーの行と、ユーザーがログインした各場所のサブ行が必要です。それぞれの場所からのログイン回数も追加してみましょう。この例では、「日付」フィールドを使用してこれを行います。

このテーブルを調べると、ソースの場所に異常のある 2 人のユーザーがいることがすぐにわかります。Ginger Breadman と William Brody は、「FarFarAway」からのログオン数が少なく、このデータ セットに基づくと、これらのユーザーにとって異常です。

このピボットテーブルにさらにデータを追加して、この不審なアクティビティの時間枠を取得するには、[値] 領域にさらに 2 つの [日付] フィールドを追加します。 Excel では、この領域にドロップするフィールドのデフォルトは「カウント」ですが、図 13 に示すように、「値フィールドの設定」を使用して、これを「最小」および「最大」の値に変更します。

これで、ログオンの異常な場所と、ログオンが発生した時間枠を示すピボットテーブルができたので、調査に磨きをかけることができます。この例では、適切なセルをすべて選択し、右クリックして [セルの書式設定] を選択し、”YYYY- MM-DD HH:MM:SS」。

IP アドレスの異常

ジオロケーションの異常は、常に価値があるとは限りません。ただし、前の例と同様の構成を使用すると、疑わしい送信元 IP アドレスを特定できます。 「User Principle Name」フィールドと「IP Address」フィールドを行として追加し、「IP Address」フィールドを値として追加します。 「App」フィールドも列に追加しましょう。フィールド設定と結果のテーブルを図 15 に示します。

数回クリックするだけで、各ユーザーがどの IP アドレスからログインし、どのアプリにログインしたかを示す要約表が表示されます。 203.0.113.0/24 の範囲の IP アドレスから 6 回ログインした 2 人のユーザーと、これらの各 IP アドレスからログインしたアプリケーションをすばやく特定できます。

これらはいくつかの使用例にすぎませんが、ピボットテーブルを使用してエビデンスをフォーマットおよび表示する方法は多数あります。 Excel で確認するデータ セットに対してピボットテーブルを試し、行、列、および値のパラメーターを試してみることをお勧めします。

また、 ピボットテーブルのオプションを調整することをお勧めします。これにより、テーブル自体を要件に適合する形式に再フォーマットすることができます。

結論

これらの Excel 関数は、FireEye Mandiant での調査中に頻繁に使用され、重要なフォレンジック分析手法と見なされています。ここで示す例は、LOOKUP 関数とピボットテーブルのユーティリティを垣間見るだけです。 LOOKUP 関数は、多数のデータ ソースを参照するために使用でき、修復や分析作業の追跡などの調査中に他の状況で適用できます。

ピボットテーブルは、利用可能なデータや、疑わしいアクティビティを特定するために分析される情報の種類に応じて、さまざまな方法で使用される可能性があります。これらの手法と、以前に強調した手法を一貫して採用することは、フォレンジック分析のスキルと効率を「高める」のに大いに役立ちます。