'Azure DevOps - The time a WorkItem spends in specific columns on the kanban board

I would like to report about the days features spend in each column of our kanban-board.

So for an example output I want, we have a kanban with columns:

Funnel           --> Workitem X spend 10 days in here
Analyzing        --> Workitem X spend 13 days in here
Backlog          --> Workitem X spend  3 days in here
Implementing     --> Workitem X spend 11 days in here
Done             --> Workitem X spend 50 days in here

So far I tried with

  • Analytic Views: There is no BoardColumn that can be added to the output fields
  • OData: Found a way to get the column-value (BoardLocation), based on the current WorkItem state
  • OData: WorkItemSnapshot(to get Historic Data) does not support BoardLocation.

Do you guys know of any way that I can retrieve historic data on features and their BoardColumns?

thanks in advance, Joost

azureazure-devopsodatadevops


Solution 1:[1]

We can use Rest API and power shell to do this.

  1. Get all work items ID by status via Wiql query.

Sample script:

$connectionToken="pat"
$base64AuthInfo= [System.Convert]::ToBase64String([System.Text.Encoding]::ASCII.GetBytes(":$($connectionToken)"))
$WorkItemQueryURL = "https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql?api-version=6.0" 

$body =@"
{
  "query": "Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.WorkItemType] = 'User Story' AND [State] = 'Closed' order by [Microsoft.VSTS.Common.Priority] asc, [System.CreatedDate] desc"
}
"@
$WorkItem = Invoke-RestMethod -Uri $WorkItemQueryURL -ContentType "application/json" -Body $body -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Method POST

Write-host $WorkItem.workItems.id

Result:

enter image description here

  1. We can get the value of field Microsoft.VSTS.Common.StateChangeDate via REST API Get Work Item, It is the time when the state changes, then we can calculate how many days it has been spend in this state since the state changed to xxx.

Sample script:

Write-host $WorkItem.workItems.id

ForEach ($ID in $WorkItem.workItems.id)
{
   $WorkItemInfoURL = "https://dev.azure.com/v-viliu/test/_apis/wit/workitems/$($ID)?api-version=6.0" 

   $WorkItemDetail = (Invoke-RestMethod -Uri $WorkItemInfoURL -Method Get -UseDefaultCredential -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)})
   
   $StateChangeDate = $WorkItemDetail.fields."Microsoft.VSTS.Common.StateChangeDate"

   Write-host "Work item ID: $ID and StateChangeDate is $StateChangeDate"

}

Result:

enter image description here

  1. Calculate how many days it has been spend in this state since the state changed to xxx.

Sample script:

$current = Get-Date

$SpendDate= New-TimeSpan -Start $current -End $StateChangeDate 

Write-Output "The spend date is: $SpendDate"

Note: you need change the current date format, you can refer to this doc for more details.

Solution 2:[2]

Not to resurrect an old thread but, TL;DR: I had a similar problem and posted a solution out on my Github.

Vito's solution above will work for you if each of your columns has its own unique state, however it can only tell you how long the Work Item has spent in the current state based on Today. It can't show you a historical view of Work Items moving across the board.

The solution I provided above isn't as simple as a Power Shell script that just gives you the report, it is a process for capturing the historical changes to the Kanban Column a work item is in that allows you to run a report similar to the one shown below.

SELECT DISTINCT
    wi.WitID,
    DATEDIFF(day, wic_new.AuditCreatedDate, Coalesce(wic_prog.AuditCreatedDate, wic_released.AuditCreatedDate, GETDATE())) as DaysInNew,
    DATEDIFF(day, wic_prog.AuditCreatedDate, Coalesce(wic_released.AuditCreatedDate, GETDATE())) as DaysInProgess,
    DATEDIFF(day, wic_released.AuditCreatedDate, GETDATE()) as DaysInReleased
FROM WorkItems wi
LEFT JOIN WorkItem_ColumnHistory wic_new on (wic_new.WitID = wi.WitID AND wic_new.CurrentColumn = 'New' AND wic_new.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
LEFT JOIN WorkItem_ColumnHistory wic_prog on (wic_prog.WitID = wi.WitID AND wic_prog.CurrentColumn = 'In Progress' AND wic_prog.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
LEFT JOIN WorkItem_ColumnHistory wic_released on (wic_released.WitID = wi.WitID AND wic_released.CurrentColumn = 'Released' AND wic_released.WefColumnID LIKE '%[YOUR TEAMS WEF ID]%')
WHERE
wi.[State] NOT IN('Closed', 'Removed')
ORDER BY WitID ASC

The explanation for this query is in the ReadMe file of the linked GitHub repo so I will not repeat the explanation here. Basically though this query looks at the historical data that has been harvested from Azure DevOps and then displays a result set that shows how long a Work Item has existed in each column of your Kanban board.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Vito Liu
Solution 2 dparsons

Related Questions