Skip to content

Instantly share code, notes, and snippets.

@eblaauw

eblaauw/bq-top-spenders.sql

Created December 23, 2020 09:28
Show Gist options
  • Save eblaauw/4e58599434716102be7ff915b6a19daa to your computer and use it in GitHub Desktop.
Save eblaauw/4e58599434716102be7ff915b6a19daa to your computer and use it in GitHub Desktop.
Download ZIP
Raw
bq-top-spenders.sql
SELECT
protopayload_auditlog.authenticationInfo.principalEmail as user,
sum(CAST(5.0* (protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobStatistics.totalProcessedBytes/POWER(2,40)) AS numeric )) AS queryCostInUSD,
FROM
`PROJECT_ID.stackdriver_logging.cloudaudit_googleapis_com_data_access_202012*`
WHERE
protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.eventName="query_job_completed"
AND protopayload_auditlog.servicedata_v1_bigquery .jobCompletedEvent.job.jobStatistics.totalProcessedBytes IS NOT NULL
GROUP BY
protopayload_auditlog.authenticationInfo.principalEmail
ORDER BY
queryCostInUSD DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment