【BigQuery】ドライブ監査ログをエクスポートしてみた(前編)

はじめに

Googleドライブの監査ログをBigQueryに出力する機会があったので
その過程で調査した内容の備忘録になります。

ちなみにGoogleドライブの監査ログを利用するには、以下の3つの方法があります。
1. Google管理コンソールのレポート機能^[1]
2. Reports API経由でのログ取得^[2]
3. BigQueryへのExport機能^[3]

上記の1と2はBusinessエディションで利用できますが
3はEnterpriseエディションである必要があります。

そのため、なかなか手の出せない企業が多いように思います。
本投稿はBigQueryへのExport機能で実施するとどんな感じになるのかを
まとめてみました。

今回のやりたきこと

まず、やりたかったことは以下の3点になります。

1. BigQuery Export機能の設定
2. ドライブ監査ログのスキーマ定義情報の確認
3. VPC Service Controlsを有効化した場合の設定

今回の前編では上記1と2を紹介し、後編で3について紹介します。

2については、Reports APIで取得した場合との差分を確認しました。
対象イベントは、監査業務でよく利用する下記4つのイベントとしています。

#	イベント名	説明
1	view	ドライブ内のファイルの参照操作
2	download	ドライブ内のファイルのダウンロード操作
3	print	ドライブ内のファイルの印刷操作
4	upload	ドライブへのファイルのアップロード操作

実行環境

Product	version
Google Workspace	Enterprise Edition
BigQuery	2021年12月1日時点

【構成図】

【補足】
・ Google WorkspceとBigQueryは同じ組織に所属しています。
・ BigQuery Export機能を有効化するとactivityとusageの2つのテーブルが作成されます。

実施手順

1. Google Cloudのプロジェクト作成
2. BigQuery Exportの有効化
3. BigQueryのスキーマ確認
4. BigQueryのテーブル参照

【補足】
・ 本手順は、Google Workspaceの特権管理者ロールで作業しています。
・ Google Cloudのプロジェクト課金が有効化されていることを前提としています。

1. Google Cloudのプロジェクト作成

• Google Cloudのコンソールにログインします。
• [IAMと管理] > [プロジェクトを作成]に移動します。

• Google Workspaceの組織が指定されている状態で新しいプロジェクトを作成します。

• [ビッグデータ] > [BigQuery]を開き、作成したプロジェクトができていることを確認します。

• プロジェクトがあればOKです。

2. BigQuery Exportの有効化

• 次は、Google Workspaceの管理コンソールにログインします。
• Enterpriseエディションの場合、[レポート] > [BigQuery Export]があります。

• ❶にチェックを入れます。
• ❷にプロジェクトID、❸には新たに作成するデータセット名を入力して、[保存]します。

• 新たにデータセット (今回はtest_dataset01) ができていれば完了です。

【補足】
・ Google Workspaceと同一組織内のBigQueryにしか指定できません。
・ 出力先のBigQuery データセットは1つしか指定できません。
・ 出力先のロケーションは米国(デフォルト) もしくは 欧州連合しか指定できません。

3. BigQueryのスキーマ確認

• 監査ログが出力されるには1日〜数日かかる場合があります。(規則性まで把握できていません)
• ログが出力されると2つのテーブルが作成されます。(監査ログはactivityに出力されます)

• この時点で以下の2点が想定外でした。
  ・ 時刻フィールド (time_usec)がTIMESTAMP型ではなくINTEGER型だった。
  ・ 送信元IPアドレスフィールド (ip_address)がSTRING型ではなくBYTES型だった。

• サービス固有のフィールドはサービスごとに親フィールド(RECORD型)が作成されます。

• ドライブ監査ログ (driveフィールド配下) のスキーマ定義は以下となっていました。

#	フィールド名	種類	モード
01	doc_id	STRING	NULLABLE
02	target_user	STRING	NULLABLE
03	old_value	STRING	NULLABLE
04	new_value	STRING	NULLABLE
05	doc_type	STRING	NULLABLE
06	doc_title	STRING	NULLABLE
07	primary_event	BOOLEAN	NULLABLE
08	target_domain	STRING	NULLABLE
09	source_folder_title	STRING	NULLABLE
10	source_folder_id	STRING	NULLABLE
11	destination_folder_title	STRING	NULLABLE
12	destination_folder_id	STRING	NULLABLE
13	owner	STRING	NULLABLE
14	dlp_info	STRING	NULLABLE
15	owner_is_team_drive	BOOLEAN	NULLABLE
16	owner_is_shared_drive	BOOLEAN	NULLABLE
17	old_visibility	STRING	NULLABLE
18	visibility	STRING	NULLABLE
19	visibility_change	STRING	NULLABLE
20	removed_role	STRING	NULLABLE
21	added_role	STRING	NULLABLE
22	membership_change_type	STRING	NULLABLE
23	target	STRING	NULLABLE
24	team_drive_id	STRING	NULLABLE
25	shared_drive_id	STRING	NULLABLE
26	originating_app_id	STRING	NULLABLE
27	team_drive_settings_change_type	STRING	NULLABLE
28	shared_drive_settings_change_type	STRING	NULLABLE
29	old_settings_state	STRING	NULLABLE
30	new_settings_state	STRING	NULLABLE
31	usage_billing_entity_id	INTEGER	NULLABLE
32	usage_billing_metric_container.usage_billing_metric_id	STRING	NULLABLE
33	usage_billing_metric_container.integer_application_metric	INTEGER	NULLABLE
34	billable	BOOLEAN	NULLABLE
35	sheets_import_range_recipient_doc	STRING	NULLABLE
36	category_name	STRING	NULLABLE
37	attribute_id	STRING	NULLABLE
38	old_attribute_value	STRING	NULLABLE
39	new_attribute_value	STRING	NULLABLE
40	old_publish_visibility	STRING	NULLABLE
41	new_publish_visibility	STRING	NULLABLE
42	actor_is_collaborator_account	BOOLEAN	NULLABLE
43	label	STRING	NULLABLE
44	field_id	STRING	NULLABLE
45	old_field_value	STRING	NULLABLE
46	new_field_value	STRING	NULLABLE
47	is_encrypted	BOOLEAN	NULLABLE
48	storage_entity_id	INTEGER	NULLABLE
49	storage_usage_in_bytes	INTEGER	NULLABLE
50	label_title	STRING	NULLABLE
51	reason	STRING	NULLABLE
52	field	STRING	NULLABLE
53	old_value_id	STRING	NULLABLE
54	new_value_id	STRING	NULLABLE

• テーブルのパーティショニングは取り込み日で実施されています。

項目	値
テーブルタイプ	分割
分割基準	DAY
フィールドで分割	_PARTITIONTIME
パーティションの有効期限	60日
パーティションフィルタ	省略可

• デフォルトでは60日でデータが消えてしまいます。
• 期限を変えたい場合は以下コマンドで設定変更してください。

パーティションの有効期限を90日に延長

bq update --time_partitioning_expiration 7776000 composed-arbor-332218:test_dataset01.activity

4. BigQueryのテーブル参照

• 先にも記載したよく利用する4つのイベントに絞ってテーブルを参照してみます。

よく利用するイベントに絞ったテーブル参照

SELECT
  TIMESTAMP_MICROS(time_usec) AS timestamp,
  email,
  org_unit_name_path,
  ip_address,
  event_type,
  event_name,
  record_type,
  drive.doc_id,
  drive.doc_type,
  drive.doc_title,
  drive.primary_event,
  drive.owner,
  drive.owner_is_team_drive,
  drive.owner_is_shared_drive
FROM
  `composed-arbor-332218.test_dataset01.activity`
WHERE
  record_type = "drive"
  AND event_name IN ("view", "download", "print", "upload")
ORDER BY
  1

• 以下、SQL実行結果になります。

行	timestamp	email	org_unit_name_path	ip_address	event_type	event_name	record_type	drive.doc_id	drive.doc_type	drive.doc_title	drive.primary_event	drive.owner	drive.owner_is_team_drive	drive.owner_is_shared_drive
1	2021-11-29 05:24:54.254 UTC	[e-mail]	test.com	MTc1LjE3Ny40NC4yMcj=	access	view	drive	14VOBRruaEcKl6NBACdejdZXeI42AhlwWTmohtUl3g1M	document	test_file01	true	[e-mail]	false	false
2	2021-11-29 05:25:00.442 UTC	[e-mail]	test.com	MTc1LjE3Ny40NC4yMcj=	access	download	drive	14VOBRruaEcKl6NBACdejdZXeI42AhlwWTmohtUl3g1M	document	test_file01	true	[e-mail]	false	false
3	2021-11-29 05:25:57.421 UTC	[e-mail]	test.com	MTc1LjE3Ny40NC4yMcj=	access	print	drive	14VOBRruaEcKl6NBACdejdZXeI42AhlwWTmohtUl3g1M	document	test_file01	true	[e-mail]	false	false
4	2021-11-29 05:26:04.430 UTC	[e-mail]	test.com	MTc1LjE3Ny40NC4yMcj=	access	upload	drive	14VOBRruaEcKl6NBACdejdZXeI42AhlwWTmohtUl3g1M	document	test_file01	true	[e-mail]	false	false

【補足】
・ 値が含まれないフィールドはSELECT句で除外しています。
・ 時刻はUNIXTIME(マイクロ秒)からISO8601に変換した上で表示しています。

まとめ

さて、いかがでしたでしょうか？

全イベントで差分比較した訳ではないので、詳細を見ていくと違いがあるかもしれませんが
基本的にはReports APIで取得していた監査ログは確認できるのではないでしょうか？

ip_addressフィールドを文字列で表示するため
NET.IP_TO_STRING^[4]で変換しようと思ったんですが、以下のエラーとなりました。

NET.IP_TO_STRING() encountered an invalid IP address. Expected 4 (for IPv4) or 16 (for IPv6) bytes but got 14

14バイトになっている原因までは追えなかったので、こちらは追加検証にしようと思います。

後編では、VPC Service Controlsで制御した状態で
BigQueryにExportできるようにするための設定について紹介します^^

脚注

1. ドライブの監査ログ

2. Reports API

3. BigQueryでのGoogle Workspaceのログとレポート

4. NET.IP_TO_STRING