Activity logs for organizations in Workbench

Prior reading: Manage organizations

Purpose: This document explains what activity logs are and how you can use them to gain insight about your organizations.

Introduction

Verily Workbench activity logs are time-stamped logs that can associate a user ID, org ID, operation taken (e.g., create, clone, delete, update access), and a Workbench object (e.g., workspace, data collection, storage resource, VM, Dataproc cluster).

These logs are useful to Organization Admins who want to better understand how active their organization's users are in Workbench. With these logs, admins can write SQL queries to pull results that answer queries such as:

• How many users are in this org?
• How many workspaces have users in this org created?
• How many cloud apps (i.e., notebooks) have users in this org created?

Access the activity logs

Activity logs are accessible at the data collection and organization levels.

For organizations, Org Admins will see a link to these logs, stored in BigQuery, from the Organizations page in the Workbench web UI:

Click the Activity Logs link to open these logs in the BigQuery UI. The logs are stored in BigQuery tables that are separated by organization ID.

Using BigQuery, Org Admins can review the schema and directly query the tables via SQL, save query results or open them in Google Sheets, and more.

Understand table naming with org_id suffix

Each table in the activity logs dataset is separated by organization ID using a table suffix. The table naming pattern is <table_name>_<org_id>. For example, if your organization ID is abc123, the workspace activity logs table would be named:

`workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspace_activity_logs_abc123`

You can find your organization's user-facing ID in the URL when viewing your organization: https://workbench.verily.com/organizations/{your-organization-user-facing-id}

If you're an admin for multiple organizations, you'll need to query each organization's table separately.

SELECT * FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspace_activity_logs_<org_id>` LIMIT 10

Schemas of the activity logs tables

To see CRUD (Create, Read, Update, Delete) activity, query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspace_activity_logs_<org_id> tables:

To see basic workspace properties, query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspaces_<org_id> tables:

To see workspace resources (e.g., apps, buckets, datasets), query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_resources_<org_id> tables:

To get a list of workspace roles, query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.sam_workspace_users_<org_id> tables:

For user information (e.g., user email, orgs, active states), query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_users_<org_id> tables:

For pod information (e.g., pod name, org, billing account), query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_pods_<org_id> tables:

For a list of pods and their users, query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_user_pod_roles_<org_id> tables:

To view audit logs for workspace-level activities, query the workbench-bq-log-sink.workbench_monitoring_org_logs_prod.cloud_audit_logs_<org_id> tables:

Query the activity logs tables

BigQuery UI

You can query the activity logs table directly from the BigQuery panel in the Google Cloud console by clicking the Activity logs link from a data collection that you own.

Run queries from a Workbench app

You can also query the table from within a Workbench app using the bigquery library, as shown below:

import pandas as pd
from google.cloud import bigquery

BQ_dataset = 'workbench-bq-log-sink.workbench_monitoring_org_logs_prod'

job_query_config = bigquery.QueryJobConfig(default_dataset=BQ_dataset)
client = bigquery.Client(default_query_job_config=job_query_config)

Next, define a query on the table:

query = """
SELECT DISTINCT(workspace_user_facing_id), workspace_id, pod_user_facing_id, is_data_collection
FROM `wsm_workspace_activity_logs_<org_id>`
ORDER BY change_date DESC
"""

Then, run the query and view a dataframe of the results:

df = client.query(query).result().to_dataframe()
df

Query on a specific data collection

If you're an owner of multiple data collections, the logs are aggregated in one table. So if you're interested in the usage of a particular data collection, you can first get the user-facing ID of that data collection, and then refine your query to retrieve data for just that data collection. You can find the user-facing ID in the path of the data collection URL like this: https://workbench.verily.com/data-collections/{your-data-collection-user-facing-id}

Example queries

"How many users are in my org?"

SELECT COUNT(user_id)
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_users_<org_id>`

"How many pods are in my org?"

SELECT COUNT(pod_id)
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_user_pod_roles_<org_id>`

"How many workspaces are in my org?"

SELECT COUNT(workspace_id)
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspaces_<org_id>`

"How many cloud apps (i.e., notebooks) have users in this org created?"

SELECT COUNT(resource_id)
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_resources_<org_id>`
WHERE resource_type IN ('AWS_EC2_INSTANCE', 'AWS_SAGEMAKER_NOTEBOOK', 'AI_NOTEBOOK_INSTANCE', 'DATAPROC_CLUSTER', 'GCE_INSTANCE')

"What are the most recent cloud audit log events for my organization?"

SELECT timestamp, workspace_id, user_email, org_id
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.cloud_audit_logs_<org_id>`
ORDER BY timestamp DESC
LIMIT 50

"What workspaces have the most audit log activity?"

SELECT workspace_id, org_id, COUNT(*) as event_count
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.cloud_audit_logs_<org_id>`
GROUP BY workspace_id, org_id
ORDER BY event_count DESC
LIMIT 10

Query comprehensive cloud audit logs

For detailed GCP-level audit logs across your organization, use the workbench_cloud_audit_logs_prod dataset:

"What are the most recent comprehensive cloud audit logs for my organization?"

SELECT timestamp, workspace_id, user_email, org_id
FROM `workbench-bq-log-sink.workbench_cloud_audit_logs_prod.cloud_audit_logs_<org_id>`
ORDER BY timestamp DESC
LIMIT 50

"What are the audit logs for a specific workspace?"

SELECT timestamp, user_email, audit_log
FROM `workbench-bq-log-sink.workbench_cloud_audit_logs_prod.cloud_audit_logs_<org_id>`
WHERE workspace_id = 'your-workspace-id'
ORDER BY timestamp DESC
LIMIT 100

"What BigQuery queries were performed?"

SELECT
  timestamp,
  user_email,
  JSON_VALUE(audit_log, '$.methodName') AS method_name,
  JSON_VALUE(audit_log, '$.status.code') AS status_code,
  JSON_VALUE(audit_log, '$.status.message') AS status_message,
  JSON_VALUE(audit_log, '$.servicedata_v1_bigquery.jobInsertRequest.resource.jobConfiguration.query.query') AS query_text
FROM `workbench-bq-log-sink.workbench_cloud_audit_logs_prod.cloud_audit_logs_<org_id>`
WHERE JSON_VALUE(audit_log, '$.serviceName') = 'bigquery.googleapis.com' and JSON_VALUE(audit_log, '$.methodName') ='jobservice.insert'
ORDER BY timestamp DESC
LIMIT 100

"What Google Cloud Storage (GCS) operations were performed?"

SELECT
  timestamp,
  user_email,
  JSON_VALUE(audit_log, '$.methodName') AS method_name,
  JSON_VALUE(audit_log, '$.resourceName') AS resource_name,
  JSON_VALUE(audit_log, '$.status.code') AS status_code,
  JSON_VALUE(audit_log, '$.status.message') AS status_message
FROM `workbench-bq-log-sink.workbench_cloud_audit_logs_prod.cloud_audit_logs_<org_id>`
WHERE JSON_VALUE(audit_log, '$.serviceName') = 'storage.googleapis.com'
ORDER BY timestamp DESC
LIMIT 100