Extracting bugs from Azure Devops into CSV with their time stamped change of states

Question

Back ground

I'm essentially doing a review where, i see how long it took team A to address a bug from it being assigned to them. I need a query which can produce these results, which in turn will be downloaded into a CSV file.

Task

I need to essentially extract bugs in devops with their respective flow of state ('Raised'-'Ready for Test'-'in progress'-etc.) also, along with their corresponding time stamps. So for bug x i can see it was assigned to member p on xx/xx/xx with status 'in progress' and then it was changed to 'ready for test' on xx/xx/xx by member p, or whatever the route of the states, and assigned persons on that bug was.

this is akin to a performance review. Is there any way this can be achieved using queries?

Answer 1

I've never seen a way to extract history from the Azure DevOps (ADO) Boards Query UI. You can query history, but that isn't what you're looking to do.

That said, you can easily do this through the ADO API for work items. Below is an example PowerShell script.

function Get-WorkItemRevisions {
    param (
        [Parameter(HelpMessage = 'Personal Access Token for Azure DevOps. Default=uses a pat token stored in environment variables named AZURE_DEVOPS_EXT_PAT')]
        [String] $PersonalToken = $Env:AZURE_DEVOPS_EXT_PAT,

        [Parameter(HelpMessage = 'Core uri for Azure DevOps. Default=dev.azure.com')]
        [String] $CoreServer = 'dev.azure.com',

        [Parameter(HelpMessage = 'Organization to access.')]
        [string]$Organization = 'PUT_YOUR_ORGNAME_HERE',

        [Parameter(Mandatory = $true)]
        [String] $WorkItemId,

        [Parameter(HelpMessage = 'The Azure DevOps API version to use. Default=6.0')]
        [String] $ApiVersion = '6.0'
    )
    $token = [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($PersonalToken)"))
    $headers = @{authorization = "Basic $token" }

    $uri = "https://$coreServer/$Organization/_apis/wit/workitems/${WorkItemId}/revisions/?api-version=$ApiVersion"
    Write-Verbose -Message "API URL: $uri"
    
    $result = Invoke-RestMethod -uri $uri -Headers $headers -Method Get
    $result
}

$bugs = @(
    '4390'
    '7399'
    '3675'
)

foreach ($bugId in $bugs) {
    $revisions = Get-WorkItemRevisions -WorkItemId $bugId -Verbose 

    $prevState = ''
    foreach ($revision in $revisions.value) {
        $id = $revision.id
        $rev = $revision.rev
        
        $changedDate = $revision.fields.'System.ChangedDate'
        $changedBy = $revision.fields.'System.ChangedBy'.displayName
        $assignedTo = $revision.fields.'System.AssignedTo'.displayName
        $state = $revision.fields.'System.State'

        # Only write to CSV if the state has changed
        if ($prevState -ne $state){
            Add-Content -Path performance-review.csv -Value "$id, $rev, $changedDate, $changedBy, $state, $assignedTo"
            $prevState = $state
        }
    }
}

The one thing that you'll need to do in order to run this script is to:

1. generate a Personal Access Token (PAT)
2. --either-- add the PAT to an environmental variable named $Env:AZURE_DEVOPS_EXT_PAT
3. --or-- call the Get-WorkItemRevisions function with a -PersonalToken parameter.
4. Change PUT_YOUR_ORGNAME_HERE to your organization name, you can find that in the ADO uri after https://dev.azure.com/{orgname}

Below is example output of the script above. Note: I added a header row for clarity and changed the real names to Disney character names.

Using PowerShell like this would probably be better for what you are doing than using the ADO Boards Query UI as you could run this script on a scheduled basis and post the results to Slack, MS Teams, email it out, etc. You could expand the script by using the ADO API for Query Wiql to query the bugs rather than hardcoding them.

An alternative to the code above would be the ADO API for reporting or connect PowerBI to your ADO data and build reporting.