Query by assignment or workflow changes

Azure Boards | Azure DevOps Server 2019 | TFS 2018 | TFS 2017 | TFS 2015 | TFS 2013

Workflow states support tracking the status of work as it moves from a new state to a closed or a done state. Kanban query fields support tracking the status of work as it moves from one column or swimlane to another on the Kanban board.

Each workflow consists of a set of states, valid transitions between states, and reasons for transitioning the work item to the selected state. Workflow states and reasons differ among the work item types (WITs) and default processes used to create your project.

Most work items move from a New, Active, or Proposed state to a Done or Closed state. As each work item moves from one state to another, the item might also be reassigned to various members of the team. For example, a tester might create a bug that is assigned to another team member during triage. When the other team member resolves the bug, it is reassigned to the tester who created it.

For example, you can find all work items that were closed but then reactivated. By specifying the Changed Date field, you can focus on reactivations that occurred today, yesterday, or in the last week.

Query Editor filter for reactivated items

You can also use the Activated By and Activated Date fields, or other workflow fields.

Tip

Not all fields are valid for all WITs. Jump to Workflow and Kanban query fields for the set of fields you can include in queries and which WITs they apply to.

If you're new to creating queries, see Use the query editor to list and manage queries.

Supported operators and macros

Query clauses that specify an Identity or workflow-associated field can use the operators and macros listed in the following table. To learn what the field data type is, see the Workflow and Kanban board fields provided later in this article.

Data type

Supported operators and macros

Boolean 1

= , <> , =[Field] , <>[Field]

DateTime

= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], In, Not In, Was Ever

Macros: @Today, @Today +/- n valid with any DateTime field

Identity = , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], Contains, Does Not Contain, In, Not In, In Group, Not In Group, Was Ever

Macros: @me valid for all Identity fields

Single text (String) = , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], Contains, Does Not Contain, In, Not In, In Group, Not In Group, Was Ever

Notes:

  1. The Boolean data type field is supported for TFS 2017 and later versions.

Use the In and Not In operators when you want to filter for or exclude two or more picklist entries or a delimited set of items. Use the In Group or Not In Group operators to filter for items that belong or don't belong within a category group, team security group, or other security group. For more information, see Query fields, operators, and macros.

Identity based queries

You can use the search box or query editor to quickly find work items based on an assignment made to an Identity field. Also, you can filter for work items based on who changed, resolved, or closed a work item. By specifying a time period, you can scope your query even further which can help with performance.

Use = to find current assignments, Was Ever to list items based on past assignments, and @Me to scope to your user identity.

Filter for

Include these query clauses

Active items assigned to me

            Assigned To _ = _ @Me

And _ State _ = _ Active

Closed items that at some point was assigned to me

            Assigned To _ Was Ever _ @Me

And _ State _ = _ Closed

Active user stories assigned to my (Web) team

            Work Item Type = User Story

And _ State _ = _ Active

And _ Assigned To _ In Group _ [FabrikamFiber]\Web

Items I've modified in the last 30 days

            Changed By _ = _ @Me

And _ Changed Date _ >= _ @Today-30

Unassigned items (leave the Value blank)

            Assigned To _ = _

Team or group membership queries

To filter on items assigned to someone who belongs to a team or security group, use the In Group operator.

Filter based on assignment to a TFS security group

You can use the In Group or Not In Group operators to filter a query based on several values that are members of a group, or that are not members of a group. Examples of groups are teams, built-in security groups, custom security groups, Azure Active Directory and Active Directory groups, and work item categories.

Workflow change based queries

You use the State, Reason, and Resolved Reason fields to query for items based on workflow changes.

Filter for Include these query clauses

Resolved stories

            Work Item Type _ = _ User Story

And _ State _ = _ Resolved

Stories, bugs, and tasks that are new or active

            Work Item Type _ In _ User Story,Bug,Task

And _ State _ In _ New,Active

Items removed as they're duplicate

            State _ = _ Removed

And _ Reason _ = _ Duplicate

Items failing acceptance tests

Resolved Reason = Acceptance tests fail

Items closed within the last 15 days

            State _ = _ Closed

And _ Closed Date _ > _ @Today-15

Workflow changes and Identity based queries

You can quickly find items that you changed, resolved or closed. You can also find items that were changed by other team members. Several fields—such as the Created By, Changed By, Resolved By, and Closed By—are populated based on changes to the workflow.

Filter for Include these query clauses

User Stories that I closed

            Work Item Type _ = _ User Story

And _ Closed By _ = _ @Me

Items I resolved in the last week

            Resolved By _ = _ @Me

And _ Resolved Date _ >= _ @Today-7

Kanban board change queries

Using the Kanban query fields—Board Column, Board Column Done, and Board Lane—you can list work items according to their flow status on the Kanban board. And, you can create a status or trend chart based on these queries.

Note

Kanban query fields are available with TFS 2015.1 or later versions.

For example, you can list items based on the team area path, and if they are in a specific custom Kanban column and swimlane. If you rename a column or swimlane, you'll need to update the query filters to reflect the new name. For more ideas, see this blog post: New fields bring Kanban goodness to queries, and more

Query filter on Kanban board fields

Note

Queries are now scoped to the current project by default. Check the Query across projects to find work items defined in other projects within the collection.

Filter for Include these query clauses

User Stories in the Code/Doing column

            Work Item Type = User Story

And _ Board Column _ = _ Code

And _ Board Column Done _ = _ False

Items in the Expedite swimlane Board Lane _ = _ Expedite
Items in any swimlane that contains "Test" Board Lane _ Contains _ Test

Important

Work items that appear on more then one team's Kanban board can yield query results that don't meet your expectations. Because each team can customize the Kanban board columns and swimlanes, the values assigned to work items which appear on different boards may not be the same. The primary work around for this issue is to maintain single ownership of work items by team area path. Another option is to add custom workflow states which all teams can use.

Workflow and Kanban board fields

You can use the following fields to filter your queries or build reports. Some of these fields are populated with information as a work item progresses from one state to another, or you move an item in the Kanban board to a different column or swimlane. Several of these fields do not appear on the work item form, but they are tracked for those WITs listed in the following table.

For more information about field attributes, see Work item fields and attributes.

Field name Description Data type Work item type
Activated By1, 2 The name of the team member who created the work item or changed its status from closed, completed, or done state to a new or active state.

Reference name=Microsoft.VSTS.Common.ActivatedBy

String (Identity) All
Activated Date 2 The date and time when the work item was created or when its status was changed from closed, completed, or done to a new or active state.

Reference name=Microsoft.VSTS.Common.ActivatedDate

DateTime All
Assigned To1, 2, 3 The name of the team member who currently owns the work item. For additional information, see Note 1 on synchronization and person-name fields.

Reference name=System.AssignedTo

String (Identity) All
Board Column The current Kanban board column assignment of the work item, for example: Active, Closed, Committed, Done, or other custom column assignment.

Reference name=System.BoardColumn

String Requirement Category4
Board Column Done

The current assignment of the work item to Doing (False) or Done (True) Kanban column. Only assigned when split-columns has been enabled for a Kanban board column.

Reference name=System.BoardColumnDone

Boolean Requirement Category4

Board Lane

The current Kanban board swimlane assignment of the work item, for example: Default, Expedite, Blocked, or other custom swimlane assignment.

Reference name=System.BoardLane

String Requirement Category4
Closed By1, 2

The name of the team member who set the state to closed, completed, or done.

Reference name=System.ClosedBy

String All
Closed Date

The date and time when a work item was closed.

Reference name=Microsoft.VSTS.Common.ClosedDate

DateTime All
Created By1, 2

The name of the team member who created the work item.

Reference name=Microsoft.VSTS.Common.CreatedBy

String (Identity) All
Created Date

The date and time when a work item was created.

Reference name=Microsoft.VSTS.Common.CreatedDate

DateTime All
Reason 2, 3 The reason why the work item is in the current state.

Values are defined within the WORKFLOW section of the WIT definition using the REASON element. To modify the defined reasons, see Change the workflow for a work item type.

Reference name=System.Reason

String All (except Test Case and Shared Steps)
Resolved By 1, 2

The date and time when the work item was moved into a resolved or done state.

Reference name=Microsoft.VSTS.Common.ResolvedBy

String (Identity) All
Resolved Date 2 The date and time when the work item was moved into a resolved or done state.

Reference name=Microsoft.VSTS.Common.ResolvedDate

DateTime All
Resolved Reason 2 The reason why a work item was resolved. For example, the user story is code complete or the bug is fixed.

This field is read-only and only valid for Agile and CMMI work item types.

Reference name=Microsoft.VSTS.Common.ResolvedReason

String All (Agile, CMMI)
Reviewed By The name of the team member who responded to a code review request and is cataloged in the code review response.

Reference name=Microsoft.VSTS.Common.ReviewedBy

String (Identity) Code Review Response
State 2, 3 The current state of the work item. This field allows you to update the status of a work item as it progresses from new or active to a done or closed state.

Values are defined within the WORKFLOW section of the WIT definition using the STATE element. To add a custom state to Azure Boards, see Customize the workflow for a process. To add or modify States or the workflow for TFS, see Change the workflow for a work item type.

Reference name=System.State

String All
State Changed Date The date and time when the value of the State field changed.

Reference name=Microsoft.VSTS.Common.StateChangeDate

DateTime All

Notes

  1. By default, the server synchronizes system-defined person-name fields with Active Directory or Azure Active Directory, if these are configured. These fields include: Activated By, Assigned To, Closed By, Created By, and Resolved By. You can grant access to a project by adding security groups that you created in AD or Azure AD or by adding accounts to existing or custom groups defined from the collection setting Security page. See Set up Active Directory or Azure Active Directory.

    For on-premises deployments, you can enable or disable synchronization for a person-name field by using the witadmin changefields command-line tool. You can also synchronize custom person-name fields by specifying the syncnamechanges attribute. See Manage work item fields and FIELD (Definition) element reference.

  2. Reportable field with attribute set to Dimension. Reportable data is exported to the data warehouse and can be included in Excel or SQL Server reports. For on-premises TFS, use the witadmin changefield command to change the reportable attribute for a field.
  3. Indexed field. Enabling indexing for a field may increase the performance of finding work items whose queries specify that field. For on-premises TFS, use the witadmin indexfield command to change the index attribute for a field.
  4. This field applies to all work item types that appear on the Kanban board. This includes all WITs added to the Requirement Category and may include those added to the Bug Category based on the team setting for Show bugs on boards and backlogs. If you want to modify a board-related field, such as Board Column or Board Lane, from the work item form, you must add it to the form. For more information, see Add and manage fields (Inheritance process model) or Add or modify a work item field (On-premises XML process model).

Workflow fields

You can use the following fields to filter your queries or build reports. Some of these fields are populated with information as a work item progresses from one state to another. Several of these fields do not appear on the work item form, but they are tracked for those WITs listed in the following table. For more information about field attributes, see Work item fields and attributes.

Field name Description Data type Work item type
Activated By 1, 2 The name of the team member who created the work item or changed its status from closed, completed, or done state to a new or active state.

Reference name=Microsoft.VSTS.Common.ActivatedBy

String (Identity) All
Activated Date 2 The date and time when the work item was created or when its status was changed from closed, completed, or done to a new or active state.

Reference name=Microsoft.VSTS.Common.ActivatedDate

DateTime All
Assigned To 1, 2, 3 The name of the team member who currently owns the work item. For additional information, see Note 1 on synchronization and person-name fields.

Reference name=System.AssignedTo

String (Identity) All
Closed By 1, 2

The name of the team member who set the state to closed, completed, or done.

Reference name=System.ClosedBy

String All
Closed Date

The date and time when a work item was closed.

Reference name=Microsoft.VSTS.Common.ClosedDate

DateTime All
Created By 1, 2

The name of the team member who created the work item.

Reference name=Microsoft.VSTS.Common.CreatedBy

String (Identity) All
Created Date

The date and time when a work item was created.

Reference name=Microsoft.VSTS.Common.CreatedDate

DateTime All
Reason 2, 3 The reason why the work item is in the current state.

Values are defined within the WORKFLOW section of the WIT definition using the REASON element. To modify the defined reasons, see Change the workflow for a work item type.

Reference name=System.Reason

String All (except Test Case and Shared Steps)
Resolved By 1, 2

The date and time when the work item was moved into a resolved or done state.

Reference name=Microsoft.VSTS.Common.ResolvedBy

String (Identity) All
Resolved Date 2 The date and time when the work item was moved into a resolved or done state.

Reference name=Microsoft.VSTS.Common.ResolvedDate

DateTime All
Resolved Reason 2 The reason why a work item was resolved. For example, the user story is code complete or the bug is fixed.

This field is read-only and only valid for Agile and CMMI work item types.

Reference name=Microsoft.VSTS.Common.ResolvedReason

String All (Agile, CMMI)
Reviewed By The name of the team member who responded to a code review request and is cataloged in the code review response.

Reference name=Microsoft.VSTS.Common.ReviewedBy

String (Identity) Code Review Response
State 2, 3 The current state of the work item. This field allows you to update the status of a work item as it progresses from new or active to a done or closed state.

Values are defined within the WORKFLOW section of the WIT definition using the STATE element. To add a custom state to Azure Boards, see Customize the workflow for a process. To add or modify States or the workflow for an on-premises server, see Change the workflow for a work item type.

Reference name=System.State

String All
State Changed Date The date and time when the value of the State field changed.

Reference name=Microsoft.VSTS.Common.StateChangeDate

DateTime All

Notes

  1. By default, the server synchronizes system-defined person-name fields with Active Directory or Azure Active Directory, if these are configured. These fields include: Activated By, Assigned To, Closed By, Created By, and Resolved By. You can grant access to a project by adding security groups that you created in AD or Azure AD or by adding accounts to existing or custom groups defined from the collection setting Security page. See Set up Active Directory or Azure Active Directory.

    You can enable or disable synchronization for a person-name field by using the witadmin changefields command-line tool. You can also synchronize custom person-name fields by specifying the syncnamechanges attribute. See Manage work item fields and FIELD (Definition) element reference.

  2. Reportable field with attribute set to Dimension. Reportable data is exported to the data warehouse and can be included in Excel or SQL Server reports. For on-premises server, use the witadmin changefield command to change the reportable attribute for a field.

  3. Indexed field. Enabling indexing for a field may increase the performance of finding work items whose queries specify that field. For on-premises server, use the witadmin indexfield command to change the index attribute for a field.

SDK resources

To programmatically interact with queries, see Query for Bugs, Tasks, and Other Work Items.