Query in Azure DevOps for work items

Having processes, it ends up in request of custom query in Azure DevOps for work items. I have discovered that it is possible to write specific queries using a kind of SQL. Its name is WIQL.

A query defined using the Work Item Query Language (WIQL) consists of a SELECT statement that lists the fields to be returned as columns in the result set. So, you can further qualify the result set by using a logical expression. You can specify a sort order. Use an ASOF clause to state that a query was evaluated previously.

Install the editor

Now, from your organization, you can search in the Marketplace for the Wiql Editor or click on the link that brings you to the following page.

WIQL Editor on Microsoft Marketplace - Query in Azure DevOps for work items
WIQL Editor on Microsoft Marketplace

Then, when the WIQL Editor is installed, you can find it under Boards in your project pages.

WIQL Editor in the Boards
WIQL Editor in the Boards

Work Item Query Language

So, the work item query language has five parts shown in the following syntax snippet and described in the following table.

SELECT [State], [Title] 
FROM WorkItems
WHERE [Work Item Type] = 'User Story'
ORDER BY [State] Asc, [Changed Date] Desc
ASOF '6/15/2010'

The WIQL syntax isn’t case-sensitive.

Limits on WIQL length

The WIQL length of queries made against Azure Boards must not exceed 32K characters. The system won’t allow you to create or run queries that exceed that length.

ClauseExample
SELECT
Identifies the fields to return for each work item returned by the query. You can specify either the friendly name or reference name. Use square brackets ([]) if the name contains blanks or periods.
FROMIndicates whether you want the query to find work items or links between work items.
Use FROM WorkItems to return work items.
Use FROM workItemLinks to return links between work items. For more information, see Queries for links between work items later in this article.
WHERESpecifies the filter criteria for the query. For more information, see Filter conditions (WHERE) in the next section.
ORDER BYSpecifies the sort order of the work items returned. You can specify Ascending (Asc) or Descending (Desc) for one or more fields. For example:
ORDER BY [State] Asc, [Changed Date] Desc
ASOFSpecifies a historical query by indicating a date or when the filter is to be applied. For example, this query returns all user stories that existed on June 15, 2019.
ASOF '6/15/2019'

WHERE filter conditions

The WHERE clause specifies the filter criteria. The query returns only work items that satisfy the specified criteria. For example, the following example WHERE clause returns user stories that are active and that are assigned to you.WIQLCopy

WHERE [Work Item Type] = 'User Story'
AND [State] = 'Active'
AND [Assigned to] = @Me

You can control the order in which logical operators are evaluated by enclosing them within parentheses to group the filter criteria. For example, to return work items that are either assigned to you or that you closed, change the query filter to match the following example.WIQLCopy

WHERE [Work Item Type] = 'User Story'
AND [State] = 'Active'
AND ( [Assigned to] = @Me
OR [Closed by] = @Me )

Filter conditions

Each filter condition is composed of three parts, each of which must conform to the following rules:

  • Field: You can specify either the reference name or friendly name. The following examples are valid WIQL syntax:
    • Reference name with spaces: SELECT [System.AssignedTo] ...
    • Friendly name with spaces: SELECT [Assigned To] ...
    • Names without spaces don’t require square brackets: SELECT ID, Title ...
  • Comparison operator: Valid values are specified in the Operators section later in this article. –
  • Field value: You can specify one of the following three values depending on the field specified.
    • literal value must match the data type of the field value.
    • A *variable or macro that indicates a certain value. For example, @Me indicates the person who is running the query. For more information, see Macros and variables later in this article.
    • The name of another field. For example, you can use [Assigned to] = [Changed by] to find work items that are assigned to the person who changed the work item most recently.

For a description and reference names of all system-defined fields, see Work item field index.

Operators

Queries use logical expressions to qualify result sets. These logical expressions are formed by one or more conjoined operations.

Some simple query operations are listed below.

WHERE [System.AssignedTo] = 'joselugo'  
WHERE [Adatum.CustomMethodology.Severity] >= 2

The table below summarizes all the supported operators for different field types. For more information on each field type, see Work item fields and attributes.

The =, <>, >, <, >=, and <= operators work as expected. For instance, System.ID > 100 queries for all work items with an ID greater than 100. System.ChangedDate > '1/1/19 12:00:00' queries for all work items changed after noon of January 1, 2019.

Beyond these basic operators, there are some behaviors and operators specific to certain field types.

Field typeSupported operators
Boolean= , <> , =[Field] , <>[Field]
DateTime= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], In, Not In, Was Ever
Double, GUID, Integer= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], In, Not In, Was Ever
Identity= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], Contains, Does Not Contain, In, Not In, In Group, Not In Group, Was Ever
PlainTextContains Words, Does Not Contain Words, Is Empty, Is Not Empty
String= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], Contains, Does Not Contain, In, Not In, In Group, Not In Group, Was Ever
TreePath=, <>, In, Not In, Under, Not Under

Logical groupings

So, you can use the terms AND and OR in the typical Boolean sense to evaluate two clauses. You can use the terms AND EVER and OR EVER when specifying a WAS EVER operator. Now, you can group logical expressions and further conjoin them, as needed. Examples are shown below.

WHERE [System.State] =  'Active' 
    AND [System.AssignedTo] = 'joselugo' 
	AND ([System.CreatedBy] = 'linaabola' 
    OR [Adatum.CustomMethodology.ResolvedBy] = 'jeffhay') 
    AND [System.State] = 'Closed'
    WHERE [System.State] = 'Active'
    AND [System.State] EVER 'Closed'

You can negate the contains, under, and in operators by using not. You can’t negate the ever operator. The examples below query for all work items that aren’t classified within the subtree of ‘MyProject\Feature1’.

WHERE [System.AreaPath] not under 'MyProject\Feature1'
WHERE [System.AssignedTo] ever 'joselugo'

For more documentation, visit the Microsoft documentation.

Querying all PBIs in an Epic

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.Id] = {Epic ID}
    )
    AND (
        [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
    )
    AND (
        [Target].[System.TeamProject] = @project
        AND [Target].[System.WorkItemType] = 'Product Backlog Item'
    )
ORDER BY [System.Id]
MODE (Recursive)
The query in the editor
The query in the editor

As a workaround, when we add child item(PBI) to an epic, we can add a same tag and get the result via query tag, then we can create a dashboard chart to see the number and states of the PBIs in a given Epic.

The result as a list and as chart
The result as a list and as chart

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.