How to Use Google BigQuery to Analyze Exported Google Workspace Audit Logs

Introduction

Google Workspace (formerly G Suite) generates millions of audit logs across Gmail, Google Drive, Admin console logins, and Google Meet. By default, Google only retains these logs for six months in most editions. For long-term retention, security incident response, and advanced threat hunting, organizations must export these logs to Google Cloud Platform (GCP). This guide explains how to stream Google Workspace audit logs into Google BigQuery and perform basic analysis using SQL.

Prerequisites

To set up the export, you must be a Google Workspace Super Administrator. You also need an active Google Cloud Platform account with billing enabled, and Owner or BigQuery Admin permissions on the target GCP project.

Step 1: Configure the Export in Google Workspace

First, you must link your Workspace environment to a Google Cloud project to begin streaming logs.

1. Navigate to the Google Workspace Admin Console.
2. Go to Reporting > BigQuery Export.
3. Click Set up export.
4. Select the target Google Cloud Project from the list. If you do not have one, you must create it in the GCP console first.
5. Choose the BigQuery dataset location (e.g., US or EU). It is crucial to select a region that complies with your organization’s data residency requirements.
6. Review the terms and click Save.

It can take up to 48 hours for the logs to begin populating in BigQuery. Once active, Workspace will stream log events continuously.

Step 2: Understand the BigQuery Schema

In the GCP Console, navigate to BigQuery. You will see a new dataset named workspace_audit (or similar, depending on your configuration). Inside this dataset, Google creates partitioned tables for different log types. The most common is the activity table.

The schema relies heavily on nested records. The core fields include:

• time_usec: The timestamp of the event.
• email: The user who performed the action.
• ip_address: The source IP address.
• event_name: The specific action taken (e.g., login_success, doc_download).
• record_type: The service (e.g., login, drive, admin).

Step 3: Analyze Drive Data Exfiltration

To detect potential data theft, you can query BigQuery for massive file downloads from Google Drive.

In the BigQuery query editor, run the following SQL command:


SELECT
  email,
  COUNT(*) AS download_count
FROM
  `your_project.workspace_audit.activity`
WHERE
  record_type = 'drive'
  AND event_name = 'download'
  AND time_usec >= CAST(UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)) AS INT64)
GROUP BY
  email
ORDER BY
  download_count DESC
LIMIT 10;


This query identifies the top 10 users who downloaded the most files in the past 7 days, highlighting potential insider threats.

Step 4: Identify Suspicious Login Locations

You can also use BigQuery to find successful logins from unusual IP addresses.


SELECT
  email,
  ip_address,
  time_usec
FROM
  `your_project.workspace_audit.activity`
WHERE
  record_type = 'login'
  AND event_name = 'login_success'
  AND ip_address NOT LIKE '192.168.%'
ORDER BY
  time_usec DESC
LIMIT 50;


Modify the IP address filter to exclude your corporate VPN or branch office IP ranges to isolate external authentication attempts.

Conclusion

Exporting Google Workspace logs to BigQuery removes the six-month retention limit and empowers security teams to use the full power of SQL for threat hunting. Because BigQuery charges based on the amount of data queried, always use partitioned tables and limit your date ranges to control costs.