Problem
One of my client’s recently had a problem that a large number of Azure DevOps work items had been updated via Excel in error.
They asked if there was a means to undo these edits. Unfortunately, a feature Azure DevOps does not provide.
Solution
So, I wrote a PowerShell script to do it. The script…
- Gets a list of work items edited today by a specific user, the one who did the problematic bulk update, using a Work Item Query
- Get the last update of each work item and check it was made by the user who did the bulk edit, incase someone manually fixed the problematic update already.
- For a limited list of fields, revert the change to the value prior to the last update
- Save the updated work item, or if the
-whatif
flag is set just validate the update against the Azure DevOps instance
For my client, the script worked well enough, reverting over 1000 work items in about 5 minutes. The few work items it could not revert were fixed manually.
The common factor in the work items that it could not revert was that they all had rich text/HTML based descriptions, though so did many that successfully reverted.
I suspect there is an edge case related to the encoding of some character(s) content. However, I have not been able to reproduce the problem as yet on my test rig.
The good news is that if the revert of a work item does fail, the target work item is left unchanged. So allowing either repeated revert attempts with an updated version of the script, or for a manual fix can be done.
You can find the PowerShell script here as a Gist
[CmdletBinding()] | |
<# | |
.SYNOPSIS | |
Reverts work items to their previous state in Azure DevOps based on the specified criteria. | |
.DESCRIPTION | |
Reverts work items to their previous state in Azure DevOps based on the specified criteria. | |
.PARAMETER pat | |
The Personal Access Token (PAT) used to authenticate with Azure DevOps. | |
.PARAMETER organizationUrl | |
The URL of the Azure DevOps organization | |
.PARAMETER projectName | |
The name of the Azure DevOps project. | |
.PARAMETER changedBy | |
The name of the user who made the changes to the work items. | |
.PARAMETER logFile | |
(Optional) The path to the log file where the script will write the results. | |
.PARAMETER whatif | |
(Optional) If the -whatif switch is provided, the script will only test the process without making any changes. | |
.EXAMPLE | |
.\revert-workitems.ps1 -pat "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" -organizationUrl "https://dev.azure.com/contoso" -projectName "MyProject" -changedBy "John@consoto.com" -logFile "C:\Logs\revert.log" | |
This example reverts work items in the "MyProject" Azure DevOps project that were changed by the user "John@consoto.com". The script will authenticate using the provided PAT and organization URL, and write the results to the specified log file. | |
#> | |
param ( | |
[Parameter(Mandatory = $true)] | |
$pat, | |
[Parameter(Mandatory = $true)] | |
$organizationUrl , | |
[Parameter(Mandatory = $true)] | |
$projectName , | |
[Parameter(Mandatory = $true)] | |
$changedBy, | |
[Parameter(Mandatory = $false)] | |
$logFile = "", | |
[Parameter(Mandatory = $false)] | |
[switch]$whatif # if -whatif is provided, will only test the process | |
) | |
function Revert-WorkItem { | |
[CmdletBinding()] | |
param ( | |
$pat, | |
$organizationUrl , | |
$projectName , | |
$workItemId, | |
$fieldsofInterest = @( | |
"System.WorkItemType", | |
"System.Title", | |
"System.Description", | |
"Microsoft.VSTS.Common.AcceptanceCriteria", | |
"System.AssignedTo", | |
"System.State", | |
"Custom.DeployedStatus", | |
"System.Tags", | |
"System.IterationPath", | |
"System.AreaPath", | |
"Microsoft.VSTS.Common.Severity", | |
"Microsoft.VSTS.Common.Resolution", | |
"Custom.RCACategory", | |
"Custom.RCADevelopment"), | |
[switch]$whatif | |
) | |
# Import necessary modules | |
Import-Module -Name PowerShellGet | |
$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($pat)")) | |
# Define headers for HTTP request | |
$headers = @{ | |
"Authorization" = "Basic $token" | |
"Content-Type" = "application/json-patch+json" | |
} | |
Write-Host "Getting work item $workItemId..." -ForegroundColor green | |
# Fetch work item current state | |
$currentState = Invoke-RestMethod -Uri "$organizationUrl/$projectName/_apis/wit/workitems/$workItemId`?api-version=6.0" -Method Get -Headers $headers | |
# Fetch work item last update | |
$updates = Invoke-RestMethod -Uri "$organizationUrl/$projectName/_apis/wit/workitems/$workItemId/updates`?api-version=6.0" -Method Get -Headers $headers | |
# you can't trust the rev to be the update number, so can't use /updates/rev. Have to get the last one re | |
$lastupdate = $updates.value[-1] | |
if ($updates.count -lt 2) { | |
Write-host "- SKIP - WI has not been updated since initial creation" -ForegroundColor yellow | |
return 1 | |
} | |
elseif ($lastupdate.revisedBy.uniqueName.tolower() -ne $changedBy.tolower()) { | |
Write-host "- SKIP - As WI has been update by $($lastupdate.revisedBy.displayName) since updated by $changedBy" -ForegroundColor yellow | |
return 0 | |
} else { | |
Write-host "- Last update by: $($lastupdate.revisedBy.displayName)" -ForegroundColor green | |
Write-host "- Fields of Interest updated: " -ForegroundColor green | |
# build the payload, starting with revision counter which must be present and correct | |
$payload = @() | |
$payload += @{ | |
op = "test" | |
path = "/rev" | |
value = $currentState.rev | |
} | |
# iterate arosss the field property and list the changes | |
foreach ($field in $lastupdate.fields.PSObject.Properties.Name) { | |
if ($field -notin $fieldsofInterest) { | |
continue | |
} | |
foreach ($change in $lastupdate.fields.$field) { | |
Write-Host "-- $field $($change.oldValue) -> $($change.newValue)" -ForegroundColor green | |
# some fields if being cleared required extra processing | |
if ($lastupdate.fields.$field.oldValue -ne $null) { | |
$value = $lastupdate.fields.$field.oldValue | |
} | |
else { | |
$value = '' | |
} | |
$payload += @{ | |
op = "replace" | |
path = "/fields/$field" | |
value = $value | |
} | |
} | |
} | |
if ($payload.Count -eq 1) { | |
Write-Host "- No changes to revert" -ForegroundColor Yellow | |
return 1 | |
} | |
else { | |
# write the WI back to the server | |
$json = $payload | ConvertTo-Json -Depth 10 | |
Write-Verbose $json | |
if ($whatif.IsPresent) { | |
Write-Host "Would reverting work item $workItemId... (dry-run validate only)" -ForegroundColor Yellow | |
$payload | ForEach-Object { | |
write-host "- $($_.path) : $($_.value)" -ForegroundColor Yellow | |
} | |
# run the rest call in test mode | |
try { | |
Invoke-RestMethod -Uri "$organizationUrl/$projectName/_apis/wit/workitems/$workItemId`?validateOnly=true&api-version=6.0" -Method Patch -Headers $headers -Body $json | |
} catch { | |
Write-host "Error validating update request with Azure DevOps $($_.ErrorDetails.Message)" -ForegroundColor red | |
return -1 | |
} | |
} | |
else { | |
Write-Host "Reverting work item $workItemId..." -ForegroundColor green | |
try { | |
Invoke-RestMethod -Uri "$organizationUrl/$projectName/_apis/wit/workitems/$workItemId`?api-version=6.0" -Method Patch -Headers $headers -Body $json | |
} catch { | |
Write-host "Error updating request on Azure DevOps $($_.ErrorDetails.Message)" -ForegroundColor red | |
return -1 | |
} | |
} | |
return 2 | |
} | |
} | |
} | |
function Get-WorkItems { | |
[CmdletBinding()] | |
param ( | |
$pat, | |
$organizationUrl , | |
$projectName, | |
$wiql | |
) | |
# Import necessary modules | |
Import-Module -Name PowerShellGet | |
$token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($pat)")) | |
# Define headers for HTTP request | |
$headers = @{ | |
"Authorization" = "Basic $token" | |
"Content-Type" = "application/json" | |
} | |
$json = @{query = $wiql } | ConvertTo-Json -Depth 10 | |
Write-Verbose $json | |
# build a list of workitems based on a wiql query | |
$response = Invoke-RestMethod -Uri "$organizationUrl/$projectName/_apis/wit/wiql`?api-version=6.0" -Method Post -Headers $headers -Body $json | |
$response | Select-Object -ExpandProperty workItems | Select-Object -ExpandProperty id | |
} | |
write-host "Searching for work items to revert that were edited tody by $changedBy " -ForegroundColor green | |
$workitems = Get-WorkItems -pat $pat -organizationUrl $organizationUrl -projectName $projectName -wiql "SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject] = '$projectName' AND [System.ChangedDate] = @today AND [System.ChangedBy] = '$changedBy'" | |
# Delete $logFile if present | |
if ($logFile.Length -gt 0) { | |
if (Test-Path $logFile) { | |
Write-host "Deleting old logfile $logfile" | |
Remove-Item $logFile -Force | |
} | |
} | |
$results = @{} | |
foreach ($id in $workitems) { | |
$return = Revert-WorkItem -pat $pat -organizationUrl $organizationUrl -projectName $projectName -workItemId $id -whatif:$($whatif.IsPresent) | |
switch ($return) { | |
-1 { $results[$id] = "Errored" } | |
1 { $results[$id] = "Not Reverted" } | |
2 { $results[$id] = "Reverted" } | |
Default { $results[$id] = "Skipped" } | |
} | |
if ($logFile.Length -gt 0) { | |
# Append the value of $return to a CSV file | |
$results | Select-Object @{Name = "WorkItemId"; Expression = { $id } }, @{Name = "Result"; Expression = { $results[$id] } } | Export-Csv -Path $logFile -Append -NoTypeInformation | |
} | |
} | |
Write-host "Completed - Found $($workitems.Count) work items" | |
write-host "- Reverted $(($results.GetEnumerator() | ?{$_.Value -eq "Reverted"}).Count) as last edit was by $changedBy" | |
write-host "- Skipped $(($results.GetEnumerator() | ?{$_.Value -eq "Not Reverted"}).Count) as no tracked fields were updated in last edit by $changedBy" | |
write-host "- Skipped $(($results.GetEnumerator() | ?{$_.Value -eq "Skipped"}).Count) as last edit was not by $changedBy" | |
write-host "- Errored $(($results.GetEnumerator() | ?{$_.Value -eq "Errored"}).Count) as could not save updated Work Item" | |
if ($logFile.Length -gt 0) { | |
write-host "Details of WI updates written to $logfile" | |
} | |