Activity logs for organizations in Workbench

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

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)

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

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

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

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

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

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')

WITH parsed_logs AS (
    SELECT
        l.change_timestamp,
        l.change_type,
        l.org_user_facing_id,
        l.subject_id,
        JSON_EXTRACT_SCALAR(l.properties, '$.role') AS role,
        JSON_EXTRACT_SCALAR(l.properties, '$.reason') AS reason_for_change,
        l.actor_email,
        l.related_resources
    FROM workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_user_activity_in_org_log_<orgname> l
    WHERE l.change_timestamp >= '2026-03-02T00:00:00Z'
      AND l.change_timestamp < '2026-03-10T00:00:00Z'
      AND l.subject_type = 'ORGANIZATION'
  ),
  extracted_users AS (
    SELECT *,
        JSON_EXTRACT_SCALAR(resource, '$.resourceId') AS user_affected,
        ROW_NUMBER() OVER (PARTITION BY change_timestamp, change_type, subject_id ORDER BY resource) as rn
    FROM parsed_logs,
    UNNEST(JSON_EXTRACT_ARRAY(related_resources)) AS resource
    WHERE JSON_EXTRACT_SCALAR(resource, '$.resourceType') = 'PRINCIPAL'
  )
  SELECT change_timestamp, change_type AS action, org_user_facing_id AS org_name,
         subject_id AS org_uuid, user_affected, role AS role_level,
         actor_email AS admin_who_acted, reason_for_change
  FROM extracted_users
  WHERE rn = 1
  ORDER BY change_timestamp DESC;

WITH parsed_logs AS (
        SELECT
        l.change_timestamp,
        l.change_type,
        l.org_user_facing_id,
        l.subject_id,
        JSON_EXTRACT_SCALAR(l.properties, '$.role') AS role,
        JSON_EXTRACT_SCALAR(l.properties, '$.reason') AS reason_for_change,
        l.actor_email,
        l.related_resources
    FROM workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_user_activity_in_org_log_<orgname> l
    WHERE l.change_timestamp >= '2026-06-17T00:00:00Z'
      AND l.change_timestamp < '2026-06-30T00:00:00Z'
      AND l.subject_type = 'GROUP'
  ),
  extracted_users AS (
    SELECT *,
        JSON_EXTRACT_SCALAR(resource, '$.resourceId') AS user_affected,
        ROW_NUMBER() OVER (PARTITION BY change_timestamp, change_type, subject_id ORDER BY resource) as rn
    FROM parsed_logs,
    UNNEST(JSON_EXTRACT_ARRAY(related_resources)) AS resource
    WHERE JSON_EXTRACT_SCALAR(resource, '$.resourceType') = 'PRINCIPAL'
  )
  SELECT
      e.change_timestamp,
      e.change_type AS action,
      g.internal_name AS group_name,
      e.subject_id AS group_uuid,
      e.user_affected,
      e.role AS role_level,
      e.actor_email AS admin_who_acted,
      e.reason_for_change
  FROM extracted_users e
  LEFT JOIN workbench-bq-log-sink.workbench_monitoring_org_logs_prod.um_workbench_groups_<orgname> g
    ON e.subject_id = g.internal_name
  WHERE e.rn = 1
  ORDER BY e.change_timestamp DESC;

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

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

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

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

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