Powershell script to make use of @CurrentIteration query token in TFS queries

29 Sep, 2015
Xebia Background Header Wave

Since the introduction of Visual Studio 2013 update 5, a new query token (@CurrentIteration) is available to automatically identify the current iteration of your iteration path, based on today’s date. By using this query token in queries that are related to the current iteration/sprint, you will save a lot of time in managing those queries (as mentioned in my previous blogpost).
However, once upgraded to Visual Studio 2013 update 5 or higher, you have to manually update your queries to make use of this new @CurrentIteration query token. This may cost you a lot of time. For that reason, I’ve made a powershell script to automatically implement the @CurrentIteration query token for the shared queries of your team project (see the code below or the attached zip file). After downloading/unzipping, just specify your own values for the different variables and run the powershell script. :)

# Load TFS PowerShell Snap-in
if ((Get-PSSnapIn -Name Microsoft.TeamFoundation.PowerShell -ErrorAction SilentlyContinue) -eq $null)
    Add-PSSnapin Microsoft.TeamFoundation.PowerShell
# Load Referenced Assemblies
# Variables
$tfsCollectionUrl = "[TEAM PROJECT COLLECTION]"
$tfsTeamProject = "[TEAM PROJECT]"
$username = "[USERNAME]"
$password = "[PASSWORD]"
$currentIterationToReplace = "'[TEAM PROJECT][IterationX]'"
# Connect with the TFS collection
$tfsServer = New-Object System.Uri($tfsCollectionUrl)
$netCred = New-Object System.Net.NetworkCredential($username,$password,"")
$basicCred = New-Object Microsoft.TeamFoundation.Client.BasicAuthCredential($netCred)
$tfsCred = New-Object Microsoft.TeamFoundation.Client.TfsClientCredentials($basicCred)
$tfsCollection = New-Object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection($tfsServer,$tfsCred)
if ($tfsCollection.HasAuthenticated)
 #Get Work Item Store object
 $ws = $teamProjectCollection.GetService([type]"Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore")
 #Get work item store of Team Project
 $proj = $ws.Projects[$tfsTeamProject]
 # Update all queries of team project
 $queryHierarchy = $proj.QueryHierarchy;
 $queryRootFolder = $queryHierarchy -as [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryFolder]
 $AllQueryDefsOfProject = GetAllQueriesOfQueryFolder $queryRootFolder
 foreach ($item in $AllQueryDefsOfProject)
     $queryDef = $item -as [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryDefinition]
     $newQueryText = $queryDef.QueryText.Replace($currentIterationToReplace, "@CurrentIteration")
     $queryDef.QueryText = $newQueryText
function GetAllQueriesOfQueryFolder([Microsoft.TeamFoundation.WorkItemTracking.Client.QueryFolder] $queryFolder)
    $AllQueryDefinitions = @()
    foreach ($item in $queryFolder)
        $queryItem = $item -as [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryItem]
        if($queryItem -is [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryFolder])
            $subQueryFolder = $queryItem -as [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryFolder]
            $AllQueryDefinitions += GetAllQueriesOfQueryFolder $subQueryFolder;
        elseif($queryItem -is [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryDefinition])
            $queryDef = $queryItem -as [Microsoft.TeamFoundation.WorkItemTracking.Client.QueryDefinition]
            $AllQueryDefinitions += $queryDef
     return $AllQueryDefinitions;


Cornell Knulst
Cornell works for Xpirit, Hilversum, The Netherlands, as a trainer/architect. He is specialized in the domain of Application Lifecycle Management and Continuous Delivery, with a special focus on Microsoft-based technologies.

Get in touch with us to learn more about the subject and related solutions

Explore related posts