Zenn
🔍

BigQueryにシンクしたデータアクセス監査ログでよく使うクエリ5選

2021/12/15に公開
BigQuery
#
adventcalendar
tech

株式会社マネーフォワードでデータエンジニアをしている太田です。

BigQuery Advent Calendar 2021 15日目の記事です。
https://qiita.com/advent-calendar/2021/bigquery

当初、「VPC Service ControlsとBigQuery」をテーマに記事を書いておりましたが内容に誤りがあったためテーマ変更してお送りします。
当初のテーマの記事は後ほど内容整えて別途公開したいと思います。
急遽テーマ変更したため、薄い内容の記事になってしまったこと、アドベントカレンダー記事として公開が遅れたことお許しください🙏

さて、今回はBigQueryに溜めた監査ログを読む際にわたしがよく使用しているクエリを紹介したいと思います。

個人的な話をすると、近年は分析屋兼データエンジニアからデータエンジニア専業にシフトしつつありクエリを投げる機会が減っていたりします。
今年一番お世話になったテーブルは、組織でシンクしているデータアクセス監査ログテーブルだったこともあり感謝の意を込めて記事に残したいと思います。

前提

  • 組織配下のすべてのBigQueryを使用するプロジェクトでデータアクセス監査ログの収集をしていること
  • 収集した監査ログを特定のプロジェクトのBigQueryにシンクしていること

弊社では、GCPプロジェクト（組織）をTerraformで管理しており、プロジェクト立ち上げ時からBigQuery含むすべてのサービスにおいてもれなく監査ログを収集しています。

resource "google_project_iam_audit_config" "default" {
  project = google_project.project.project_id
  service = "allServices"
  audit_log_config {
    log_type = "DATA_READ"
  }
  audit_log_config {
    log_type = "DATA_WRITE"
  }
  audit_log_config {
    log_type = "ADMIN_READ"
  }
}

よく使うクエリ

1. 特定のテーブルが参照されたクエリの一覧を取得する

#standardSQL
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail,
  timestamp,
  protopayload_auditlog.servicedata_v1_bigquery.jobGetQueryResultsResponse.job.jobConfiguration.query.query,
FROM
  `audit_sink_project.cloud_audit_logs.cloudaudit_googleapis_com_data_access_202111*`,
  UNNEST(protopayload_auditlog.servicedata_v1_bigquery.jobGetQueryResultsResponse.job.jobStatistics.referencedTables) AS t
WHERE
  1 = 1
  AND t.projectId = "project"
  AND t.datasetId = "dataset"
  AND t.tableId = "table"

protopayload_auditlog.servicedata_v1_bigquery.jobGetQueryResultsResponse.job.jobStatistics.referencedViewsを使用することで参照されたビューを取得することも出来ます。

2. よく利用されているデータセット一覧を取得する

#standardSQL
SELECT
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, '^projects/[^/]+/datasets/([^/]+)/tables') AS dataset_ref,
  COUNT(DISTINCT REGEXP_EXTRACT(protopayload_auditlog.resourceName, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$')) AS active_tables,
  COUNTIF(JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead") IS NOT NULL) AS read_events,
  COUNTIF(JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataChange") IS NOT NULL) AS change_events,
FROM
  `audit_sink_project.cloud_audit_logs.cloudaudit_googleapis_com_data_access_202111*`
WHERE
  1 = 1
  AND (
    JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead") IS NOT NULL
    OR JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataChange") IS NOT NULL
  )
GROUP BY
  dataset_ref
ORDER BY
  read_events DESC

3. テーブルごとの最終アクセス（参照された）日時を取得する

#standardSQL
SELECT
  protopayload_auditlog.authenticationInfo.principalEmail,
  REGEXP_EXTRACT(protopayload_auditlog.resourceName, '^projects/[^/]+/datasets/([^/]+)/tables') AS dataset_ref,
  SPLIT(REGEXP_EXTRACT(protopayload_auditlog.resourceName, '^projects/[^/]+/datasets/[^/]+/tables/(.*)$'), '$')[OFFSET(0)] AS table_ref,
  MAX(timestamp) AS last_accessed_at,
FROM
  `audit_sink_project.cloud_audit_logs.cloudaudit_googleapis_com_data_access_202111*`
WHERE
  1 = 1
  AND JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableDataRead") IS NOT NULL
GROUP BY
  principalEmail, dataset_ref, table_ref
ORDER BY
  last_accessed_at DESC

4. 月別ユーザー別のクエリコスト確認

#standardSQL
SELECT
  TIMESTAMP_TRUNC(TIMESTAMP(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobStats.endTime")), MONTH) AS month,
  protopayload_auditlog.authenticationInfo.principalEmail,
  5.0 * (SUM(CAST(JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobStats.queryStats.totalBilledBytes") AS INT64)) / POWER(2, 40)) AS USD
FROM
  `audit_sink_project.cloud_audit_logs.cloudaudit_googleapis_com_data_access_202112*`
WHERE
  1 = 1
  AND JSON_EXTRACT_SCALAR(protopayload_auditlog.metadataJson, "$.jobChange.job.jobConfig.type") = "QUERY"
GROUP BY
  month,
  principalEmail
ORDER BY
  USD DESC

5. エラーが発生したクエリを特定する

SELECT
  protopayload_auditlog.authenticationInfo.principalEmail,
  resource.labels.project_id,
  timestamp,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message,
FROM
  `audit_sink_project.cloud_audit_logs.cloudaudit_googleapis_com_data_access_202112*`
WHERE
  1 = 1
  AND protopayload_auditlog.methodName = 'jobservice.jobcompleted'
  AND severity = "ERROR"
ORDER BY
  timestamp DESC

おまけ

公式のBigQuery Audit Log Dashboardオススメです。

Discussion