# 31 Audit-log export to SIEM/BigQuery

> v0.1.3 · role: Detect · edition: Ent/Edu/Frontline Std+, Ent Ess Plus · [policy: #18 · #33](https://docs.google.com/spreadsheets/d/1nOztaPd1Y7eNeRSR_hdovYy-ncpx-bAx/edit?usp=sharing&ouid=115159875779023172526&rtpof=true&sd=true)

Workspace keeps its audit logs for a bounded period and offers only a console search over them. BigQuery Export streams the activity and usage events — login, admin, Drive, token — into a GCP project you control, where they outlive Google's retention window and can be queried properly. It is the substrate the WORM archive ([№58](worm-log-archive.md)) and the detection rules ([№66](detection-engineering.md)) are built on, and the dataset region should match your data-region policy.

Documentation: [About reporting logs and BigQuery](https://knowledge.workspace.google.com/admin/reports/about-reporting-logs-and-bigquery)

## Caveats

- Enabling the export backfills history — roughly 180 days of activity data and 450 days of usage data land in BigQuery on first setup; anything older than those windows is gone for good.
- Activity events land in BigQuery within about 10 minutes; usage reports start after a 48-hour initial delay and can then lag 1–3 days — this is an archive and investigation substrate; alerting still comes from Alert center rules (№2, №3).
- BigQuery Export requires Enterprise Standard+, Education Standard+, Frontline Standard+ or Enterprise Essentials Plus — on other editions the Data integrations screen exists but the export card is gated.
- The exported dataset is only as immutable as the GCP project it lands in — a super admin who can delete the project can delete the evidence, which is what the Bucket-Locked sink in №58 is for.

## Setup steps

1. Open Data integrations and configure BigQuery Export, pointing it at a GCP project you control (billing enabled). — `Reporting › Data integrations`

   BigQuery Export = On; Project = <dedicated logging project>; grant gapps-reports@system.gserviceaccount.com the project Editor role (or an equivalent IAM editor role) on that project

   docs: [Set up service log exports to BigQuery](https://knowledge.workspace.google.com/admin/reports/set-up-service-log-exports-to-bigquery) · [Set up a BigQuery project for reporting logs](https://knowledge.workspace.google.com/admin/reports/set-up-a-bigquery-project-for-reporting-logs)

2. Select the dataset location and confirm the export covers activity events and usage reports. — `Reporting › Data integrations`

   - **Dataset region** = matching your data-region policy (№24)
   - **export** = the date-partitioned activity + usage tables

   docs: [Set up service log exports to BigQuery](https://knowledge.workspace.google.com/admin/reports/set-up-service-log-exports-to-bigquery)

3. Sanity-check that the source logs (login, admin, Drive, token) are populated in-console before relying on the export; then wait one full day and confirm the activity table has a partition for that date. — `Reporting › Audit and investigation`

   Verify the day's partition exists in the activity table (query _PARTITIONTIME)

   docs: [About the audit and investigation tool](https://knowledge.workspace.google.com/admin/reports/about-the-audit-and-investigation-tool) · [Example queries for reporting logs in BigQuery](https://knowledge.workspace.google.com/admin/reports/example-queries-for-reporting-logs-in-bigquery)

4. Set the BigQuery dataset/table retention beyond Workspace's own log-retention window (that is the entire point of the export), and feed the WORM archive ([№58](worm-log-archive.md)) and detection rules ([№66](detection-engineering.md)) from it.

   Dataset default table expiration = never (or > your retention policy); onward sink to Bucket-Locked GCS

   docs: [Data retention and lag times](https://knowledge.workspace.google.com/admin/reports/data-retention-and-lag-times)

## Ongoing maintenance

- **[automatable: script]** Daily: alert if the newest exported event is older than 24h (fold into [№52](alert-pipeline-heartbeat.md) heartbeat).

## How to verify

1. Query the newest partition and check freshness — an export that silently stopped is the failure mode that matters.

   ```
   bq query --use_legacy_sql=false 'SELECT MAX(time_usec) FROM `<project>.<dataset>.activity`'
   ```

## Settings screens

- Reporting > Data integrations (BigQuery Export)
  - console: https://admin.google.com/ac/reporting/bigqueryexport
  - screenshot: ../screenshots/admin.google.com/ac/reporting/bigqueryexport.png
- Reporting > Audit and investigation > User log events (verify events are flowing before/after export)
  - console: https://admin.google.com/ac/sc/investigation
  - screenshot: ../screenshots/admin.google.com/ac/sc/investigation.png