WIQL query to get all the team and the users in a

Question

let's say i have a project name ="Scrum" and that has some users the project and got sprints so **i want dstinct users of the Project that the Sprints in Scrum **. image attached.

Answer 1

According to your description, seems you want to get all teams (include all users in the team) and corresponding sprint for each team.

It's not able to do this through WIQL. WIQL is a work item query language which used to query for bugs, tasks, other types of work items. The return value should be work item.

Instead, you could use Rest API to achieve your requirement.

1.First, Get a list of teams in a project.

GET https://fabrikam-fiber-inc.visualstudio.com/DefaultCollection/_apis/projects/eb6e4656-77fc-42a1-9181-4c6d8e9da5d1/teams?api-version=2.2

2.Get a team's members

GET https://fabrikam-fiber-inc.visualstudio.com/DefaultCollection/_apis/projects/eb6e4656-77fc-42a1-9181-4c6d8e9da5d1/teams/564e8204-a90b-4432-883b-d4363c6125ca/members/?api-version=2.2

3.Get a team's iterations

GET https://{instance}/DefaultCollection/{project}/{team}/_apis/work/TeamSettings/Iterations?[$timeframe=current&]api-version={version}

Combine with the above Rest APIs, you could be able to achieve this get teams name with there Sprints in Scrum project and all users of the team.

Answer 2

You can use also this code for teams:

using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Framework.Common;
using Microsoft.TeamFoundation.ProcessConfiguration.Client;
using Microsoft.TeamFoundation.WorkItemTracking.Client;
using System;
using System.Collections.Generic;
using System.Linq;

namespace QueryLinkedWIQL
{
    class Program
    {

        static List<TeamFoundationTeam> ListTeams(TfsTeamProjectCollection pTpc, Project pProject)
        {
            TfsTeamService _teamService = pTpc.GetService<TfsTeamService>();
            var _teams = _teamService.QueryTeams(pProject.Uri.ToString());

            return (from t in _teams select t).ToList();
        }

        static bool GetTeamsSettings(TfsTeamProjectCollection pTpc, TeamFoundationTeam pTeam)
        {
            try
            {
                var _teamConfig = pTpc.GetService<TeamSettingsConfigurationService>();

                var _configs = _teamConfig.GetTeamConfigurations(new Guid[] { pTeam.Identity.TeamFoundationId  });

                Console.WriteLine("============={0}==================", pTeam.Name);

                Console.WriteLine("Team Members: ");

                foreach (var _member in pTeam.GetMembers(pTpc, MembershipQuery.Expanded))
                    Console.WriteLine("Display Name: " + _member.DisplayName + " || Is active: " + _member.IsActive);

                foreach( var _config in _configs)
                {
                    Console.WriteLine("Team current iteration: " + _config.TeamSettings.CurrentIterationPath);
                    Console.WriteLine("Team selected iterations:");
                    foreach (var _iteration in _config.TeamSettings.IterationPaths)
                        Console.WriteLine(_iteration);
                    Console.WriteLine("Team selected areas:");
                    foreach (var _area in _config.TeamSettings.TeamFieldValues)
                        Console.WriteLine("Area path: " + _area.Value + " || Include children: " + _area.IncludeChildren.ToString());
                }

                Console.WriteLine("===============================");


            }
            catch (Exception)
            {
                return false;
            }

            return true;
        }

        static void Main(string[] args)
        {

            string _teamProject = "YourProjectName";

            try
            {
                TfsTeamProjectCollection _tpc = new TfsTeamProjectCollection(new Uri("http://yourserver/DefaultCollection"));

                WorkItemStore _wistore = _tpc.GetService<WorkItemStore>();

                var _teams = ListTeams(_tpc, _wistore.Projects[_teamProject]);
                foreach (var _team in _teams) GetTeamsSettings(_tpc, _team);

            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
                Console.WriteLine(ex.StackTrace);
            }
        }
    }
}