Audit log system table reference

Preview

This system table is in Public Preview. To access the table, the schema must be enabled in your system catalog. For more information, see Enable system table schemas.

This article outlines the audit log table schema and has sample queries you can use with the audit log system table to answer common account usage questions. For information on audit log events, see Audit log reference.

Table path: This system table is located at system.access.audit.

Audit log considerations

• Most audit logs are only available in the region of the workspace.

• Account-level audit logs record workspace_id as 0.

Audit log system table schema

The audit log system table uses the following schema:

Column name	Data type	Description	Example
version	string	Audit log schema version	2.0
event_time	timestamp	Timestamp of the event. Timezone information is recorded at the end of the value with +00:00 representing UTC timezone.	2023-01-01T01:01:01.123+00:00
event_date	date	Calendar date the action took place	2023-01-01
workspace_id	long	ID of the workspace	1234567890123456
source_ip_address	string	IP address where the request originated	10.30.0.242
user_agent	string	Origination of request	Apache-HttpClient/4.5.13 (Java/1.8.0_345)
session_id	string	ID of the session where the request came from	123456789
user_identity	string	Identity of user initiating request	{"email": "user@domain.com", "subjectName": null}
service_name	string	Service name initiating request	unityCatalog
action_name	string	Category of the event captured in audit log	getTable
request_id	string	ID of request	ServiceMain-4529754264
request_params	map	Map of key values containing all the request parameters. Depends on request type	[["full_name_arg", "user.chat.messages"], ["workspace_id", "123456789"], ["metastore_id", "123456789"]]
response	struct	Struct of response return values	{"statusCode": 200, "errorMessage": null, "result": null}
audit_level	string	Workspace or account level event	ACCOUNT_LEVEL
account_id	string	ID of the account	23e22ba4-87b9-4cc2-9770-d10b894bxx
event_id	string	ID of the event	34ac703c772f3549dcc8671f654950f0
identity_metadata	struct	Identities involved in the action, including run_by and run_as. See Auditing group dedicated compute activty.	{run_by: example@email.com; run_as: example@email.com;

Sample queries

The following sections include sample queries you can use to gain insights into your audit logs system table. For these queries to work, replace the values in curly brackets {{}} with your parameters.

Note

Some examples include verbose audit log events, which are not enabled by default. To enable verbose audit logs in a workspace, see Enable verbose audit logs.

This article includes the following example queries:

Who can access this table?

This query uses the information_schema to determine which users have permissions on a table.

SELECT DISTINCT(grantee) AS `ACCESSIBLE BY`
FROM system.information_schema.table_privileges
WHERE table_schema = '{{schema_name}}' AND table_name = '{{table_name}}'
  UNION
    SELECT table_owner
    FROM system.information_schema.tables
    WHERE table_schema = '{{schema_name}}' AND table_name = '{{table}}'
  UNION
    SELECT DISTINCT(grantee)
    FROM system.information_schema.schema_privileges
    WHERE schema_name = '{{schema_name}}'

Which users accessed a table within the last day?

Note

Full names are not captured in the log for DML operations. Include the schema and simple name to capture all.

SELECT
  user_identity.email as `User`,
  IFNULL(request_params.full_name_arg,
    request_params.name)
    AS `Table`,
    action_name AS `Type of Access`,
    event_time AS `Time of Access`
FROM system.access.audit
WHERE (request_params.full_name_arg = '{{catalog.schema.table}}'
  OR (request_params.name = '{{table_name}}'
  AND request_params.schema_name = '{{schema_name}}'))
  AND action_name
    IN ('createTable','getTable','deleteTable')
  AND event_date > now() - interval '1 day'
ORDER BY event_date DESC

Which tables did a user access?

Note

To filter by date range, uncomment out the date clause at the bottom of the query.

SELECT
        action_name as `EVENT`,
        event_time as `WHEN`,
        IFNULL(request_params.full_name_arg, 'Non-specific') AS `TABLE ACCESSED`,
        IFNULL(request_params.commandText,'GET table') AS `QUERY TEXT`
FROM system.access.audit
WHERE user_identity.email = '{{User}}'
        AND action_name IN ('createTable',
'commandSubmit','getTable','deleteTable')
        -- AND datediff(now(), event_date) < 1
        -- ORDER BY event_date DESC

Example result

EVENT	WHEN	TABLE ACCESSED	QUERY TEXT
getTable	2023-05-31	system.access.audit	GET table
getTable	2023-05-31	system.access.table_lineage	GET table
commandSubmit	2023-05-31	Non-specific	show functions;
commandSubmit	2023-05-31	Non-specific	SELECT request_params FROM system.access.audit WHERE service_name = "notebook" AND action_name = "moveFolder" LIMIT 5

View permissions changes for all securable objects

This query will return an event for every permission change that has occurred in your account. The query will return the user who made the change, the securable object type and name, and the specific changes that were made.

SELECT event_time, user_identity.email, request_params.securable_type, request_params.securable_full_name, request_params.changes
FROM system.access.audit
WHERE service_name = 'unityCatalog'
  AND action_name = 'updatePermissions'
ORDER BY 1 DESC

View the most recently run notebook commands

This query returns the most recently run notebook commands and the user who ran the command.

Note

The runCommand action is only emitted when verbose audit logs are enabled. To enable verbose audit logs, see Enable verbose audit logs.

SELECT event_time, user_identity.email, request_params.commandText
FROM system.access.audit
WHERE action_name = `runCommand`
ORDER BY event_time DESC
LIMIT 100

Which users have logged into a Databricks app?

This query returns an event for each login to a Databricks app instance.

SELECT
  event_date,
  workspace_id,
  request_params.request_object_id as app,
  user_identity.email as user_email,
  user_identity.subject_name as username
FROM
  system.access.audit
WHERE
  action_name IN ("workspaceInHouseOAuthClientAuthentication", "mintOAuthToken", "mintOAuthAuthorizationCode")
AND
  request_params["client_id"] LIKE "{{application-ID}}"
GROUP BY
  event_date,
  workspace_id,
  app,
  user_email,
  username

Replace {{application-ID}} with the Application ID value for the service principal assigned to a specific Databricks app. This value can be found in the admin settings for the Databricks workspace hosting the app.

Which Databricks apps have been updated to change how the app is shared with other users or groups?

This query returns events for permission updates to change the access to Databricks apps, including the permission type, the user or group to which the new permission is assigned, and the user who submitted the change.

SELECT
  event_date,
  workspace_id,
  request_params['request_object_id'] as app,
  user_identity['email'] as sharing_user,
  acl_entry['group_name'],
  acl_entry['user_name'],
  acl_entry['permission_level']
FROM
  system.access.audit t
LATERAL VIEW
  explode(from_json(request_params['access_control_list'], 'array<struct<user_name:string,permission_level:string,group_name:string>>')) acl_entry AS acl_entry
WHERE
  action_name = 'changeAppsAcl'
AND
  request_params['request_object_type'] = 'apps'
ORDER BY
  event_date DESC