VSTS Rest API queries for linked work items in Powershell

Question

Using REST APIs of VSTS in PowerShell, I wanted to create a report on my VSTS Team Project which consists of the details of the Work Item Linking relationships of all workitems under particular "Iteration Path" and "Area Path".

Ex: Epics→Features→UserStories. Since there are parent/child relationships between Epics & Features and also between Features & UserStories.

So the input would be Iteration Path and Area Path, and the corresponding output would be a report (.csv or .xls) which has all the details of these workitems and their relationships.

Could someone let me know on how to achieve this using REST APIs in PowerShell?

I wrote the code for getting the hierarchical workitems in a Team Project but need to modify to filter the the list based on a given Iteration and Area paths. Also when executing the below code, the string comparison in the query(due to presence of '') is returning an error in the power-shell.

Error: Invoke-RestMethod : {"count":1,"value":{"Message":"After parsing a value an unexpected character was encountered: G. Path 'query', line 1, position 163.\r\n"}}

$vstsAccount = "xxxx"
$projectName = "xxxx"
$user = "xxxx"
$token = "xxxx"

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

# Construct the REST URL to obtain Build ID
$uri = "https://$($vstsAccount).visualstudio.com/$($projectName)/_apis/wit/wiql?api-version=1.0"

$body = "{'query': 'Select [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State] From WorkItemLinks WHERE 
((Source.[System.TeamProject] = '$projectName' and Source.[System.State] <> 'Removed') and (Source.[System.WorkItemType] = 'Epic') and
([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') and (Target.[System.WorkItemType] = 'UserStory') mode(Recursive)'}"

# Invoke the REST call and capture the results (notice this uses the POST method)
$result = Invoke-RestMethod -Uri $uri -Method Post -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)} -Body $body

It doesn't look like you've done the basic investigation or even given this a try yourself? Someone would be unlikely to post a full solution for you. How about you give it a go yourself and post back for assistance filling in the blanks? — Martyn C

starian chen-MSFT starian chen-MSFT · Accepted Answer · 2016-11-09T07:46:50

Modify part of your code like this:

$query = "Select
    [System.Id],
    [System.WorkItemType],
    [System.Title],
    [System.AssignedTo],
    [System.State]
From WorkItemLinks
WHERE (
        Source.[System.TeamProject] = '$projectName'
        AND Source.[System.State] <> 'Removed'
    ) AND (
        Source.[System.WorkItemType] = 'Epic')
        AND ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
    ) AND (Target.[System.WorkItemType] = 'UserStory') mode (Recursive)
"

$body = @{query=$query} | ConvertTo-Json
$result = Invoke-RestMethod -Uri $uri -Method Post -ContentType "application/json" -Headers @{Authorization=("Basic $base64AuthInfo")} -Body $body

VSTS Rest API queries for linked work items in Powershell

1 Answers