GCPの監査ログからOrganization全体のjob_idとクエリ文字列の対応表を作成する

BigQueryの INFORMATION_SCHEMA.JOBS_XXX 系のメタデータビューを使うとジョブに関する様々なデータを取得できます。
しかし、queryはJOBS_BY_PROJCETにしか存在しないので少々不便です。

なので、監査ログからjob_idとquery相当の文字列を取得してみることにします。

queryやjob_idが格納されている場所がクエリの投げ方によって異なるので、可能性のある箇所を列挙してcoalesce関数に入れています。

    select
      coalesce(
        protopayload_auditlog.servicedata_v1_bigquery.jobInsertRequest.resource.jobName.jobId,
        protopayload_auditlog.servicedata_v1_bigquery.jobInsertResponse.resource.jobName.jobId,
        protopayload_auditlog.servicedata_v1_bigquery.jobQueryResponse.job.jobName.jobId,
        protopayload_auditlog.servicedata_v1_bigquery.jobGetQueryResultsResponse.job.jobName.jobId,
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId
       ) as job_id,
      coalesce(
        protopayload_auditlog.servicedata_v1_bigquery.jobInsertRequest.resource.jobConfiguration.query.query,
        protopayload_auditlog.servicedata_v1_bigquery.jobInsertResponse.resource.jobConfiguration.query.query,
        protopayload_auditlog.servicedata_v1_bigquery.jobQueryResponse.job.jobConfiguration.query.query,
        protopayload_auditlog.servicedata_v1_bigquery.jobGetQueryResultsResponse.job.jobConfiguration.query.query,
        protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobConfiguration.query.query
      ) as query,
      timestamp,
    from <Data Access Audit log> where protopayload_auditlog.methodName in('jobservice.insert', 'jobservice.query', 'jobservice.getqueryresults', 'jobservice.jobcompleted')

あとは、 INFORMATION_SCHEMA.JOBS_ORGANIZATION にクエリを実行するときにjob_idを使ってJOINすればqueryを補うことができます。