Follow When security logs sit in BigQuery (from a Cloud Logging sink, a Google Workspace export or your own pipeline), export them with EXPORT DATA. It writes newline-delimited JSON to a Cloud Storage bucket, and you download that. For small results, bq query –format=json prints the rows directly. Either way, filter on the table’s time or partition column first, because BigQuery bills by data scanned.
BigQuery is where many Google Cloud teams keep log history long after Cloud Logging’s default retention has passed. Prime Core has more than 24,000 unique detection rules, mapped to 676 MITRE ATT&CK techniques. Below is how to get a slice out, sanitize it, and investigate it for free in LogTotal.
Before you start
Permissions. You need to run jobs (BigQuery Job User), read the dataset (BigQuery Data Viewer), and write to the export bucket.
Know the tables. A Cloud Logging sink to BigQuery creates tables named after the logs, for example cloudaudit_googleapis_com_activity, often partitioned by timestamp. Workspace exports have their own schema.
Check the cost first. Add –dry_run to a bq query to see how many bytes it would scan. If the number makes you wince, add a tighter time filter.
Option 1: EXPORT DATA to Cloud Storage
EXPORT DATA OPTIONS (
uri = 'gs://my-bq-exports/audit-20261019/*.json',
format = 'JSON',
overwrite = true
) AS
SELECT *
FROM `my-project.audit_logs.cloudaudit_googleapis_com_activity`
WHERE timestamp >= TIMESTAMP('2026-10-19') AND timestamp < TIMESTAMP('2026-10-20');
The URI needs a wildcard (*), because BigQuery may write several files. Download and combine them:
gcloud storage cp 'gs://my-bq-exports/audit-20261019/*' ./audit-20261019/
cat ./audit-20261019/*.json > bq_audit_20261019.jsonl
Option 2: bq query for small results
bq query --nouse_legacy_sql --format=json --max_rows=10000 \
'SELECT * FROM `my-project.audit_logs.cloudaudit_googleapis_com_activity`
WHERE timestamp >= TIMESTAMP("2026-10-19") AND timestamp < TIMESTAMP("2026-10-20")' \
> bq_audit_small.json
jq -c '.[]' bq_audit_small.json > bq_audit_small.jsonl
–format=json prints a JSON array, which jq flattens to one row per line.
Upload, sanitize and review in LogTotal
- Upload. Open logtotal.com and drop in the .jsonl file.
- Sanitize.
- Review.
When it goes wrong
| Symptom | Likely cause | Fix |
|---|---|---|
| Query scans the whole table | No filter on the partition or time column | Filter on timestamp (or _PARTITIONTIME) |
| EXPORT DATA fails on the URI | No wildcard in the URI | Use …/*.json |
| Permission denied writing to the bucket | Missing Storage object create rights | Grant write access on the bucket |
| bq output cut at 100 rows | Default –max_rows | Set –max_rows, or use EXPORT DATA |
| Nested fields look odd | Repeated and nested columns keep their structure | Expected; it’s still valid JSON |
FAQ
Is this the same as exporting from Cloud Logging?
No. Cloud Logging has its own export. This guide is for logs that already live in BigQuery tables.
Can I export a whole table without a query
Yes: bq extract –destination_format NEWLINE_DELIMITED_JSON copies a table or a partition to Cloud Storage. For investigations, a filtered query is usually better.
