CODEWITHSUNDEEP
azure-devopsazure-boards
awk1912
awk1912

Reputation: 69

In Azure DevOps, how to query user stories that were not completed and are carried over to current sprint iteration?

I'm trying to list user stories that were not completed and are carried over to current sprint using a better method than what I had currently made. The output should list user stories that were created not in the current sprint iteration and also it must be at least activated once in any previous iterations to be listed. My output is correct but the problem is that I have to change the date manually since I use the create date field and just set it manually to be greater than the start date of my current sprint iteration. Is there another way that I can detect if the user story was ever in a previous sprint iteration so I can list those if they are carried over to the current sprint? I was thinking something like comparing the create date of the stories so that the new stories won't be listed but I haven't found the right parameters.

My inefficient query

Upvotes: 2

Views: 9804

Answers (2)

Andy Li-MSFT
Andy Li-MSFT

Reputation: 30432

We cannot achieve that by the built-in work item query, however we can achieve that by calling the REST APIs in a script (Wiql - Query By Wiql and Revisions - Get).

Please try the following PowerShell script which works for me: (you can also export the list to a CSV file, then open with Microsoft Excel)

It query the not completed User Story from current iteration of the specific team, then retrieve the work item revisions for each of them to detect the System.IterationPath field to get all the values. If the field has more than one unique values, then that means the work item is carried from previous iterations to current sprint iteration:

Param(
   [string]$baseurl = "https://dev.azure.com/{organization}", 
   [string]$projectName = "project name",
   [string]$user = "username",
   [string]$token = "password/PAT"  
)

# Base64-encodes the Personal Access Token (PAT) appropriately
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$token)))


# Query the work items with wiql
$uri = "$baseurl/$($projectName)/_apis/wit/wiql?api-version=5.1"


function CreateJsonBody
{
    $value = @"
{
  "query": "Select [System.Id], [System.Title], [System.State],[System.Tags] From WorkItems Where [System.WorkItemType] = 'User Story' AND [System.State] <> 'Closed' AND [System.State] <> 'Removed' AND [System.IterationPath] = @currentIteration('[Agile-0219]\Agile-0219 Team') order by [System.CreatedDate] desc"
}

"@
 return $value
}
$json = CreateJsonBody

#Get the urls for WIs
$queryresult = Invoke-RestMethod -Uri $uri -Method POST -Body $json -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}

$wiurls = $queryresult.workItems.url


#Filter the work items which carried from other iterations to current iteration
$wis = @()
cls
foreach($wiurl in $wiurls){

#Set the work item revision URL
$revurl = "$wiurl/revisions"

$wi = (Invoke-RestMethod -Uri $revurl -Method GET -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)})

#Detect the Unique iterations which the work item ever been involved  
$iterationcount = ($wi.value.fields.'System.IterationPath'|select -Unique).count
#write-host $iterationcount

if ($iterationcount -gt 1) # Filter the work items which moved from other iterations
    { 
      # Select the latest revision 
       $wilatest = ($wi.value | Select -last 1) 

        $customObject = new-object PSObject -property @{
          "WitID" = $wilatest.id
          "Title" = $wilatest.fields.'System.Title'
          "AssignedTo" = $wilatest.fields.'System.AssignedTo'.displayName
          "ChangedDate" = $wilatest.fields.'System.ChangedDate'
          "ChangedBy" = $wilatest.fields.'System.ChangedBy'.displayName
          "WorkItemType" = $wilatest.fields.'System.WorkItemType'
          "State" = $wilatest.fields.'System.State'
          "URL" = $wilatest.url
        } 

    $wis += $customObject   
    }

}
    $wis | Select-Object `
                WitID,
                Title, 
                AssignedTo,
                ChangedDate, 
                ChangedBy,
                WorkItemType,
                State,
                URL #|export-csv -Path D:\sample.csv -NoTypeInformation

enter image description here

UPDATE

If you want to filter by created date in the query, then you just need to add the the filter condition like: AND [System.CreatedDate] < '2019-10-10'

So, the query should be :

"Select [System.Id], [System.Title], [System.State],[System.Tags] From WorkItems Where [System.WorkItemType] = 'User Story' AND [System.State] <> 'Closed' AND [System.State] <> 'Removed' AND [System.IterationPath] = @currentIteration AND [System.CreatedDate] < '2019-10-10' order by [System.CreatedDate] desc"

Upvotes: 1

Shamrai Aleksander
Shamrai Aleksander

Reputation: 16163

I think, you use the best way. You can add the date macros (like @StartOfWeek-1) that helps to analyse your user stories without changing dates in your query (Start of Day, Week, Month, or Year date-based queries). Additionally, you can use the Power BI reporting where you can analyse each history revision of work items and find previous iterations. Connect to Analytics with Power BI Data Connector

Upvotes: 0

Related Questions