Organization logs 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.

What are activity logs and why are they useful?

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 Workbench object (e.g., workspace, data collection, storage resource, VM, Dataproc cluster).

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

• 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?

How can I access the activity logs?

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

For organizations, organization admins will see a link to these logs, stored in BigQuery, from the Organizations tab:

Click the Activity Logs link to open these logs in the BigQuery UI. The logs are stored in a single BigQuery table:

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

Using BigQuery, organization admins can review the schema and directly query the table via SQL, save query results or open them in Google Sheets, and more. The table provides information about all the organizations for which you are an admin.

SELECT * FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspace_activity_logs` LIMIT 10

Schema of the activity logs table

workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspace_activity_logs table:

workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspaces table:

workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_resources table:

workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_users table:

workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_user_pod_roles table:

Querying the activity logs table

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.

Running queries from a Workbench app

You can also query the table from within a Workbench app, using the bigquery library, e.g., by running something like the following:

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`
ORDER BY change_date DESC
"""

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

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

Querying on a specific data collection

If you're an owner of multiple data collections, the logs are aggregated in one table. So if you are 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` 

"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`

"How many workspaces are in my org?"

SELECT COUNT(workspace_id)
FROM `workbench-bq-log-sink.workbench_monitoring_org_logs_prod.wsm_workspaces`

"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`
WHERE resource_type IN ('AWS_EC2_INSTANCE', 'AWS_SAGEMAKER_NOTEBOOK', 'AI_NOTEBOOK_INSTANCE', 'DATAPROC_CLUSTER', 'GCE_INSTANCE')