Excelerating Analysis – Microsoft Excel でデータを分析するためのヒントとコツ

インシデント対応の調査には、完全に開発された解析および分析ツールを備えた標準的なホストベースのアーティファクトが常に含まれているわけではありません。 FireEye Mandiant では、カスタム ロギングやアーティファクト データを利用する多くのシステムやソリューションに関連するインシデントに頻繁に遭遇します。インシデントで何が起こったのかを判断するには、提示されたあらゆる種類のデータを詳しく調べ、それについて学び、重要な証拠を分析する効率的な方法を開発する必要があります。

この種の分析を実行する最も効果的なツールの 1 つは、ほぼすべてのツールキットに含まれている Microsoft Excel です。この記事では、あらゆる種類のデータが提示されたときに Excel で分析を実行するためのヒントとコツについて詳しく説明します。

詳細アーティファクトの要約

FireEye Redlineなどのツールには、複数のアーティファクト タイプを 1 つの簡潔なタイムラインにまとめる便利なタイムライン機能が含まれています。個々のパーサーまたはカスタム アーティファクト フォーマットを使用する場合、同じビューで複数のタイプのデータを表示するのは難しい場合があります。成果物データを Excel で特定の使いやすい列セットに正規化すると、さまざまな成果物タイプをスムーズに組み合わせることができます。

次のデータを使用して、解析されたファイル システム、イベント ログ、およびレジストリ データを同じビューで確認することを検討してください。

これらの生のアーティファクト データ セットには異なる列見出しとデータ タイプがあるため、1 つのタイムラインでレビューするのは困難です。 Excel の文字列連結を使用してデータをフォーマットすると、データを簡単に 1 つのタイムライン ビューに結合できます。データをフォーマットするには、関数で「&」操作を使用して、必要な情報を「Summary」フィールドに結合します。

アンパサンドで区切られた関連するファイル システム データを結合するコマンドの例は、「=D2 & ” | ” & C2 & ” | ” & E2 & ” | ” & F2 & ” | ” & G2 & ” | ” & H2」です。このフォーマット関数を「タイムスタンプ」列と「タイムスタンプ タイプ」列と組み合わせることで、合理化された分析に必要なすべてが完成します。

タイムスタンプで並べ替えた後、「DomainCorpAdministrator」アカウントが「DomainCorpVictimUser」アカウントを使用して「SourceSystem」から「DestinationServer」に RDP 経由で 3 つのアーティファクト タイプにわたって接続している証拠を確認できます。

タイムゾーンの変換

インシデント対応とフォレンジック分析の最も重要な要素の 1 つはタイムラインです。時間分析は、関心のあるイベントに先行または後続するイベントを特定することによって、新しい証拠を発見することがよくあります。同様に重要なのは、レポートの正確なタイムラインを作成することです。タイムスタンプとタイム ゾーンはイライラする可能性があり、分析対象のシステムがさまざまなタイム ゾーンにまたがると混乱する可能性があります。 Mandiant は、調査においてすべてのタイムスタンプを協定世界時 (UTC)形式で追跡し、タイム ゾーンと夏時間や地域の夏季などの時間調整の混乱を排除します。

もちろん、さまざまな証拠源が常に同じ方法で時間を記録するとは限りません。現地時間の場合もあれば、UTC の場合もあります。前述のように、地理的にさまざまな場所にあるソースからのデータは、事態をさらに複雑にします。タイムラインをコンパイルするときは、証拠ソースが UTC で記録されているか現地時間で記録されているかを最初に知ることが重要です。現地時間で記録されている場合は、証拠のソースがどの現地時間帯からのものかを確認する必要があります。次に、Excel の TIME() 式を使用して、必要に応じてタイムスタンプを UTC に変換できます。

この例のシナリオは、ターゲット組織がフィッシング メールによって侵害され、従業員の直接預金情報が社内の HR アプリケーションによって変更されたという実際の調査に基づいています。この状況では、メール受信ログ、アプリケーション ログイン、アプリケーション Web ログの 3 つのログ ソースがあります。

電子メール ログは UTC で記録され、次の情報が含まれます。

アプリケーションのログインは東部夏時間 (EDT) で記録され、以下が含まれます。

アプリケーション Web ログも東部夏時間 (EDT) で記録され、次の内容が含まれます。

この情報を取得してマスター タイムラインに変換するには、CONCAT 関数 (以前に使用したアンパサンド連結の代替) を使用して、ログ ソースごとに 1 つのセルに列の概要を作成できます。メール受信ログ:

ここで、各データ ソースのタイム ゾーンを確認することが重要です。ログに表示されている情報を取得し、タイムスタンプがすべて同じタイム ゾーンにあると仮定して、この情報のタイムラインを作成すると、次のようになります。

前のスクリーンショットにあるように、HR アプリケーションへのログイン イベントがいくつかあります。これは、従業員の通常のアクティビティのように見える場合があります。その後、その日の後半に、不審な電子メールを受け取ります。これが数百行のログ イベントである場合、アクティビティの時刻が、疑わしい最初の侵害ベクトルよりも数時間先行するため、ログインおよび Web ログ イベントが見落とされる危険があります。これが報告に使用されるタイムラインである場合、それも不正確になります。

ログ ソースがどのタイム ゾーンにあるかがわかれば、それに応じてタイムスタンプを調整して UTC を反映させることができます。この場合、アプリケーションのログインと Web ログが UTC から 4 時間遅れた EDT、つまり「UTC-4」で記録されることをテストで確認しました。これらを UTC 時間に変更するには、時刻に 4 時間を追加するだけです。 Excel の TIME 関数を使用すると、これが簡単になります。既存のテーブルに列を追加するだけで、最初のセルに「=A2+TIME(4,0,0)」と入力します。これを分解する：

• =A2
  • 参照セル A2 (この場合は EDT タイムスタンプ)。これは絶対参照ではないことに注意してください。したがって、残りの行にこの式を使用できます。
• +時間
  • これは、セル A2 のデータの値を「時間」値タイプとして取得し、次の時間を追加するように Excel に指示します。
• (4,0,0)
  • このインスタンスの TIME 関数には、左から右に、時間、分、秒の 3 つの値が必要です。この例では、4 時間 0 分 0 秒を追加しています。

これで、EDT タイムスタンプに 4 時間を加算して UTC にする数式ができました。次に、この式をテーブルの残りの部分に複製できます。最終結果は次のようになります。

すべてのログを同じタイム ゾーンに配置したら、マスター タイムラインをコンパイルする準備が整います。 UTC タイムスタンプと作成した要約イベントを取得すると、新しい正確なタイムラインは次のようになります。

これで、(架空の) 従業員 Austin と Dave に送信された疑わしい電子メールがはっきりとわかります。数分後、Austin のアカウントは HR アプリケーションにログインし、新しい銀行口座を追加します。この後、Jake に送信された同じメールが表示されます。この直後、Jake のアカウントは HR アプリケーションにログインし、Austin と同じ銀行口座情報を追加します。 Excel を使用してすべてのデータ ソースを同じタイム ゾーンに変換することで、これらのイベントをすばやく関連付けて、攻撃者の行動を簡単に特定できるようになりました。さらに、残りのログで検索する既知の不正な銀行口座番号など、より多くの指標を提供してくれました。

プロのヒント: 夏時間や夏季などの地域イベントによる UTC オフセットの変化にまたがるログ データを必ず考慮してください。たとえば、ローカル タイム ゾーンの調整は、毎年 3 月の最初の週末に米国東部時間で米国バージニア州から +TIME(5,0,0) から +TIME(4,0,0) に変更する必要があります。 +TIME(4,0,0) から +TIME(5,0,0) に 11 月の最初の週末を戻し、夏時間と標準シフトを考慮します。

ログ ベースラインの CountIf

ユーザー アカウントとタイムスタンプの形式で認証を記録するログを確認する場合、COUNTIF を使用して単純なベースラインを確立し、一貫性のないアクティビティを持つユーザー アカウントを特定できます。

次のユーザー ログオンの例では、数式 “=COUNTIF($B$2:$B$25,B2)” を使用して履歴ベースラインを確立します。この例の C2 にあるこの COUNTIF 式のパラメーターの内訳は次のとおりです。

• COUNTIF
  • この Excel 数式は、値がセル範囲に存在する回数をカウントします。
• $B$2:$B$25
  • これは、特定の値を検索する範囲として使用する B2 から B25 までのすべてのセルの範囲全体です。範囲の開始と終了が絶対参照であり、この数式を他のセルにコピーした場合に Excel によって自動的に更新されないようにするために、”$” を使用していることに注意してください。
• B2
  • これは、$B$2:$B$25 の範囲で検索して出現回数をカウントする値を含むセルです。このパラメータは、「$」が先行する絶対参照ではないことに注意してください。これにより、すべての行に数式を入力し、該当するユーザー名を確実に数えることができます。

要約すると、この数式は、すべてのログオン データのユーザー名列を検索し、各ログオンのユーザーがすべてのデータ ポイントで合計何回ログオンしたかをカウントします。

ほとんどのユーザー アカウントが定期的にログオンする場合、最初のログオンに使用された侵害されたアカウントは、合計ログオン数を確認すると明らかに目立つ場合があります。特定の時間枠を念頭に置いている場合は、その時間帯に最初にログオンしたアカウントを知ることが役立つ場合があります.

COUNTIF 式は、アカウントを経時的に追跡して最初のログオンを特定するのに役立ちます。これにより、限られた時間枠で悪用された、めったに使用されない資格情報を特定するのに役立ちます。

セル D3 の式「=COUNTIF($B$2:$B2,B2)」から始めます。この COUNTIF 式のパラメーターの内訳を次に示します。絶対参照での “$” の使用は、使用される範囲によってわずかに異なることに注意してください。これは重要なニュアンスです。

• COUNTIF
  • この Excel 数式は、値がセル範囲に存在する回数をカウントします。
• $B$2:$B2
  • これは、開始したい B2 から B2 までのセルの範囲です。ログ データの行をたどって範囲を広げたいので、最後のセルの行番号 (この例では 2) は絶対値にしません。この式を残りのログ データに入力すると、現在のログ レコードと以前のすべてのログが含まれるように範囲が自動的に拡張されます。
• B2
  • このセルには、検索する値が含まれており、定義した範囲内で見つかった出現回数が示されます。このパラメーター B2 は、先行する「$」による絶対参照ではないことに注意してください。これにより、すべての行に数式を入力し、該当するユーザー名を確実に数えることができます。

要約すると、この式は、現在のログを含むすべてのログオン データのユーザー名列を検索し、各ログオンのユーザーがその時点までにログオンした回数をカウントします。

次の例は、Excel がフィル ハンドルを使用してD15 の範囲を $B$2:$B15 に自動的に更新する方法を示しています。

大規模なデータ セットを視覚化するために、カラー スケールの条件付き書式を各行に個別に追加しましょう。そうするために：

1. カラー スケールと比較するセルのみを選択します (D2 から D25 など)。
2. [ホーム] メニューで、[スタイル] 領域の [条件付き書式] ボタンをクリックします。
3. [カラー スケール] をクリックします。
4. 使用するカラー スケールの種類をクリックします。

次の例では、最小値を赤に、最大値を緑に設定します。方法は次のとおりです。

• 認証数が少ないユーザーは、認証数が多いユーザーと対照的です。
• ユーザーの最初の認証時間は赤で目立ちます。

どの色を使用する場合でも、緑などの 1 つの色は安全性を意味し、赤などの別の色は悪意を意味すると思い込まないように注意してください。

結論

この記事で説明する手法は、Excel を利用して任意のデータを分析する方法のほんの一部です。これらのテクニックは、他のさまざまなスキル セットと同様に、Excel のより強力な機能の一部を活用できない場合がありますが、基礎を習得することで、より高いレベルで実行できるようになります。基本的な Excel 分析手法を採用することで、調査担当者は提示されたデータ型を可能な限り効率的に分析できるようになります。