意外と使える！こんなところにもBigQuery

※本記事は、2022年10月31日に公開済みの記事を移行して再掲載したものです。

はじめに

フェズでエンジニアをしている321です
クラウド全体のコストやセキュリティの管理をひとつのミッションとしています

フェズではBigQueryを主に購買データ分析等の用途で利用していますが、BigQueryにはクラウド管理の用途として利用できる機能もあります
便利に使えば運用担当者の負荷を下げる事もできるので、今回はその機能をご紹介しようと思います

コスト (Cloud Billing)

従量課金のクラウドを利用する上で、コストを正しく把握することは必要不可欠です
Google Cloudは、組織毎に Cloud Billing でProjectやサービスレベルでコストを把握する事ができます
Cloud Billing自体にはレポートやダッシュボード機能があり、ある程度詳細な請求データを可視化することができます
ですが、Cloud Billingの閲覧にはGoogle Cloudの権限を持つ必要があり、非エンジニアや経営層レベルに共有するのは少し難しいです

Cloud Billing データを BigQuery にエクスポートする

Cloud Billing データを BigQuery にエクスポートする | Google Cloud

Cloud Billingには詳細な請求データをBigQueryにエクスポートする機能があります
BigQueryにエクスポートした請求データをBIツールでダッシュボード化して共有できれば、管理者が都度レポートを作成する手間を省くことができます
エクスポート手順は上記公式ドキュメントに記載があるので省略しますが、GUIからポチポチするだけで設定可能です

エクスポート先の課金データテーブル

• 標準の使用料金テーブル : アカウント ID、請求書の日付、サービス、SKU、プロジェクト、ラベル、ロケーション、費用、使用量、クレジット、調整、通貨など
• 詳細な使用料金テーブル : 標準の使用料金テーブルに加えてリソースレベルの費用データも含む詳細データ
• 料金データテーブル : Cloud 請求先アカウントの料金データ

課金データテーブルは上記の3種類が用意されています
スキーマ情報は各リンク先で参照できるので、利用用途に応じて必要なデータテーブルを選択してください
フェズでは要件として 標準の使用料金テーブル で十分だったので、有効化した結果以下のようなテーブルが作成されました
※ 有効化する前の請求データは エクスポートされない のでご注意ください

請求データを分析する

請求データがエクスポートできたら、あとはBigQueryにクエリを実行するだけです
公式ドキュメント にサンプルクエリが紹介されていますのでご参照ください
例えば簡単な例として、Projectごとに前月の請求額を算出したい 場合は以下のようなクエリを実行すればOKです

SELECT
  project.name AS project_name,
  CAST(SUM(cost) AS INT) AS cost,
FROM
  `[project_name].[dataset_name].gcp_billing_export_v1_XXXXX_XXXXX_XXXXX`
WHERE
  invoice.month = FORMAT_TIMESTAMP("%Y%m",DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))
GROUP BY
  project_name
ORDER BY
  cost DESC

もう少し実戦的な例として、企業の部門やグループごとにコストを按分したい ケースがあるかと思います
例えば以下のように、Projectのコストが組織のどの部門やグループに紐づくかを台帳的に管理するテーブル( Name:organization )を事前に用意します

このテーブルと請求データテーブルをJOINさせれば、部門やグループごとに請求コストが算出できます

SELECT
  ORG.division_name AS division_name,
  ORG.group_name AS group_name,
  CAST(SUM(BILLING.cost) AS INT) AS cost,
FROM
  `[project_name].[dataset_name].gcp_billing_export_v1_XXXXX_XXXXX_XXXXX` AS BILLING
FULL JOIN
  `[project_name].[dataset_name].organization` AS ORG
ON
  BILLING.project.id = ORG.project_id
WHERE
  BILLING.invoice.month = FORMAT_TIMESTAMP("%Y%m",DATE_TRUNC(DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 MONTH), MONTH))
GROUP BY
  division_name, group_name
ORDER BY
  cost DESC

セキュリティ (Security Command Center)

以前 こちら の記事でもご紹介したのですが、フェズでは Security Command Center(SCC) を使って脆弱性の検知を実施しています
SCCは独自でダッシュボード機能を持っており、Google Cloudのコンソール上で可視化することは可能です
ですが、こちらも Cloud Billing と同様に閲覧にはGoogle Cloudの権限を持つ必要があり、関係者への共有がやはり難しいです

SCC検出結果を BigQuery にエクスポートする

SCCにも公式に検出結果をBigQueryにエクスポート機能があります
分析のために検出結果を BigQuery にエクスポートする | Security Command Center | Google Cloud

こちらもエクスポートする手順は上記の公式ドキュメントにある通りですが、この記事を書いている時点ではGUIではなくgcloudコマンドやSDKで有効化します

エクスポート先のデータテーブル

こちらもエクスポートを有効にすると自動的に以下のようなテーブルが作成されます
データセットの構造については 公式ドキュメント をご参照ください

SCC検出結果を分析する

エクスポートしたSCC検出結果を分析するためのクエリが 公式ドキュメント に紹介されています
例えば、最新のアクティブな検出結果 をProjectごとに出力したければ以下のようなクエリを実行すればOKです

WITH latestFindings AS (
    SELECT * EXCEPT(row)
    FROM (
        SELECT *, ROW_NUMBER() OVER(
            PARTITION BY finding_id
            ORDER BY event_time DESC, finding.mute_update_time DESC
        ) AS row
        FROM `PROJECT_ID.DATASET.findings`
    ) WHERE row = 1
)
SELECT
  finding.category AS category,
  count(finding.category) AS finding_count,
FROM
  latestFindings
WHERE
  finding.state = "ACTIVE"
GROUP BY
  category
ORDER BY
  finding_count DESC

BIツールとの連携

「BigQueryにエクスポートしたデータにクエリを実行して詳細な分析ができるよ」
というご紹介をしていますが、
フェズではBigQueryにエクスポートしたデータにLooker Studio から接続してダッシュボード化をしています
(ダッシュボードのご紹介は機密情報が多くモザイクだらけになるので割愛しました)
今回ご紹介したCloud BillingとSCCはどちらも独自にダッシュボードやレポート機能があり、可視化自体はBigQueryを利用しなくても実現可能です

ですが、非エンジニアや経営層に情報を伝えるためには可視化ツールやデータの切り口は、組織ごとにカスタマイズが必要になるケースが多いと思います
実際にフェズでもLooker Studioと連携する事で必要な人に必要な情報を提供する事が実現できました
BigQueryは色んなBIツールと連携可能なので、エクスポートしておくと何かと役に立つかもしれません

まとめ

ということで、BigQueryの実は便利な機能のご紹介でした
他にも自分が知らないだけでBigQueryを便利に使い倒す方法はきっとあるので、発見できたらまた記事にしようかなと思います