Problem

When working in SQL Analytics, you want to query the number of users per workspace. In order to do that, you need to identify which system tables to query.

This requirement often arises when managing user access and monitoring workspace usage. 

Cause

The system.access.audit table contains data on the number of users per workspace.

Solution

Query the system.access.audit table with appropriate filters and groupings.

1. Open your Databricks SQL Analytics environment.
2. Use the following SQL query to retrieve the number of users per workspace.

%sql

SELECT
workspace_id,
COUNT(DISTINCT user_identity.email) as user_count
FROM
system.access.audit
WHERE
service_name = 'accounts'
AND action_name = 'tokenLogin'
AND request_params.user LIKE '%@%'
GROUP BY workspace_id;

This query selects the workspace_id and counts the distinct user emails from the system.access.audit table where the service_name is 'accounts' and the action_name is 'tokenLogin'. The request_params.user filter ensures that only valid user entries are considered. Grouping by workspace_id provides the user count for each workspace.

For more information, refer to the Monitor account activity with system tables (AWS | Azure | GCP) documentation.