Use Google Connected Sheets to Leverage Audit logs in BigQuery

Google Workspace administrators must often retain logs beyond the default retention window for extended periods. While most Google Workspace stores Audit logs in the Admin console for up to 6 months, it retains Gmail logs, Data Access logs for 30 days, and Admin Activity logs for 400 days. If you need longer retention, exporting logs to BigQuery offers a scalable solution that allows you to store logs indefinitely.

Setting up this export is free to Google Workspace Plus and Standard customers. While the export service is free, BigQuery is subject to billing for storage and analysis. 

See, Data retention and lag times

What You’ll Learn

This article guides you through setting up this export and utilizing Connected Sheets to analyze your data. You will learn how to:

Set up an export to BigQuery

Export the logs

Use connected sheets to query logs in BigQuery

Setting Up Export to BigQuery

Google Workspace Plus and Standard customers can export logs from the Admin console to BigQuery for free. While exporting logs is cost-free, remember that BigQuery is subject to billing for storage and analysis. Once you export the logs, you can retain them indefinitely, allowing you to manage your data for as long as needed.

See, Set up service log exports to BigQuery

Steps to Export Logs

1. In the Google Admin console, navigate to Reports > Audit logs.
2. Configure the export destination to BigQuery.
3. Specify the Google Cloud Project and dataset to store your logs.
4. Set up the export schedule to ensure regular updates to your log data.

Once exported, you can analyze the logs using Connected Sheets, Google’s powerful integration between Sheets and BigQuery.

See, Using Connected Sheets - Google Cloud Documentation

Using Connected Sheets to Query Logs in BigQuery

After exporting logs to BigQuery, you might need to analyze the data. Connected Sheets makes it easy to query and visualize log data within Google Sheets without specialized SQL knowledge. Let’s explore how to connect a Google Sheet to your logs stored in BigQuery.

How to Connect Google Sheets to BigQuery

1. Open a new Google Sheet.
2. From the Data menu, select Data Connectors, then choose Connect to BigQuery.
   
3. Click Get Connected in the popup.
4. Select the appropriate project and dataset where your logs are stored.
5. Choose the activity table corresponding to the audit logs exported from your Admin console.
   
   

   Note: You may encounter issues with column name lengths when connecting the usage table, but the activity table will work without errors.

6. Click Connect to establish the connection.

Once connected, you'll see a preview of your audit logs, including various types of events such as Drive, Admin, and Meet logs. You can use Sheets' built-in features like filters, pivot tables, and charts to analyze the data.

Querying Logs for Specific Actions

To replicate the search functionality of the Admin console within Google Sheets, you can run custom SQL queries. For example, to search through Drive audit logs and find out which documents a user-edited during a specific time frame, follow these steps:

Querying Drive Audit Logs:

1. In Google Sheets, go to the Data menu, select Data connectors, then Connect to BigQuery.
2. Select the project where your logs are stored.
3. Choose Write custom query instead of selecting a dataset directly.
   
   
4. Paste the following SQL query, replacing my-project-id and my-dataset-name with your specific project and dataset details:
   

   SELECT
   DATETIME(TIMESTAMP_MICROS(time_usec),"America/New_York") AS timestamp,
     email AS actor_email,
     org_unit_name_path,
     event_name,
     event_type,
     record_type,
     drive
   FROM `my-project-id.my-dataset-name.activity`
   WHERE record_type = "drive"

5. Click Connect to run the query and view the results.
6. If needed, use the filter options to refine your data by timestamp
   or actor email.
   

This method allows you to perform a more detailed analysis of your Drive logs over time without the limitations of the Admin console's default search window.

If you need more information on filtering in sheets, see Sort & filter Big Query data in Google Sheets. 

Querying Other Audit Logs

You can follow a similar approach to query other types of logs, such as Admin logs. Modify the SQL query to search for different record types.

Querying Admin Audit Logs

To query Admin Audit logs, paste the following SQL query, replacing my-project-id and my-dataset-name with your specific project and dataset details:

SELECT
  DATETIME(TIMESTAMP_MICROS(time_usec),"America/New_York") AS timestamp,
  email AS actor
_email, org_unit_name_path, event_name, event_type, record_type, admin FROM my-project-id.my-dataset-name.activity WHERE record_type = "admin"

By updating the **record_type** parameter in the query, you can search across different logs such as Admin, Meet, and Calendar logs, depending on your needs.

Conclusion

By exporting Google Workspace audit logs to BigQuery, administrators gain the flexibility to retain data long-term and perform advanced queries. With Connected Sheets, even users unfamiliar with SQL can leverage the power of BigQuery for detailed log analysis. This guide has shown how to set up log exports, connect Google Sheets to BigQuery, and run custom queries to find specific log data. Whether you are tracking document edits or monitoring admin actions, this method ensures you have full control over your organization's audit logs.

Document Version	Date	Description of Change
1.0	9/11/2024	Rewrote, new images, reverify