How to find who deletes a table in the logs

Question

I have a dataset ID, tables were created. Which were deleted. I need to check who deleted them and when.

Answer 1

You need to enable auditlogs export.

There are many types of deletes:

• delete from UI / API
• delete with QUERY (overwrite as destination table)
• delete via CREATE OR REPLACE syntax
• autodelete done via expiration

Common type of deletes are explained here:

Type 1: you issued a table deleted Query/API call etc..

Then you can run a query like:

SELECT * FROM `dataset.cloudaudit_googleapis_com_activity_20190919` 
where resource.type='bigquery_resource' 
and protopayload_auditlog.methodName='tableservice.delete'

you get back a large table which cannot be posted here as a nice image, but as simplified JSON it is here:

[
  {
    "logName": "projects/editedname/logs/cloudaudit.googleapis.com%2Factivity",
    "resource": {
      "type": "bigquery_resource",
      "labels": {
        "project_id": "editedname",
      }
    },
    "protopayload_auditlog": {
      "serviceName": "bigquery.googleapis.com",
      "methodName": "tableservice.delete",
      "resourceName": "projects/editedname/datasets/dataset/tables/industry2",
      "authenticationInfo": {
        "principalEmail": "[email protected]",
        "authoritySelector": null,
        "serviceAccountKeyName": null,
        "serviceAccountDelegationInfo": []
      },
      "authorizationInfo": [
        {
          "resource": "projects/editedname/datasets/dataset/tables/industry2",
          "permission": "bigquery.tables.delete",
          "granted": "true",
          "resourceAttributes": null
        }
      ],
      "requestMetadata": {
        "callerIp": "1.2.3.4",
        "callerSuppliedUserAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/77.0.3865.75 Safari/537.36,gzip(gfe)",
        "callerNetwork": null,
        "requestAttributes": null,
        "destinationAttributes": null
      },
    },
    "timestamp": "2019-09-19 08:47:00.381 UTC",
    "receiveTimestamp": "2019-09-19 08:47:00.590316 UTC",
    "severity": "NOTICE",
  }
]

As you can see you have the table that was deleted eg: industry2 in my example, also the user's or service account email address, and the date, even metadata of the caller by IP, and userAgent.

More about the available information here

Type 2: You issued a table override query or a CREATE OR REPLACE syntax

For these kind of "deletes" you won't find a separate deleted entry. But you can look for the "truncated" flag in the metadataJson