Big Query audit report supporting query - fixes for the updated Cloud Audit schema.

data_access_query_billing_report.sql

SELECT
  timestamp as Date,
  resource.labels.project_id as ProjectId,
  protopayload_auditlog.serviceName as ServiceName,
  protopayload_auditlog.methodName as MethodName,
  protopayload_auditlog.status.code as StatusCode,
  protopayload_auditlog.status.message as StatusMessage,
  protopayload_auditlog.authenticationInfo.principalEmail as UserId,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId as JobId,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query as Query,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.projectId as DestinationTableProjectId,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.datasetId as DestinationTableDatasetId,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.destinationTable.tableId as DestinationTableId,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.createDisposition as CreateDisposition,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.writeDisposition as WriteDisposition,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.dryRun as DryRun,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.state as JobState,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.code as JobErrorCode,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatus.error.message as JobErrorMessage,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.createTime as JobCreateTime,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.startTime as JobStartTime,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.endTime as JobEndTime,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.billingTier as BillingTier,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes as TotalBilledBytes,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes as TotalProcessedBytes,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000 as TotalBilledGigabytes,
  (protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000) / 1000 as TotalBilledTerabytes,
  ((protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalBilledBytes / 1000000000) / 1000) * 5 as TotalCost,
  1 as Queries
FROM (TABLE_QUERY([YOUR DATASET HERE],'table_id CONTAINS "cloudaudit_googleapis_com_data_access_"'))
WHERE
  protopayload_auditlog.serviceName = 'bigquery.googleapis.com'
  AND protopayload_auditlog.methodName = 'jobservice.jobcompleted'
  AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName	= 'query_job_completed'