Skip to content

Latest commit

 

History

History
515 lines (327 loc) · 31.7 KB

using-queries.md

File metadata and controls

515 lines (327 loc) · 31.7 KB
title titleSuffix description ms.custom ms.service ms.assetid ms.author author ms.topic monikerRange ms.date
Define a work item query in Azure Boards
Azure Boards
Learn how to use queries and perform tasks like define a flat-list, clause, or hyperlink, and use a tree, direct link, hyperlink or logical expression. Also learn how to query across or within projects and view results in a dashboard, and best practices.
boards-queries, linked-from-support, cross-project, engagement-fy23
azure-devops-boards
364000d3-200a-495a-bfb9-83915240af67
chcomley
chcomley
tutorial
<= azure-devops
08/10/2023

Define a work item query in Azure Boards

[!INCLUDE version-lt-eq-azure-devops] [!INCLUDE version-vs-gt-eq-2019.md]

Work item queries generate lists of work items based on the filter criteria provided by you. You can save and share these managed queries with others.

::: moniker range=">= azure-devops-2020" You can create queries from the web portal or from a supported client, such as Visual Studio Team Explorer. To support bulk updates or additions, import or export queries using Excel or .csv files.
::: moniker-end

Check out our recommended best practices, later in this article.

[!INCLUDE temp]

Choose a query filter

From the Query Editor, you can select the filter to jump to an article with sample queries. Along with the query filters, you can interactively apply filters to query results.

::: moniker range=">= azure-devops-2019"

Note

Managed queries don't support proximity searches, however semantic searches do. In addition, semantic searches supports both * and ? as wildcard characters and you can use more than one wildcard character to match more than one character. For more information, see Functional work item search. ::: moniker-end


:::row::: :::column span="2"::: Filter features :::column-end::: :::column span="2"::: Macros :::column-end::: :::row-end:::

:::row::: :::column span="1"::: ::: moniker range=">= azure-devops-2019" - Compare fields - Key words
- Linked work items
- Logical groupings - Query macros
- Tags
- Was Ever
- Was Ever (Board Column)
- Wildcard
::: moniker-end

:::column-end::: :::column span="1"::: ::: moniker range=">= azure-devops-2019" - Blank or empty fields - Boolean searches - Identity searches - History and Discussion - Board fields - In and Not In Group searches - Search across projects ::: moniker-end

:::column-end::: :::column span="2"::: ::: moniker range=">= azure-devops-2019" - [Any] - @Me - @Today - @CurrentIteration, @CurrentIteration +/-n
- @Follows - @MyRecentActivity, @RecentMentions, @RecentProjectActivity - @StartOfDay, @StartOfMonth, @StartOfWeek, @StartOfYear
- @TeamAreas ::: moniker-end

:::column-end::: :::row-end:::

You can also interactively filter a query using the :::image type="icon" source="../../media/icons/filter-icon.png" border="false"::: Filter function.

Open Queries

[!INCLUDE temp]

Define a flat-list query

Start a new query from the Queries tab in the web portal or the Work Items tab in Team Explorer.

::: moniker range=">= azure-devops-2019"

Select New query.

:::image type="content" source="media/view-run-queries/new-query-new-exp.png" alt-text="Screenshot to Add new query, new experience.":::

The Query Editor displays with the following default settings: Flat list of work items, Work Item Type=[Any], and State=[Any].

:::image type="content" source="media/using-queries/new-query-web-portal.png" alt-text="Screenshot of Query Editor with flat list of work items selected.":::

Modify the Values and add or remove clauses, or change the Type of query to Work items and direct links or to a Tree of work items. ::: moniker-end

Important

We strongly recommend that everyone use the default view instead of this legacy view. It is designed for you to quickly access a list of work items based on your assignment, following, mentioned, or recent updates. The legacy view is no longer being enhanced and we expect to remove it in a future release of Visual Studio.

Select New Query from the Work Items page.

:::image type="content" source="media/using-queries/new-query-visual-studio-2019.png" alt-text="Screenshot of Work Items, choose New Query.":::

The Query Editor displays with the following default settings: Flat list (Default), Team Project=current project, Work Item Type=[Any], and State=[Any].

:::image type="content" source="media/using-queries/visual-studio-new-query-editor.png" alt-text="Screenshot of Visual Studio Query Editor, flat-list query.":::

You can modify the Values and add or remove clauses. Or, change the Type of query to Work items and direct links or to a Tree of work items.


Query across or within projects

New queries scope to the current project by default. But, you can create queries to find work items defined within the organization or project collection. All queries that you save, however, get saved under a specific project.

To list work items defined in two or more projects, checkmark Query across projects. For example, the following query finds all features created in all projects within the last 30 days.

:::image type="content" source="media/using-queries/portal-query-across-projects.png" alt-text="Screenshot of Query Editor with Query across projects checked.":::

With the Query across projects checked, you can add the Team Project field to filter to a select number of projects.

:::image type="content" source="media/using-queries/portal-query-across-two-projects.png" alt-text="Screenshot of Query Editor with Team Project and other fields added to filter.":::

Note

Separate multiple project names with the list separator that corresponds to the regional settings defined for your client computer, for example, a comma ,.

  • The Team Project field is available only after you check Query across projects.
  • When Query across projects is checked, all fields from all work item types defined in all projects in the collection appear in the Field drop-down menu.
  • When Query across projects is unchecked, only those fields from those work item types, as defined in the current project, appear in the Field drop-down menu.

Important

We strongly recommend that everyone use the default view instead of this legacy view. It is designed for you to quickly access a list of work items based on your assignment, following, mentioned, or recent updates. The legacy view is no longer being enhanced and we expect to remove it in a future release of Visual Studio.

To list work items defined in two or more projects, change the clause for the Team Project using the In operator, and enter the names of the projects to search in. For example, the following query finds work items defined in the Fabrikam Fiber and Design Agile projects.

:::image type="content" source="media/using-queries/visual-studio-query-multiple-projects.png" alt-text="Screenshot of Visual Studio Query Editor, flat-list query, specify two projects clause.":::

To query across all projects, delete the clause with the Team Project field.


Define a clause

To create a query, define one or more clauses. Each clause defines a filter criteria for a single field.

Sample query clause

And/Or Field Operator Value
And Assigned To = @Me

For a list of available operators based on the field data type, see Query index quick reference.

All clauses get added as an And statement. Select Or to change the grouping. Group clauses to ensure that the clause statements are run in the sequence required.

Select Add new clause to add another clause at the end of the query, and then select the Field, Operator, and Value for that clause.

:::image type="content" source="media/using-queries/define-clause.png" alt-text="Screenshot of Query Editor showing how to add a clause.":::

For example, search for all work items assigned to you by specifying the Assigned To field, the equals (=) operator, and the @Me macro, which represents your user identity.

Important

We strongly recommend that everyone use the default view instead of this legacy view. It is designed for you to quickly access a list of work items based on your assignment, following, mentioned, or recent updates. The legacy view is no longer being enhanced and we expect to remove it in a future release of Visual Studio.

Select Click here to add a clause to add another clause at the end of the query, and then select the Field, Operator, and Value for that clause.

:::image type="content" source="media/using-queries/visual-studio-add-new-clause.png" alt-text="Screenshot of Visual Studio Query Editor, add new clause.":::

To insert a clause within the existing set of query clauses, place your cursor on the following clause where you want to insert the clause, and then choose Insert Clause. Then, select the Field, Operator, and Value for that clause.

:::image type="content" source="media/using-queries/visual-studio-add-new-clause.png" alt-text="Screenshot of Visual Studio Query Editor, insert clause.":::

Or, open the context menu for the clause and choose Insert Clauses.

:::image type="content" source="media/using-queries/visual-studio-insert-delete-clauses.png" alt-text="Screenshot of Visual Studio Query Editor, context-menu for insert clauses and delete clause.":::

To delete a clause, choose the clause you want to delete, and choose :::image type="icon" source="../media/icons/icon-delete-clause-visual-studio.png" border="false"::: Delete Clause.

When finished, choose :::image type="icon" source="../media/icons/run_query.png" border="false"::: Run or :::image type="icon" source="../media/icons/icon-save-visual-studio.png" border="false"::: Save Query.


[!INCLUDE temp]

Checklist for defining a query clause

  1. In the first empty row, under the Field column heading, choose the down arrow to display the list of available fields, and choose an item in the list. For more information, see Query Fields and Values.

  2. In the same row, under the Operator column heading, select the down arrow to display the list of available operators, and then choose an item in the list. For more information, see Operators.

  3. In the same row, under the Value column heading, either enter a value, or select the down arrow, and then choose an item in the list. For more information about how to use a macro or variable to specify the current project, user, date, or other selection, see Variables.

  4. To add a clause, choose Add new clause.

    You can add a clause to the end of the query, or perform the following tasks with the corresponding icons:

    • :::image type="icon" source="../media/icons/add-green-icon.png" border="false"::: Insert new filter line
    • :::image type="icon" source="../media/icons/delete_icon.png" border="false"::: Remove this filter line
    • :::image type="icon" source="media/query-fields-operators-values-variables/IC588313.png" border="false"::: Group selected clauses
    • :::image type="icon" source="media/query-fields-operators-values-variables/IC588314.png" border="false"::: Ungroup clauses

Use a work item tree to view hierarchies

Use the :::image type="icon" source="media/11.png" border="false"::: Tree of Work Items query to view a multi-tiered, nested list of work items. For example, you can view all backlog items and their linked tasks. To focus on different parts of the tree, choose :::image type="icon" source="media/13.png" border="false"::: Expand all or :::image type="icon" source="media/14.png" border="false"::: Collapse all.

Note

You can't construct a query that shows a hierarchical view of Test Plans, Test Suites, and Test Cases. These items aren't linked together using parent-child link types. However, you can create a direct links query that lists test-related work items. Also, you can, view the hierarchy through the Test Plans page.

::: moniker range=">= azure-devops-2019"

:::image type="content" source="media/view-run-queries/tree-query-new-exp.png" alt-text="Screenshot of Query Results List showing a Tree Query.":::

::: moniker-end

Define the filter criteria for both parent and child work items. To find linked children, select Match top-level work items first. To find linked parents, select Match linked work items first.

:::image type="content" source="media/view-run-queries/tree-query-editor-s136.png" alt-text="Screenshot of Query Editor view of Tree of work items.":::

Important

We strongly recommend that everyone use the default view instead of this legacy view. It is designed for you to quickly access a list of work items based on your assignment, following, mentioned, or recent updates. The legacy view is no longer being enhanced and we expect to remove it in a future release of Visual Studio.

Define the filter criteria for both parent and child work items. To find linked children, select Match top-level work items first. To find linked parents, select Match linked work items first.

:::image type="content" source="media/using-queries/tree-backlog-te.png" alt-text="Screenshot, Query Editor, Tree Query, Team Explorer. ":::


Use direct links to view dependencies

Use the :::image type="icon" source="media/16.png" border="false"::: Work items and Direct links query to track work items that depend on other tracked work, such as tasks, bugs, issues, or features. For example, you can view backlog items that depend on other items being implemented or a bug being fixed.

Use the direct links query to track dependencies across teams. The query also helps you manage commitments your team makes. Choose the filter criteria for the top and linked work items. And, select the types of links to filter the dependencies.

:::image type="content" source="media/17.png" alt-text="Screenshot of Direct Links Query Results.":::

:::image type="content" source="media/view-run-queries/direct-query-editor-s136.png" alt-text="Screenshot of Query Editor, Direct Links Query.":::

Filter your first-tier list of work items by choosing one of these options:

  • Only return items that have matching links: First-tier work items return, but only if they have links to work items specified by the linked work items filter criteria.
  • Return all top level items: All first-tier work items return despite the linked work items filter criteria. Second-tier work items that are linked to the first tier return if they match the linked work items filter criteria.
  • Only return items that do not have matching links: First-tier work items are returned, but only if they don't have links to work items specified by the linked work items filter criteria.

Important

We strongly recommend that everyone use the default view instead of this legacy view. It is designed for you to quickly access a list of work items based on your assignment, following, mentioned, or recent updates. The legacy view is no longer being enhanced and we expect to remove it in a future release of Visual Studio.

:::image type="content" source="media/using-queries/direct-links-te.png" alt-text="Screenshot, Query Editor, Direct Links Query, Team Explorer.":::

Filter your first-tier list of work items by choosing one of these options:

  • Return all top level work items: All first-tier work items return despite the linked work items filter criteria. Second-tier work items that are linked to the first tier return if they match the linked work items filter criteria.

  • Only return items that have the specified links: First-tier work items return, but only if they have links to work items specified by the linked work items filter criteria.

  • Only return items that do not have the specified links: First-tier work items return, but only if they don't have links to work items specified by the linked work items filter criteria.


For more information about each link type, see Link type reference.

Use and/or logical expression

Specify And or Or to create logical expressions of your query clauses. Use And to find work items that meet the criteria in both the current clause and the previous clause. Use Or to find work items that meet the criterion in either the current clause or the previous clause.

Add one new clause for each work item field to refine your search criteria. Add clauses to return only the set of work items you want. If you don't receive the results you expect from your query, refine it. You can add, remove, group, or ungroup query clauses to improve your query results.

Group query clauses to operate as a single unit separate from the rest of the query. Grouping clauses is similar to putting parentheses around an expression in a mathematical equation or logic statement. When you group clauses, the And or Or for the first clause in the group applies to the whole group.

Group clauses

Grouped clauses operate as a single unit separate from the rest of the query. Grouping clauses is similar to putting parentheses around a mathematical equation or logic expression. The And or Or operator for the first clause in the group applies to the whole group.

As the following examples show, the grouped clauses are translated to the corresponding logical expression.

[!div class="mx-tdCol2BreakAll"]

Query Grouped clauses Logical expression
1 Screenshot showing a group clause query. Filters are set up for the Work item type field and either the State field or the Assigned to field. Screenshot of a logical expression. An AND operator groups the Work item type, State, and Assigned to fields. An OR operator groups the State and Assigned to fields.
2 Screenshot showing a group clause query, with filters for both the Work item type field and one of either the State field or the Assigned to field. Screenshot of a logical expression. An AND operator groups the Work item type with the State or Assigned to fields, which are grouped by an OR operator.
3 Screenshot showing a group clause query. Filters are set up for either the Work item type field or both the State field and the Assigned to field. Screenshot of a logical expression. An OR operator links the Work item type to both the State and the Assigned to fields, which are linked by an AND operator.

These queries return work items that are type Bug and meet the following logical expressions:

  • Query 1: AND State=Active OR Assigned to @Me
  • Query 2: AND (State=Active OR Assigned to @Me)
  • Query 3: OR (State=Active AND Assigned to @Me)

To group one or more clauses, select them and then select the :::image type="icon" source="../media/icons/group-clauses-icon.png" border="false"::: group clauses icon.

[!div class="mx-imgBorder"]
Screenshot of Web portal, Query Editor, Group Selected Query Clauses.

You can also group several grouped clauses. Check the boxes of each clause. Then, choose the :::image type="icon" source="../media/icons/group-clauses-icon.png" border="false"::: group clauses icon.

[!div class="mx-imgBorder"]
Screenshot of Web portal, Query Editor, Group multiple query clauses.

If your query results don't return expected results, do the following steps:

  • Make sure that each clause is defined as you intended.
  • Verify And/Or assignments to each clause. If your results contain more work items than expected, often an Or clause is present instead of an And clause.
  • Determine if you need to group or change the grouping of the query clauses and the And/Or assignments of each grouped clause.
  • Add more query clauses to refine your query filter criteria.
  • Review the options available to specify fields, operators, and values.
  • See best practices, later in this article.

Ungroup a clause

To ungroup a clause, select the :::image type="icon" source="../media/icons/ungroup-clause.png" border="false"::: ungroup clauses icon for the grouped clause.


View query results in a dashboard

The following two widgets display query results. You can open work items directly from these widgets.

  • Work assigned to me: Lists all proposed or active work items assigned to the signed-in user. Lists the ID, State, and Title fields.
  • Query results widget: Displays the results of a flat, tree, or direct-links query. You can configure the fields displayed through the widget, resize the column fields, and expand and collapse tree and direct-links query.

For more information, see Add widgets to a dashboard.

::: moniker range=" >= azure-devops-2022"

View query results widget with Parent titles

You can view the results of a query with the Parent title displayed. To do so, perform these steps:

  1. Create the query that filters the work items of interest. The query can be a flat-list, tree, or direct-links query.
  2. Add the Parent field as a column.
  3. Save the query as a Shared query or Team favorite.
  4. Add the Query results widget to your dashboard and configure the query. Make sure the Parent field is set to display.

The following image illustrates a query results widget that displays the Parent field.

:::image type="content" source="media/using-queries/query-results-widget-with-parent-column-field.png" alt-text="Screenshot of query results widget showing a Parent column field.":::

::: moniker-end

Define a query as a hyperlink

A query hyperlink uses the Work Item Query Language (WIQL), which resembles Transact-SQL. For more information, see Syntax for the Work Item Query Language (WIQL).

Note

Most browsers enforce a limit of between 2000 and 2083 characters for a URL string.
::: moniker range="azure-devops"

Query hyperlink syntax

Encode the WIQL portion of the URL syntax. You can use any URL encoder tool to encode your URL.

https://dev.azure.com/OrganizationName/ProjectName/_workitems?_a=query&wiql={Encoded WorkItemQueryLanguage}

For example, the following hyperlink lists the ID and title of all active bugs defined under the FabrikamFiber/Web area path for the Fabrikam organization.

https://dev.azure.com/fabrikam/FabrikamFiber/_workitems?_a=query&wiql=SELECT%20%5BSystem.ID%5D%2C%20%5BSystem.Title%5D%20FROM%20WorkItems%20WHERE%20%5BSystem.TeamProject%5D%3D'FabrikamFiber'%20AND%20%5BSystem.WorkItemType%5D%3D'Bug'%20AND%20%5BSystem.State%5D%3D'Active'%20AND%20%5BSystem.AreaPath%5D%3D'FabrikamFiber%5CWeb'

The decoded WIQL conforms to:

SELECT [System.ID], [System.Title]
   FROM WorkItems 
   WHERE [System.TeamProject]='FabrikamFiber' 
   AND [System.WorkItemType]='Bug'
   AND [System.State]='Active'
   AND [System.AreaPath]='FabrikamFiber\Web'

Note

The WIQL length must not exceed 32K characters for Azure Boards queries.

::: moniker-end

::: moniker range="< azure-devops"

Query hyperlink syntax

https://{ServerName}/{CollectionName}/{ProjectName}/_workitems?_a=query&wiql={Encoded WorkItemQueryLanguage}

For example, the following hyperlink lists the ID, title, and state of all bugs under the FabrikamFiber/Web area path.

http://fabrikam:8080/tfs/DefaultCollection/FabrikamFiber/_workitems?_a=query&wiql=SELECT%20%5BSystem.ID%5D%2C%20%5BSystem.Title%5D%2C%20%5BSystem.State%5D%20FROM%20WorkItems%20WHERE%20%5BSystem.TeamProject%5D%3D'FabrikamFiber'%20AND%20%5BSystem.WorkItemType%5D%3D'Bug'%20AND%20%5BSystem.AreaPath%5D%3D'FabrikamFiber%5CWeb'%20%20

For example, see the following nonencoded entry.

http://fabrikam:8080/tfs/DefaultCollection/FabrikamFiber/_workitems?_a=query&wiql=
SELECT [System.ID], [System.Title], [System.State] 
   FROM WorkItems 
   WHERE [System.TeamProject]='FabrikamFiber' 
   AND [System.WorkItemType]='Bug' 
   AND [System.AreaPath]='FabrikamFiber\Web'   

::: moniker-end

Best practices

The following best practices apply to the following queries you can create:

::: moniker range="azure-devops"

::: moniker range="< azure-devops"

Create focused, selective queries

Define a highly selective query by applying all filters that are necessary for your query. The more selective the query is, the smaller the set of results. The smaller the result set is, the more targeted and selective your query is.

Use tags to categorize work items

Use work item tags to categorize your work items instead of a custom field. Queries that filter on tags usually perform faster over those queries that filter on string matches.

Unlike custom field matches or partial matches, a query with a Tags Contains operation doesn't require a complete scan of all work item tables.

Use Contains words for string matches

To filter on a string match, use the Contains Words instead of the Contains operator. The Contains Words operator runs a full-text search on the specified field, which tends to complete more quickly.

The Contains operator runs a table scan, which is a slower operation than the Contains Words operator. It also consumes more CPU cycles. These CPU cycles can cause you to encounter rate limitations. For more information, see Rate and usage limits.

Specify small groups with the In Group operator

The In Group operator filters work items based on matches within a group of values. The group of values correspond to the values contained within a team, security group, or work tracking category. For example, you can create a query to find all work items that are assigned to any member of a team. Or, find all work items that belong to the requirements category (Microsoft.RequirementCategory).

When you filter on a group that contains a large number of members, your result set tends to be larger and nonselective. Also, if a group corresponds to a large Microsoft Entra group, the query generates a fairly large cost to resolve that group from Microsoft Entra ID.

Avoid use of negated operators

Negated operators—such as <>, Not In, Not Under, Not In Group—tend to generate nonselective queries and large result sets.

Only use negated operators when necessary. Always try to find alternatives first. For example, if Field1 has values A, B, C, D; specify the Field1 In A, B, C clause, instead of the negated Field1 <> D clause.

Avoid string comparisons

Queries that contain string comparisons generate table scans that are inherently inefficient. Instead, we recommend you use tags or a specific custom field as alternatives, particularly when a query performs poorly.

Limit Or operators

Limit the number of Or operators defined in your query. Queries run better when fewer Or operators are used. Too many Or operators can make your query nonselective. If your query runs slowly, reorder the Or operator clause towards the top of the query clauses.

Save your query

Due to internal optimizations, saved queries tend to perform better over unsaved queries. Always save your query when you plan to reuse it. Even for WIQL queries run through a REST API, save the WIQL through the web portal to make your REST API calls, so they're less prone to future performance regressions.

Run your query

Sometimes you need to run your query a few times to reach the right optimization plan. Make sure to save your query and run it up to 10 times over a 30-minute period. This way, the system can examine and seek out the optimization plan that's most appropriate for your query.

Related articles