- Teams are supported for Enterprise plans only.
- Only team root or admin can export team audit logs to BigQuery
To monitor recent activity for a team app, you can use AppSheet's Audit History, as described in Monitor team app activity using Audit History.
Alternatively, you can export the Audit History logs for your team to BigQuery to manage and analyze the data.
Benefits of using BigQuery include:
- Monitor recent activity across multiple team apps
- Use your existing monitoring tools to analyze the data
- Query the Audit History logs for specific data across team apps
- Extend the retention period for your AppSheet audit logs
Export your team audit logs to BigQuery as described in the following sections:
- Configure the export of team audit logs to BigQuery
- Run a query on the exported data
- View and edit a team connection
- Delete a team connection
Configure the export of team audit logs to BigQuery
To configure the export of team audit logs to BigQuery, perform the following steps:
- Step 1: Create a BigQuery dataset in your Google Cloud project
- Step 2: Connect to the BigQuery dataset from your AppSheet team
- Step 3: Update the BigQuery dataset permissions in the Google Cloud console
- Step 4: Test the connection to the BiqQuery dataset
Step 1: Create a BigQuery dataset in your Google Cloud project
To create a BigQuery dataset in your Google Cloud project:
- In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
Go to Google Cloud console
- Open the BigQuery page in the console.
Go to the BigQuery page
- Create a new dataset for your AppSheet data, as described in Creating datasets.
For example:
Step 2: Connect to the BigQuery dataset from your AppSheet team
To connect to the BigQuery dataset from your AppSheet team:
- Sign in to AppSheet.
- Select My team in the account profile drop-down.
- Click the Settings tab.
- Under Configure Audit Log Export, click Add a new export.
- Enter values for the following fields:
Field
Description
GCP Project ID
ID of your Google Cloud project.
BigQuery Dataset Name
Name of the BigQuery dataset to connect to. For example: appsheet_logs
Table Name
Name of the table. Defaults to audit_log_export.
- Click Save.
AppSheet generates a new service account in an AppSheet-owned Google Cloud project. This service account is associated with your AppSheet team account. - Click Copy to copy the service account name. It is needed in the next step.
Step 3: Update the BigQuery dataset permissions in the Google Cloud console
To update the BigQuery dataset permission in the Google Cloud console:
- Open the BigQuery page in the console.
Go to the BigQuery page
- Select the new BigQuery dataset in the Explorer panel.
- Select Sharing > Permissions.
- Click +Add principal.
- In the New Principals field, paste the service account name that you copied in the last step.
- In the Role drop-down, select BigQuery Data Editor.
Type the role name in the Filter Type to filter field to easily locate it in the list of roles. - Click Save.
Step 4: Test the connection to the BiqQuery dataset
To test the connection to the BigQuery dataset:
- Sign in to AppSheet.
- Select My team in the account profile drop-down.
- Click the Settings tab.
- Under Configure Audit Log Export, click Test associated with the configured export that you want to test.
The following message displays if the connection is successful: Connection successful
If an error is returned, ensure that the BigQuery dataset has been created and the appropriate permissions have been set.
Run a query on the exported data
After you have configured the export of team audit logs to BigQuery, you can run an interactive or batch query on the dataset.
_PARTITIONTIME
of null. If you use the default query set by BigQuery, you will not get results. To run an interactive query:
- Open the BigQuery page in the console.
Go to the BigQuery page
- Select the audit_log_export table for the BiqQuery dataset in the Explorer panel.
- Click Query > In new tab or Query > In split tab.
- Update the query details. (See the sample query below.)
- Click Run.
Sample query
The following sample query:
- Selects all columns from the audit_log_export table in the
dataset-id
dataset in theproject-id
project that have no_PARTIONTIME
(new logs) or that were logged before the current date.To populate
project-id.dataset_id
, you can copy the string by selecting More > Copy ID for the dataset in the BigQuery Explorer panel. The result will include both values in the proper format. - Orders the events so that the most recent are first.
- Limits the results to 1000 events.
SELECT
*
FROM
`project-id.dataset-id.audit_log_export`
WHERE
_PARTITIONTIME IS NULL OR DATE(_PARTITIONTIME) <= CURRENT_DATE()
ORDER BY OperationTimestamp DESC
LIMIT 1000;
Sample query results
The following shows the Query results:
View and edit the audit log export configuration for a team
To view and edit the audit log export configuration for a team:
- Sign in to AppSheet.
- Select My team in the account profile drop-down.
- Click the Settings tab.
- Under Configure Audit Log Export, click the configured export that you want to edit.
The Edit audit log export dialog displays. - Edit the configured export details.
- Click Save.
Delete an audit log export configuration for a team
To delete an audit log export configuration for a team:
- Sign in to AppSheet.
- Select My team in the account profile drop-down.
- Click the Settings tab.
- Under Configure Audit Log Export, click Delete associated with the configured export that you want to delete.
- When prompted, confirm the action.