How to: Personal Access Tokens Expiration Mapping

This script and report list all Personal Access Tokens of all users on the Azure DevOps organization to visualize next expirations

An original script is available on my GitHub repository. See below this script:

Let’s go understand each command used.

1. PowerShell script will receive the following parameters:

• $PAT = Personal Access token to connect on Azure DevOps;
• $Organization = Organization URL to list all Personal Access Tokens of all users on the Azure DevOps organization;
• $Connstr = connection string to Azure SQL Database that stores the report information. To create this report, it’s necessary to create previously a Azure SQL Server and Database and run a script below:

2. Users List = uses this REST API to list all users on the Azure DevOps organization

$AzureDevOpsAuthenicationHeader = @{Authorization = 'Basic ' + [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(":$($PAT)")) }
$UriUsers = "https://vssps.dev.azure.com/$($Organization)/_apis/graph/users?api-version=6.1-preview.1"
$UsersResult = Invoke-RestMethod -Uri $UriUsers -Method get -Headers $AzureDevOpsAuthenicationHeader 
Foreach ($user in $UsersResult.value)
{
    Write-Host $user.displayName
}

3. Personal Access Tokens List = Uses this REST API to List all the session token details of the Personal Access tokens (PATs) for a particular user.

$UriUserPAT = "https://vssps.dev.azure.com/$($Organization)/_apis/tokenadmin/personalaccesstokens/$($user.descriptor)?api-version=6.1-preview.1"    
$UserPATResult = Invoke-RestMethod -Uri $UriUserPAT -Method get -Headers $AzureDevOpsAuthenicationHeader    
Foreach ($up in $UserPATResult.value)    
{
    Write-Host $up.displayName
}

4. After extracting all users and respective Personal Access Tokens, this information is stored in a table in Azure SQL.

5. After insert information into a table, I connected this database on Power BI:

• User Name (1) = Filter report using User Name field;
• Personal Access Tokens Informations (2) = List all information about Personal Access Tokens (User Name, User Email, Token Name, Creation Token Date, Expiration Date, and Token Scope);
• Expiration Tokens by Month Year = uses this Pie chart to filter the next tokens that will expire.