Getting Kanban throughput data from Microsoft VSO

Question

I'm creating a throughput calculator for my team based on our data in the MS VSO tool. In order to accurately indicate the date we start work on a PBI I need to extract the date a PBI is committed to.

Is it possible to query the MS VSO product in order to extract the dates that a PBI changed state to "Committed"?

I can get "Created Date" via the query editor (column options only I'm afraid), but VSO doesn't seem to allow access to the state change dates. And start date isn't correct, as the PBI may have been kicking around in the backlog for some time before it's Dev ready.

Just to make sure that you're not building something that already exists, have you seen this: https://marketplace.visualstudio.com/items?itemName=ms.vss-analytics? — Wouter de Kort, Nov 16 '17 at 11:33
Thanks... I have access to these charts, but they're not up to standard, I'm afraid. — Andrew Kidd, Nov 17 '17 at 15:31

score 0 · Accepted Answer · answered Nov 16 '17 at 07:48

Cannot achieve that with the query by default.

But you could query the work item history with the TFS API and check the timestamps on when the state transitions occurred.

Refer to this article to do that : TFS SDK: Work Item History Visualizer using TFS API

You can also use the REST API to extract the dates that a Work item changed state.

For example, you can use below PowerShell sample to get the date changed to "Committed":

Param(
   [string]$collectionurl = "https://instance.visualstudio.com",  #If on-premise TFS change it to http://server:8080/tfs/DefaultCollection
   [string]$workitemId = "62",
   [string]$user = "username",
   [string]$token = "token"
)

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

$baseUrl = "$collectionurl/_apis/wit/workitems/$($workitemId)/revisions?api-version=1.0"            
$response = (Invoke-RestMethod -Uri $baseUrl -Method Get -UseDefaultCredential -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)})
$revisions = $response.value.fields | where({$_.'System.State' -eq 'Committed'}) # Change the sate which you want to get it's changed date here 

$witrevisions = @()

foreach($revision in $revisions){

    $customObject = new-object PSObject -property @{
          "WorkItemType" = $revision.'System.WorkItemType'        
          "CommittedDate" = $revision.'System.ChangedDate'
          "ChangedBy" = $revision.'System.ChangedBy'
        } 

    $witrevisions += $customObject      
}

$witrevisions | Select-Object -Last 1 `
                WorkItemType,
                CommittedDate, 
                ChangedBy

Thanks ... You've confirmed I can't access the data using the in-built query editor, which is what I suspected. I don't want to have to code a solution, as I believe that access to this data should be available to users easily through the UI. — Andrew Kidd, Nov 17 '17 at 15:32

Getting Kanban throughput data from Microsoft VSO

1 Answers1