Procedure to write and execute custom queries for Visual Studio Team Services(VSTS) using Work Item Query Language(wiql)

Question

I wanted to extract data from VSTS using "WIQL" and do some reporting with that data. 1) Could someone please let me know if its is possible to use "WIQL" in PowerShell? If so, please let me know on where i can find the some samples or demos for this?

2) Also, are there any other client tools which support "WIQL" to make custom querying to VSTS. If so, please let me know where i can find some demo or some samples with respect to this?

Answer 1

Yes, it is possible to use WIQL in PowerShell, the easy way is that you could call Work Item Query REST API by using PowerShell.

For example:

$vstsAccount = "XX"
$projectName = "XX"
$user = ""
$token = "personal access token"
Function QueryWorkItem{



# 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.Title] From WorkItems Where [System.Id] = 123'
}"

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

Write-Output ("work item title: '$($result.WorkItems[0].URL)'")

    }

    QueryWorkItem

More information, you can refer to this article.

For secondly issue, you can build an application by yourself, such as console application with C# language.

1. Install Microsoft.TeamFoundationServer.ExtendedClient package
2. Simple C# code

：

 var u = new Uri("https://XXX.visualstudio.com");
                VssCredentials c = new VssCredentials(new Microsoft.VisualStudio.Services.Common.WindowsCredential(new NetworkCredential("user name", "password")));
                var connection = new VssConnection(u, c);
                var workitemClient = connection.GetClient<WorkItemTrackingHttpClient>();

                var result = workitemClient.QueryByWiqlAsync(new Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models.Wiql() { Query= "Select [System.Id],[System.Title] From WorkItems Where [System.Id] = 123" },"Scrum2015").Result;
                Console.WriteLine(result.WorkItems.First().Url);

Update 1:

The TFS/VSTS SDK or extend SDK can be used in PowerShell. For example:

if((Get-PSSnapIn -Name Microsoft.TeamFoundation.PowerShell -ErrorAction SilentlyContinue) -eq $null)
{
    Add-PSSnapin Microsoft.TeamFoundation.PowerShell
}
$Tfs2015AssembliesPath="[vs Installation path]\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.Build.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.Build.Common.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.Git.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.SourceControl.WebApi.dll"
#Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.TestManagement.Client.dll"
Add-Type -Path "$Tfs2015AssembliesPath\Microsoft.TeamFoundation.VersionControl.Client.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])
    $wiqlQuery = "SELECT [System.ID], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State] FROM WorkItems WHERE [System.TeamProject] = 'Scrum2015' AND  [State] = 'New' AND  [System.WorkItemType] in ('Bug','User Story')  ORDER BY [System.ID]";
    $witCollection = $wis.Query($wiqlQuery);

# add logical to export to excel.
    Foreach($witItem in $witCollection)
        {

            Write-Host $witItem.Title
        }
}
GetWorkItems "[project name]" "[tfs/vsts address]"