TFS queries in Powershell

Sometimes you need to get the workitems from TFS directly into your favorite tool, Powershell.

When the workitems are available in PS, you have a lot of possibilities, like making custom reports, release notes papers, or just plain lists.

But instead of creating your own query in PS, why not use the queries already in TFS.

So to start, we need to define some variables like connection string, TFSproject and the name of the query.

$TFSServerUrl = "http://[TFS server]:8080/tfs/defaultcollection"
$TFSProject = "TFSTeam"
$StoredQuery = "Shared Queries/Bugs fixed in latest release"

Then we need to get the ID of the query
$QueryID = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/$($TFSProject)/_apis/wit/queries/$($StoredQuery)?api-version=2.2").id

With the ID in hand we run the query and get the ID of the workitems
$QueryResult = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/$($TFSProject)/_apis/wit/wiql/$($QueryID)?api-Version=1.0").workitems

And finaly we send the workitem ID’s to get the complete Workitem information.
$WorkItems = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/_apis/wit/WorkItems?ids=$($QueryResult.id -join ",")&api-version=1.0").value | Select-Object -ExpandProperty Fields

Note: the API only supports retrieving 200 workitems per request.
The ExpandProperty switch will expand the Fields column, so it is possible to access the properties directly.

Now you can access the workitem fields directly like
foreach($item in $workItems)
{
$item.'System.Title'
}

The complete Script:

$TFSServerUrl = "http://[TFS server]:8080/tfs/defaultcollection"
$TFSProject = "TFSTeam"
$StoredQuery = "Shared Queries/Bugs fixed in latest release"

#Get the ID of the query
$QueryID = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/$($TFSProject)/_apis/wit/queries/$($StoredQuery)?api-version=2.2").id

#Get Workitem IDs from the query
$QueryResult = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/$($TFSProject)/_apis/wit/wiql/$($QueryID)?api-Version=1.0").workitems

# You can only get a maximum of 200 IDs per request, so either split it in batches of 200 or iterate through each.
$WorkItems = (Invoke-RestMethod -Method Get -UseDefaultCredentials -uri "$($TfsServerUrl)/_apis/wit/WorkItems?ids=$($QueryResult.id -join ",")&api-version=1.0").value | Select-Object -ExpandProperty Fields

foreach($item in $workItems)
{
$item.'System.Title'
}

Author: KimC

TFS admin and deployment fellow View all posts by KimC