おかしんワークス

皆さんこんにちは！スタディスト開発ブログ Advent Calendar 2021の2日目を務めさせて頂きます、管理部ビジネス・テクノロジーユニットの @okash1n です。

私の部門では社内のシステム全般の管理を担っています。スタディストは社内のグループウェアとして主にGoogle Workspace（以下GWS）を利用しており、ファイルもGoogle Driveの共有ドライブで管理しています。

今回はGWSのログをBigQueryを用いて調査する方法を紹介していきます。

Google Workspaceをメインのグループウェアとして使っていると様々な理由でユーザーの操作履歴を調べる必要が出てくる場合があります。例えば、以下のようなケースがあります。

• Google Drive上のとあるファイルがいつからか消えてしまっているようだ。行方を探してほしい
• とあるファイルの権限設定変更がいつ行われたのかを調べたい
• 急に共有ドライブのファイル数制限40万個に近づいてしまった。定期的なアップロードが無いか調べて欲しい

こういった依頼はそれなりの頻度で発生します

GWSはすべての有料プランにおいて監査ログを調査する機能を持っており、管理コンソールから「レポート」=>「監査」と進むことで、監査ログを調べることが出来ます。またこのログは特定の条件でフィルタし、かつそのフィルタリング結果をスプレッドシートにエクスポートすることが可能です。

Google Vaultでドライブの監査を行う

Business Plus以上のプランであれば、Google Vault（以下Vault）というデータガバナンスの為の機能が付随しています。

Vaultはアプリ一覧画面からアクセス出来ます。

ほとんどの場合はこれら2つで事足りるのですが、いずれも簡易的な（とはいえ他のSaaSよりははるかに優れた）フィルタしか備えていないので、より詳細に特定の条件で調査を行いたい時は、スプレッドシートをいくつも作ってルックアップするなどといった回り道が発生します。

しかしBigQueryを利用することで、最初からSQLを用いて目的の結果にたどり着くことが出来る（かもしれません）ので、今回はそちらの方法を紹介していきます。

※ただし残念ながら、以下で紹介する機能はGWSのEnterprise Standard以上のプランでのみ利用可能となっています。

以下の手順を参考にセットアップします。

BigQuery プロジェクトをログレポート用に設定する

新しく「Google Workspace Logs」というプロジェクトを作成して、必要なメンバーを招待します（スタディストの場合はGCPは組織管理しているので、親の組織から継承されているユーザーがほとんどです）

GWSのログをBigQueryに書き出すためには、GWSの特権管理者アカウントがプロジェクトに編集者以上の権限で招待されている必要があります。

GCPのプロジェクトメニューの「IAMと管理」からGWS特権管理者アカウントを招待しておきましょう。

サービスログの BigQuery への書き出しを設定する

GWSの監査ログと使用状況レポートをBigQueryに書き出す為、GWSの管理コンソールでBigQuery Exportの設定を行います。

https://admin.google.com/ac/reporting/bigqueryexport

上記のURLから設定します。

最初は「無効」になっているので、画面右端の鉛筆アイコンから設定を変更します。

次に、前項で作成したGCPプロジェクトのIDやデータセット名、データセットの地理的ロケーション制限を設定します。

データセット名とはBigQueryにおけるテーブルなどの上位概念であり、データベース名のようなイメージなので、他のGCPプロジェクトのデータセット名と被らないよう、用途がわかりやすい一意のデータセット名をつけてください。

また、必要な場合はデータセットの地理的ロケーションを「米国」もしくは「欧州」に限定することが可能です。GDPRなどの絡みもあって欧州にするメリットは特に無いので、私は明示的に「米国」に設定しました。この設定をしない場合はデータセットの地理的ロケーションは恐らく米国になると思うのですが、資料が見つかりませんでした。（もし見つけた方がいたら教えて下さい）

設定が終わったら「保存」します。

設定後BigQueryに反映されるまで最大48時間かかる為、しばらく待ちます。

反映されたかどうかを確認するには、BigQueryにデータセットが作成されたかどうかを確認すれば分かります。

GCPにはGoogle Cloud ShellというGCPのリソース操作をコマンドラインで行う為のVMが用意されており、誰でも無料で使えるので、こちらで確認していきます。

画面右上のシェルっぽいアイコンをクリックしてCloud Shellを開きます。BigQueryの操作は bq というコマンド群を使いますが、Cloud Shellに対してBigQueryの操作権限を与える必要があるので、まずはプロジェクト内のデータセット一覧を表示するコマンド bq lsを打ちます。

「Cloud Shellの承認」というポップアップが出てくるので「承認」します。

こちらのコマンドで先程指定したデータセット名が表示されればGWSログの書き出しの設定が反映されています。GWS側での反映が終わっていない場合はコマンドは何も出力しません（同プロジェクト内に他のデータセットがある場合はそれらが出力されます）

続けて bq ls "dataset名" というコマンドを打ちます。これはデータセットの中にあるテーブルの一覧を表示するコマンドです。

Google Workspace からは activity と usageという2つのテーブルが作成されるので、この2つが表示されていればOKです。弊社の環境の場合は、 activity が送られてくるまで24時間、 usage が送られてくるまで48時間かかりました。

2つのテーブルが送られていることが確認できたら、ようやくBigQueryを用いた調査を行うことが出来ます。

https://support.google.com/a/answer/9079965

こちらのページにクエリ例が掲載されていますので、アカウントの状況を確認するクエリを実行してみます。

すると以下のような実行結果が表示されます。

近々機会があればさらに詳しくBQを用いた調査について書きたいと思いますが、今回はここまでとさせていただきます。また、今回の記事を皮切りに、社内システム関連の記事もどんどん書いていこうと思いますのでよろしくおねがいします。

スタディストでは今後も “新しいなにか” をともに積み上げる仲間を募集しております！様々な職種で採用をオープンしているのでぜひお気軽にお声がけください。

採用情報｜株式会社スタディスト

株式会社スタディストの採用サイトです。私たちと一緒に“知的活力みなぎる社会”をつくっていける仲間を募集しています。

https://recruit.studist.jp

明日以降もAdvent Calendarの記事が続々と公開される予定なので、よければTwitterアカウントのフォローをお願いします！

https://twitter.com