Use Google Connected Sheets to Leverage Audit logs in BigQuery

Most Google Workspace Audit logs are retained in the Admin console for 6 months, and only 30 days for Gmail logs search. This limitation can be overcome by exporting your logs to BigQuery, where you can retain them as long as you like. 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. 

Once you have your logs in BigQuery, you might struggle to make use of the data. This guide's purpose is to help you get started. Let's start with this use case: using Connected Sheets to query logs you have stored in BigQuery. 

To get started:

1. Open a new Google Sheet.
2. From the Data menu, select Data connectors, then Connect to BiqQuery.
   
   

   Your Admin console logs export to two tables: activity and usage. The activity table corresponds to Audit logs in the Admin console, while usage corresponds to aggregate reports. You will not be able to make a direct connection to the usage table due to column name length limitations in Google Sheets. However, you can select the activity table without encountering errors. 

3. Browse to your GCP project and dataset where you Admin console log export lives

4. Select the activity table as pictured below.
   

5. Click Connect.

   A preview of all your various Audit logs (Drive, Admin, Meet, etc.) is displayed together in a giant table. You can use a variety of tools such as filters, charts, and pivot tables to analyze your data.

   In our case, we want to search our Audit logs to research an action that a user or admin has taken, similar to how we do in the Admin console. Here are some samples of how to replicate a simple log search experience.

Drive Audit logs

Let’s say we need to know what documents a user has edited during a two-week window 7 months ago. Here is how we can connect a Google sheet to just our Drive Audit logs. 

1. In the same sheet, select Connect to BigQuery from the Data menu. 
   Data > Data Connectors > Connect to BigQuery.
2. This time select the project where your Admin console logs live but do not select the dataset. 
3. Instead, click Write custom query. 
   
4. The BigQuery query editor opens.
   .

5. In the query editor copy and paste the following query substituting your project id and dataset name. 

        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"
   
   This is what it will look like after you paste the query in the editor. 
   

6. Click the green Connect button in the lower right corner. 

7. You can now filter by condition on the timestamp,
   
    and by value on the actor email.
   
   The filter by value panel opens at the right for you to action.
   
   If you need more information on filtering in sheets, see Sort & filter Big Query data in Google Sheets. 

Other types of logs

Similarly, you can connect other Audit logs such as Admin. Just change your SQL query a bit. Notice that the SELECT statement below ends with admin instead of drive. Also note the final line in the SQL statement: WHERE record_type = “admin”

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"

Now you know how to quickly search all of the older logs you have stored in BigQuery.

Document Version	Date	Description of Change