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 examples shows you how to filter for large and complex queries.

  1. Go to the Logs Viewer in the Cloud Platform Console.

  2. On the Stackdriver Logging page, for Select a resource, click Select and choose the appropriate project.

  3. If necessary, select BigQuery from the list of resources that you want to filter.

    Screenshot of Logs Viewer - select BigQuery

  4. 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

  5. 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.

      resource.type="bigquery_resource"
      protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes > 5000000000
      

      Screenshot of Logs Viewer - filter statement example

    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.

      resource.type="bigquery_resource"
      protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier > 1
      

Querying audit logs using BigQuery

To analyze your aggregated usage data using SQL, stream your audit logs 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.

  2. Click Create Export.

  3. In the Edit Export window:

    1. For Sink Name, enter a value such as bigquery_audit_logs.
    2. For Sink Service, choose BigQuery.
    3. For Sink Destination, choose the dataset that will store your audit logs. You can also choose Create new BigQuery dataset to create a dataset to store your audit logs.
    4. Click Create Sink.

      Create sink page

  4. When prompted, click Close to dismiss the confirmation dialog.

  5. Navigate to the dataset in the BigQuery web UI to run queries against your audit logs. Any subsequent activity in BigQuery generates audit logging tables in the dataset you chose. The tables are named:

    • cloudaudit_googleapis_com_activity_[DATE]
    • cloudaudit_googleapis_com_data_access_[DATE]

    Where:

    • [DATE] is the date the activity occurred.

    Audit log tables

Sample audit queries in BigQuery

The following sample queries demonstrate how to retrieve data access usage and query cost data.

Example 1: Hourly Breakdown of Query Costs

#standardSQL
SELECT
  TIMESTAMP_TRUNC(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime, HOUR) as time_window,
  FORMAT('%9.2f',5.0 * (SUM(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes)/1000000000000)) as Estimated_USD_Cost
FROM
  `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD`
WHERE
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY time_window
ORDER BY time_window DESC

The following is an example of the query results.

Screenshot of BigQuery web UI - example hourly query results

Example 2: Cost Breakdown by User

#standardSQL
WITH data as
(
  SELECT
    protopayload_auditlog.authenticationInfo.principalEmail as principalEmail,
    protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent as jobCompletedEvent
  FROM
    `AuditLogsDataSet.cloudaudit_googleapis_com_data_access_YYYYMMDD`
)
SELECT
  principalEmail,
  FORMAT('%9.2f',5.0 * (SUM(jobCompletedEvent.job.jobStatistics.totalBilledBytes)/1000000000000)) as Estimated_USD_Cost
FROM
  data
WHERE
  jobCompletedEvent.eventName = 'query_job_completed'
GROUP BY principalEmail
ORDER BY Estimated_USD_Cost 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