Admin Console Logs in BigQuery

Education Standard and Education Plus editions can export Google Workspace logs and usage reports to BigQuery. This feature allows schools to analyze and get insight into activity across their domain without being limited to the default expiration times for Admin console data.

Getting started with BigQuery 

Use the articles below to learn more about using BigQuery. 

Troubleshooting and FAQs

Do I need to purchase Google Cloud Platform (GCP) if I want to leverage BigQuery?

Previously, you could get started for free using BigQuery Sandbox. In August 2022, Google removed the separate Gmail log export to BigQuery and began including Gmail logs in the same project as other Workspace Admin log exports. It's unclear at this time if using the Sandbox to get started will continue to be an option. If you are a CDW Amplified for Education customer and want to purchase GCP usage for log exports to BigQuery, contact your Google Customer Success Specialist (GCS). If you don't have your GCS's direct email, contact cdwg@amplifiedit.com and your GCS will receive your message.

Is exported data retained longer than Admin console data? How long?

The retention period depends on how you configure your data exports with BigQuery. If you set up a BigQuery export using Sandbox, you may notice that data retention is only 60 days.

How can I verify my data retention time in BigQuery?

  1. Login to https://console.cloud.google.com/bigquery with the account that manages your BigQuery project.
  2. Go to the project where your data is stored.
  3. Expand the project to view the datasets under it. Then, expand the dataset where your Google logs live. 
    AdminConsoleLog1.png
  4. Click the ellipsis to the left of the activity table and select open.
  5. Select the details tab and look for Partition expiration. If the Partition expiration is blank, data retention is indefinite.
    AdminConsoleLog2.png

How do I change the retention time for my exported data?

You'll need to change the expiration date for each table in the export: activity and usage. To change the expiration date on the activity table to one year, run the query below with your unique project ID and dataset name. You can also run the same command against the usage table.

Screen_Shot_2021-08-13_at_4.36.19_PM.png

Visit this Google Cloud document for additional information.

I have Workspace Plus and use BigQuery for exporting reports; how much would the Cloud Platform project cost?

Predicting costs for exported logs and their use can be tricky. Both storage and Analysis (queries) impact billing. The storage is the cheapest and most predictable. You can use Sandbox for 60 days to see how much data gets dumped monthly. So if there is 1GB of data after 60 days, you can store up to a year of logs and stay under the 10GB Free storage in BigQuery. The query costs can vary greatly depending on how you access the data. Looker Studio will refresh every 12 hours, which means you will have a query of your BigQuery data every 12 hours. There is a new Looker Studio feature now called Extract Data. You can take any data source, including BigQuery, and extract the data once a week or once a month. Using the Extract Data feature can help keep the query cost down.

Additional Help with BigQuery

CDW Amplified for Education customers with a support contract can contact their account manager to schedule a BigQuery Support Stack.

 

Document Version Date Description of Change
1.0 3/18/2024 Updated several links to open in a new tab
1.1 3/18/2024 Removed About section with Slides link. That presentation was too old.
1.2 4/18/2024 Edited for clarity, verified article

 

Articles in this section