Using WIQL(Work Item Query Language) 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 WIQL in PowerShell?
Simple sample:
#Load TFS PowerShell Snap-in
if((Get-PSSnapIn -Name Microsoft.TeamFoundation.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
Add-PSSnapin Microsoft.TeamFoundation.PowerShell
}
#Load Reference Assemblies
$Tfs2015AssembliesPath="C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\CommonExtensions\Microsoft\TeamFoundation\Team Explorer"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.Common.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.WorkItemTracking.Client.dll"
Function GetWorkItems{
param([string]$teamProjectName,[string]$address)
$credentials = New-Object System.Net.NetworkCredential("[user name]", "[password]")
$tfsCollection = New-Object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection((New-Object System.URI($address)))
$wis = $tfsCollection.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore])
$wiqlQT="select [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State], [System.Tags] from WorkItemLinks where (Source.[System.TeamProject] = @project and Source.[System.WorkItemType] = 'Epic' and Source.[System.State] <> '' and Source.[System.AreaPath] = 'Agile2015Starain' and Source.[System.IterationPath] = 'Agile2015Starain') and ([System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward') and (Target.[System.TeamProject] = @project and Target.[System.WorkItemType] <> '') mode (Recursive)"
$variableValues=@{}
$variableValues.Add("project", $teamProjectName)
$query=New-Object Microsoft.TeamFoundation.WorkItemTracking.Client.Query($wis,$wiqlQT,$variableValues)
$witCollection=$query.RunLinkQuery()
#logical to save data to excel or csv
$wits=New-Object "System.Collections.Generic.List[Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItem]"
$title=""
$id=0
Foreach($witItem in $witCollection)
{
$id=$witItem.SourceId
if($id -gt 0)
{
$parentWorkItem = $wits | where {$_['ID'] -eq $id}
$t = $parentWorkItem.Title;
}
$currentWorkItem = $wis.GetWorkItem($witItem.TargetId);
$t = $currentWorkItem.Title;
$wits.Add($currentWorkItem);
}
}
GetWorkItems "Agile2015Starain" "https://[your vsts name].visualstudio.com"