Query history audits allow you to generate a report of all jobs run using your Ads Data Hub account. This allows you to answer questions relating to who accessed your data and when they did it.
Query history audits are written as BigQuery tables containing log entries for all queries run using your Ads Data Hub account. To view query history audits for your account, you need to first generate the report via an API. Each audit log contains 1 day’s worth of data. You can generate an audit log for any day within the past 30 days.
Query history audits are only available to superusers. Learn more about role-based access
Query history audit format
Each query history audit uses the following schema:
| Field name | Description |
|---|---|
| customer_id | The Ads Data Hub customer ID |
| ads_customer_id | The ID of the sub-account, if used (will be identical to customer_id otherwise) |
| match_table_customer_id | The ID of the account containing the match table, if used (will be identical to customer_id otherwise) |
| user_email | Email address of the user who ran the query |
| query_start_time | The time the query began running |
| query_end_time | The time the query finished running |
| query_type | Differentiates between analysis queries and audience queries |
| query_resource_id | The ID associated with the query |
| query_text | The query’s SQL |
| query_parameters | |
| query_parameters.name | The name of the query’s parameter |
| query_parameters.value | The value passed via the query’s parameter row_merge_summary |
| row_merge_summary.column_name | The name of column |
| row_merge_summary.merge_type | The type of row merge summary |
| row_merge_summary.constant_value | The value of the constant set (will be null if no constant is used) |
| destination_table | The location (in BigQuery) that the query was written to |
Accessing query history audits
In order to access the query history audits, you’ll need to call the API. Find sample code for calling the API below, or view the reference documentation and write your own query.
The results of the API request will be written to the BigQuery dataset that you specify in the body of the API request.
"""This sample shows how to create a query history audit.
For the program to execute successfully, ensure that you run it using Python 3.
"""
from __future__ import print_function
from json import dumps
from google_auth_oauthlib import flow
from googleapiclient.discovery import build
appflow = flow.InstalledAppFlow.from_client_secrets_file(
# Replace client_secrets.json with your own client secret file.
'client_secrets.json',
scopes=['https://www.googleapis.com/auth/adsdatahub'])
appflow.run_local_server()
credentials = appflow.credentials
developer_key = input('Developer key: ').strip()
service = build('adsdatahub', 'v1', credentials=credentials,
developerKey=developer_key)
def pprint(x):
print(dumps(x, sort_keys=True, indent=4))
customer_id = input('Customer ID (e.g. "customers/123"): ').strip()
bq_project = input('Destination BigQuery project ID (e.g. "your-project"): ').strip()
dataset_id = input('Destination BigQuery dataset (e.g. "your-dataset"): ').strip()
start = input('The start date for your query history audit. Formatted as "mm/dd/yyyy": ').strip().split('/')
end = input('The end date for your query history audit. Should be 1 day later than start_date. Formatted as "mm/dd/yyyy": ').strip().split('/')
choice = input("Do you want to enter a timezone? Defaults to UTC otherwise. (y/n) ")
if choice.lower() == 'y':
timezone = input("Timezone (e.g. 'UTC'): ")
else:
timezone = 'UTC'
body = {
'project_id': bq_project,
'dataset': dataset_id,
'start_date': {
'year': start[2],
'day': start[1],
'month': start[0]
},
'end_date': {
'year': end[2],
'day': end[1],
'month': end[0]
},
'time_zone': timezone
}
pprint(service.customers().exportJobHistory(customer=customer_id, body=body).execute())