Introduction

You want to create a Databricks function to alert you when created personal access tokens (PATs) are about to expire.

Instructions

You can create a custom function. Use Databricks system tables and SQL alerts to set alerts when a PAT is about to expire. 

First get the expiry date of any PATs using the following query. Make sure to save the SQL query through the SQL editor UI. Adjust current_date()+X to the number of days in the future the PATs are scheduled to expire. For example, +3 returns any PAT due to expire in three days.

SELECT from_unixtime(request_params.tokenExpirationTime / 1000) as expiry_date
FROM system.access.audit
WHERE 
 service_name = 'accounts'
 AND action_name = 'generateDbToken'
 AND to_date(from_unixtime(request_params.tokenExpirationTime / 1000)) = current_date()+X;

The following screenshot shows the code with +3, and the output of one column with expiration dates.

Then, to create a notification, leverage SQL alerts. Set a trigger condition according to your use case. For details about SQL alerts, refer to the “Create an alert” section of the Databricks SQL alerts (AWS | Azure | GCP) documentation.