BigQuery Audit Logから現在のBillingと、NewBillingの値を算出するQuery

select-from-auditlog-where-billing.sql

SELECT
  protoPayload.authenticationInfo.principalEmail,
  protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId,
  protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier,
  protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes,
  ROUND(((protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes*5)/1000000000000),2) Cost_In_Dollars_Now,
  ROUND(((protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.totalBilledBytes*(5*protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier))/1000000000000),2) Cost_In_Dollars_New,
  protoPayload.serviceData.jobCompletedEvent.job.jobConfiguration.query.query,
FROM TABLE_DATE_RANGE(bigquery.cloudaudit_googleapis_com_data_access_, DATE_ADD(CURRENT_TIMESTAMP(), -3, 'DAY'), CURRENT_TIMESTAMP())
WHERE
  protoPayload.serviceData.jobCompletedEvent.eventName = 'query_job_completed'
ORDER BY
  protoPayload.serviceData.jobCompletedEvent.job.jobStatistics.billingTier DESC