1

I use BigQuery's AuditLog to see access from outside my company to BigQuery with callerIP in "requestMetadata" field in "protoPayload" field, but value in callerIP has disappeared somehow since 2018/9/1 on Japan time when "methodName" in "protoPayload" field is set as "jobservice.jobcompleted".
callerIP is shown as usual when "methodName" is set as others like "jobservice.insert", "jobservice.query" and so on.

Has the specification on BigQuery's AuditLog been changed about showing callerIP?
How should I do to show callerIP again when "methodName" is set as "jobservice.jobcompleted"?
(though it seems seeing "jobservice.query" also works, if I just want to see access from outside my company to BigQuery with callerIP).

cf:AuditLog

Tommy F.
  • 33
  • 5

1 Answers1

1

You can get the desired data back by doing a self join:

SELECT a.protopayload_auditlog.requestMetadata.callerIp 
FROM `your-project.audit.cloudaudit_googleapis_com_data_access_20180904`  a
JOIN `your-project.audit.cloudaudit_googleapis_com_data_access_20180904`  b
ON 
 a.protopayload_auditlog.servicedata_v1_bigquery.jobInsertResponse.resource.jobName.jobId
 =b.protopayload_auditlog.servicedata_v1_bigquery.jobCompletedEvent.job.jobName.jobId

I don't have additional info on why these changes were implemented, but I can confirm that in August both rows showed the callerIp - while now only the jobInsertResponse one does.

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325