Analyzing Audit Logs Using BigQuery

Cloud Audit Logs are a collection of logs provided by Google Cloud Platform that lets you establish a paper trail of usage for your Google Cloud projects. Audit logs for BigQuery comprise two logs, Administrative Activity and Data Access, both of which are enabled automatically.

Audit logs are available via Google Cloud Logging, where they can be immediately filtered to provide insights on specific jobs or queries, or exported to Google Cloud Pub/Sub, Google Cloud Storage, or BigQuery.

Using Cloud Logging to find large queries in BigQuery

You can leverage Cloud Logging to filter your audit logs to find specific jobs that match your criteria, whether it’s expensive queries, specific failure modes, or unintended access patterns. The following example shows you how to filter for large queries.

  1. Go to the Logs Viewer in the Cloud Platform Console.
  2. Select BigQuery from the list of resources that you want to filter.

    Screenshot of Logs Viewer - select BigQuery

  3. Switch to Advanced Filtering by clicking on the drop-down arrow in the Filter box and selecting Convert to advanced filter. Screenshot of Logs Viewer - select advanced filter

  4. Enter filter statements to look for particular patterns of usage.

    1. Example 1: Show events from users that have run queries exceeding 5GB.

      Enter the following filter statements in the Filter by box and click Submit Filter.

      metadata.serviceName="bigquery.googleapis.com"
      protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes > 5000000000
      
    2. Example 2: Show events related to complex queries (requiring higher billing tier).

      Enter the following filter statements in the Filter by box and click Submit Filter.

      metadata.serviceName="bigquery.googleapis.com"
      protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier > 1
      

    Screenshot of Logs Viewer - filter statement example

Querying audit logs using BigQuery

To analyze your aggregated usage data using SQL, set up export of audit logs back to BigQuery. For more information about setting up exports from Cloud Logging, see Overview of Logs Export in the Cloud Logging documentation.

Set up streaming of audit logs to BigQuery

  1. Go to the Logs Viewer in the Cloud Platform Console and click on Exports in the navigation bar. Screenshot of Logs Viewer - exports tab
  2. Configure options under Select service:
    1. Select BigQuery as the source of your Export.
    2. Click the Add Item button and select cloudaudit.googleapis.com/data_access. Screenshot of Logs Viewer - select service options
  3. Set up Select export destinations:
    1. Under Stream to BigQuery dataset, select Add new dataset.
    2. Type the name of new dataset to create for the audit logs (for example, AuditLogs) and click Create.
    3. Click Save to finish setting up the export. Screenshot of Logs Viewer - select export destination
  4. Navigate to the dataset in the BigQuery web UI to start sending queries about your data access usage and charges.

Sample audit queries in BigQuery

Example 1: Charges for BigQuery usage over the last 7 days

SELECT
  query_date,
  ROUND(((total_bytes*5)/1000000000000),2) Cost_In_Dollars
FROM (
  SELECT
    STRFTIME_UTC_USEC(metadata.timestamp,"%Y-%m-%d") AS query_date,
    SUM(protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes) AS total_bytes
  FROM
    TABLE_DATE_RANGE(AuditLogs.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())
  WHERE
    protoPayload.serviceData.jobCompletedEvent.eventName = 'query_job_completed'
  GROUP BY
    query_date )

The following is an example of the query results.

Screenshot of BigQuery web UI - example 1 query results

Example 2: Charges for BigQuery by user over the last 7 days

SELECT
  protoPayload.authenticationInfo.principalEmail User,
  ROUND((total_bytes*5)/1000000000000, 2) Total_Cost_For_User,
  Query_Count
FROM (
  SELECT
    protoPayload.authenticationInfo.principalEmail,
    SUM(protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes) AS total_bytes,
    COUNT(protoPayload.authenticationInfo.principalEmail) AS query_count,
  FROM
    TABLE_DATE_RANGE(AuditLogs.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY'), CURRENT_TIMESTAMP())
  WHERE
    protoPayload.serviceData.jobCompletedEvent.eventName = 'query_job_completed'
  GROUP BY
    protoPayload.authenticationInfo.principalEmail)
ORDER BY
  2 DESC

For more information about which elements can be queried in the audit logs, see AuditData in the Cloud Logging documentation.

Send feedback about...

BigQuery Documentation