gistfile1.txt

/*
 *  This query will return back the project, location, job_type, and a counter of the number of jobs running in the combination
 *  of them for all projects included in the BQ audit log.
 *
 *  This will assist in tracking down jobs running outside of expected regions or show an overview of where job types are
 *  are running throughout your organization.
 *
 *  Instructions:
 *  Replace <project_id> and <audit_log_dataset> with the project and dataset name where your BQ audit log sink is located.
 *  If you have not created one yet, follow the instructions located here:
 *    https://www.googlecloudcommunity.com/gc/Data-Analytics/Creating-a-Sink-for-BigQuery-Audit-Logs-Across-Organizations-or/m-p/648631#M3291
 *  Change the value for interval_in_days to the period in time you wish to search over (starts at current time and goes backwards)
 *
 *  DoiT Navigator customers follow the following instructions to find your audit log sink:
 *    Login to the DoiT Console with an admin account
 *    Click the sprocket at the upper right corner and select "Google Cloud" underneath "Cloud Settings"
 *    Look for the name of the service account associated with your organization (should be at top in the center of screen)
 *    Take the project name out of the service account, for instance it will be doit-customer-project inside of this SA:
 *      doit-cmp@doit-customer-project.iam.gserviceaccount.com
 *    Comment out line 32 and uncomment line 33
 *    Replace <doit_customer_project_id> with this project ID
 *
 *  Note this is an almost exact duplicate of the query located in my main repository here:
 *  https://github.com/doitintl/bigquery-optimization-queries/blob/main/audit_log/jobs_in_regions.sql
 */

-- Change this value to change how far in the past the query will search
DECLARE interval_in_days INT64 DEFAULT 7;

SELECT
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.projectId AS project_id,
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.location AS location,
  UPPER(REPLACE(protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName, '_job_completed', '')) AS job_type,
  COUNT(3) AS job_type_counter
FROM
  `<project_id>.<audit_log_dataset>.cloudaudit_googleapis_com_data_access`
  --`<doit_customer_project_id>.doitintl_cmp_bq.cloudaudit_googleapis_com_data_access`
WHERE
  protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId IS NOT NULL
  AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId NOT LIKE 'script_job_%' -- filter BQ script child jobs
  AND protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.eventName LIKE '%_job_completed'
  AND protopayload_auditlog.authenticationInfo.principalEmail IS NOT NULL
  AND protopayload_auditlog.authenticationInfo.principalEmail != ""
  AND (timestamp) BETWEEN TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL interval_in_days DAY)
        AND CURRENT_TIMESTAMP()
GROUP BY
  1,
  2,
  3