🔍
BigQueryにシンクしたデータアクセス監査ログでよく使うクエリ5選
株式会社マネーフォワードでデータエンジニアをしている太田です。
BigQuery Advent Calendar 2021 15日目の記事です。
当初、「VPC Service ControlsとBigQuery」をテーマに記事を書いておりましたが内容に誤りがあったためテーマ変更してお送りします。
当初のテーマの記事は後ほど内容整えて別途公開したいと思います。
急遽テーマ変更したため、薄い内容の記事になってしまったこと、アドベントカレンダー記事として公開が遅れたことお許しください🙏
さて、今回は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