Skip to content

Instantly share code, notes, and snippets.

@sinmetal

sinmetal/select-from-auditlog-where-billing.sql

Created February 12, 2016 04:58
Show Gist options
  • Save sinmetal/9f2603e9f56d5062c21c to your computer and use it in GitHub Desktop.
Save sinmetal/9f2603e9f56d5062c21c to your computer and use it in GitHub Desktop.
Download ZIP
BigQuery Audit Logから現在のBillingと、NewBillingの値を算出するQuery
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment