メインコンテンツへスキップ

SQL で AWS コストスパイクを検出する

このチュートリアルでは、AWS の支出履歴を自前で保持し、それを SQL でクエリーするフローを作成します。カスタムスケジュールで毎朝フローを実行し、Date/time transform で前日の日付を計算し、AWS ノードで Cost Explorer からサービス単位の支出を取得します。さらに 3 つの Datastore ノードで日付の記録、単一の SQL 文によるスパイク検出、古い行の削除を行います。Filter と Notification ノード で、その結果を Slack アラートに変換します。

Goal and objectives​

  • Goal: 外部データベースやデータウェアハウスのジョブを使わずに、「前日の支出が直近 30 日平均より 50% を超えて増加した AWS サービス」を名前付きで通知する Slack アラートを受け取れるようにします。

  • Objectives: このチュートリアルでは次の内容を学びます。

    • Datastore の Insert ノードを使ってローリングな履歴テーブルを構築する方法。

    • Datastore の Run SQL アクションで SELECT 文を書き、:name パラメータで前のステップの値をバインドする方法。

    • フィルターや Slack メッセージで、クエリーの出力カラムを参照する方法。

    • スケジュールされた DELETE によってテーブルサイズを抑える方法。

以下は完成したフローです。

The cost spike sentinel flow

注意

このフローの AWS への呼び出しはすべて読み取り専用です（ce:GetCostAndUsage）。書き込みが発生するのは、自身の Datastore テーブル内だけです。

Before you begin​

  1. DoiT アカウントに CloudFlow Editor または CloudFlow Manager の権限が付与されていることを確認してください。CloudFlow permissions を参照してください。

  2. 監視したい支出が発生するアカウントに対して、AWS CloudFlow 接続 を作成してください。このロールには ce:GetCostAndUsage 権限が必要です。

  3. Slack を接続 し、アラートを受け取るチャンネルを特定してください。

Create the history table​

このフローには、日次の支出を蓄積するテーブルが 1 つ必要です。

  1. CloudFlow のランディングページで Tables を選択し、次に Create table を選択してください。

  2. Table name に daily_service_spend と入力してください。

  3. 次の 3 つのカラムを定義してください。

    ColumnData typeNotes
    dayDate利用日であり、サービス・日付ごとに 1 行となります。
    serviceTextAWS サービス名。例: AWS WAF。
    costTextCost Explorer は金額を文字列として返します。SQL は計算時にこれを数値にキャストします。

  4. Save を選択してください。

注意

cost をあえて Text カラムにしています。Cost Explorer は Metrics.UnblendedCost.Amount を文字列として返し、Numeric カラムは数値参照のみ受け付けます。生の文字列を保存し、SQL 内でキャストする（cost::numeric）ことで、Insert ノードをシンプルに保てます。

Create the flow​

  1. DoiT コンソール にサインインし、トップナビゲーションのメガメニューから Automation and operations を選択し、CloudFlow を選択してください。

  2. Create flow を選択し、Cost spike sentinel などの名前と説明を追加してください。

Configure the schedule trigger​

  1. Scheduled トリガーを追加してください。

  2. 頻度を Daily に設定し、開始日を選択して、実行時間とタイムゾーンを指定してください。前日の利用分はその時点でほぼ確定しているため、早朝の実行が適しています。

Compute yesterday's date​

Cost Explorer には日付が必要であり、後で使用する SQL にも同じ日付が必要です。1 つの transform で両方を生成します。

  1. トリガーの後に Date/time transform ノードを追加してください。

  2. Input をトリガーの startTime に設定してください。

  3. Subtract 変換を追加し、1 Day を指定し、出力名を yesterdayTs としてください。

  4. 直前のステップに対して Format 変換をチェーンし、パターンを YYYY-MM-DD、出力名を yesterday としてください。

    Date/time transform producing yesterday

これでフローには yesterday という文字列ができ、後続のすべてのノードから参照できます。

Fetch yesterday's spend per service​

  1. AWS ノードを追加し、Cost Explorer サービスと GetCostAndUsage オペレーションを選択し、AWS 接続およびアカウントを選択してください。

  2. パラメータを設定してください。

    • TimePeriod → Start: Date/time transform の yesterday を参照します。

    • TimePeriod → End: トリガーの currentDate を参照します。Cost Explorer は End を排他的として扱うため、ちょうど 1 日分が返されます。

    • Granularity: DAILY

    • Metrics 1: UnblendedCost

    • GroupBy 1: Key SERVICE、Type DIMENSION

    GetCostAndUsage parameters

  3. Test タブを開き、Save as test data を選択したまま Run test を選択してください。レスポンスには ResultsByTime[0].Groups が含まれ、サービスごとに 1 エントリあり、次のノードでこの情報をテーブルにマッピングします。

Record the day in the table​

  1. Datastore ノードを追加し、daily_service_spend テーブルを選択し、Insert アクションをそのまま使用してください。

  2. 各カラムを Cost Explorer の出力にマッピングしてください。このノードはグループごとに自動的に 1 行を挿入します。

    • Day: ResultsByTime.TimePeriod.Start

    • Service: ResultsByTime.Groups.Keys

    • Cost: ResultsByTime.Groups.Metrics を選択し、マップキーとして UnblendedCost を入力し、Amount を選択します。

    Insert mapping for the spend history table

注意

3 つのカラムはいずれも同じノードを参照します。1 つのノードのパラメータが参照できるのは、トリガー以外のステップが 1 つだけのため、日付は Date/time transform ではなく Cost Explorer のレスポンスから取得しています。

Detect spikes with SQL​

ここで分析を行うノードを設定します。前日と、それ以前のすべての日付をテーブル内で比較します。

  1. 2 つ目の Datastore ノードを追加し、同じテーブルを選択して、アクションを Run SQL に設定してください。

  2. 次のステートメントを入力してください。

    WITH baseline AS (
      SELECT service, AVG(cost::numeric) AS avg_cost
      FROM daily_service_spend
      WHERE day < :yesterday::date
        AND day >= :yesterday::date - 30
      GROUP BY service
    )
    SELECT s.service,
           ROUND(s.cost::numeric, 2) AS yesterday_cost,
           ROUND(b.avg_cost, 2) AS avg_cost,
           ROUND(s.cost::numeric / NULLIF(b.avg_cost, 0), 2) AS spike_ratio
    FROM daily_service_spend s
    JOIN baseline b ON b.service = s.service
    WHERE s.day = :yesterday::date
      AND s.cost::numeric > 1.5 * b.avg_cost
    ORDER BY spike_ratio DESC

    この共通テーブル式は、前日より前の 30 日間におけるサービスごとの平均コストを算出し、メインクエリーは前日の行とその平均を結合します。WHERE 句では、その平均より 50% を超えて高いサービスのみを残します。NULLIF を使うことで、履歴がすべて 0 のサービスでゼロ除算が起きるのを防いでいます。

    どちらの日付境界も重要です。下限がない場合、平均はテーブル内のすべての行を対象にしてしまい、履歴が蓄積されるにつれて比較ウィンドウが暗黙的に広がってしまいます。

  3. Bind parameters で Add parameter を選択し、名前を yesterday としてください。次に Add additional parameters を選択し、yesterday チェックボックスをオンにして確定してください。

  4. 表示される Yesterday フィールドで、Date/time transform の yesterday を参照してください。

    Run SQL spike detection with a bound parameter

  5. Run query を選択して検証してください。ステートメントが有効になると、Output schema にクエリーが返すカラム service、yesterday_cost、avg_cost、spike_ratio が表示されます。フィルターと Slack メッセージはこれらの名前を参照します。

注意

ステップ出力にバインドされたパラメータは、エディタ内で Run query を選択したときには空値として解決されます。実際のフロー実行時に完全に解決されます。

Prune old history​

このステップがない場合、テーブルは無制限に成長してしまいます。ここでは 1 つのステートメントで履歴を 90 日に制限します。これは 30 日の比較ウィンドウよりも意図的に長くしてあり、アラートの内容を検証したいときに参照できる履歴が残るようにしています。

  1. 同じテーブルに対して 3 つ目の Datastore ノードを追加し、アクションを Run SQL にしてください。

  2. 次のステートメントを入力してください。

    DELETE FROM daily_service_spend WHERE day < :yesterday::date - 90

  3. 前のノードと同じ方法で yesterday のバインドパラメータを追加し、再度 Date/time transform を参照してください。

このノードは Filter の前に配置し、スパイクがない日も含めて、すべての実行で pruning が行われるようにしてください。

Filter to real spikes​

  1. Filter ノードを追加してください。

  2. Field をスパイク検出ノードの出力に設定してください。

  3. 条件として spike_ratio > 1.5 を追加してください。

    Filter on spike ratio

SQL ですでにこの閾値を適用していますが、この Filter が「報告すべきものがあるかどうか」をフローに伝える役割を果たします。

Send the alert​

  1. Notification ノードを追加し、プロバイダーとして Slack を選択してください。

  2. アラートを受信するチャンネルを選択してください。

  3. メッセージ本文を作成し、@ を入力して Filter のフィールドを参照してください。例えば次のようにします。

    AWS cost spike detected: yesterday's spend ran more than 50% above the trailing 30-day average for the services below.
    @service spike ratio: @spike_ratio (yesterday @yesterday_cost vs 30-day avg @avg_cost)

  4. Don't send notification if no results を選択してください。これを有効にしない場合、スパイクがなかった日でも空の値を含むメッセージが投稿されてしまいます。

    Slack notification with referenced fields

Publish and verify​

  1. Publish を選択してください。

  2. Run を選択してフローを手動でトリガーし、実行詳細を開いてすべてのステップが完了していることを確認してください。

    A completed run of the cost spike sentinel flow

  3. スパイク検出ステップを展開し、返された行を確認してください。最初の実行時点ではテーブルに 1 日分のデータしかないため、ベースラインは空であり、どのサービスもそれを上回ることはできません。これは想定どおりです。

  4. Tables タブからテーブルを開き、前日の行が書き込まれていることを確認してください。

    Recorded rows in the daily_service_spend table

数日経つとベースラインに意味が出てきて、このフローは実際の外れ値に対してアラートを出し始めます。スパイクが閾値を超えると、Slack メッセージにはサービス名、スパイク比、その裏にある 2 つの金額が表示されます。

AWS cost spike detected: yesterday's spend ran more than 50% above the trailing
30-day average for the services below.
Amazon Elastic Compute Cloud - Compute, AWS WAF spike ratio: 1.87, 1.8
(yesterday 0.37, 0.32 vs 30-day avg 0.2, 0.18)

パターンを調整する​

このフローの形は、時間の経過とともに監視したいあらゆる対象に一般化できます。スケジュールに従って測定値を記録し、SQL で集計し、外れ値にアラートを出し、履歴を削除していきます。次のようなバリエーションも試す価値があります。

  • サービスではなくアカウントを監視するために、Cost Explorer を SERVICE ではなく LINKED_ACCOUNT でグループ化します。

  • ベースラインに HAVING COUNT(*) >= 7 句を追加し、アラートをトリガーできるようになる前に、サービスに 1 週間分の履歴が必要となるようにします。

  • 乗数を 1.5 からより厳しい値に変更するか、平均ではなく MAX(cost) と比較するようにします。

  • サービスごとの閾値を保持する第 2 のテーブルを用意してそれに結合し、ノイズの多いサービスには独自の乗数を適用できるようにします。

に最終更新