如何在TFS 2017中查询自定义看板列的变更日期？

Got it, let's break down how to pull those custom Kanban column change records you can see in work item histories. Since the data’s already being stored (you’ve confirmed it in the history), we just need to target the right fields and use the right tools to extract it. Here are your best options:

1. Use WIQL (Work Item Query Language) Directly in TFS/Azure DevOps #

WIQL is the built-in query language for work items, and it can tap into revision history for custom Kanban columns. First, you need to confirm the exact field name tied to your custom column:

• Open a work item that has a Kanban column change in its history
• Click the specific change entry, then look for the internal field name (it’ll look something like Microsoft.VSTS.Common.BoardColumn_<YourTeamName>—since each team’s Kanban board uses its own unique field for columns)

Once you have that field name, you can build two useful types of queries:

a. Find All Work Items That Had Column Changes #

This query filters work items that have ever had their custom Kanban column modified:

SELECT
  [System.Id],
  [System.Title],
  [System.RevisedDate],
  [Microsoft.VSTS.Common.BoardColumn_<YourTeamName>]
FROM WorkItems
WHERE
  [System.TeamProject] = @project
  AND [System.WorkItemType] IN ('User Story', 'Task') -- Adjust to your work item types
  AND EXISTS (
    SELECT *
    FROM WorkItemRevisions
    WHERE
      [System.Rev] > 1
      AND [Microsoft.VSTS.Common.BoardColumn_<YourTeamName>] CHANGED
  )
ORDER BY [System.RevisedDate] DESC

b. Track Column Changes Across All Revisions #

This query pulls every revision of a work item, showing the Kanban column state at each point in time:

SELECT
  [System.Id],
  [System.Title],
  [System.Rev],
  [Microsoft.VSTS.Common.BoardColumn_<YourTeamName>],
  [System.RevisedDate]
FROM WorkItemRevisions
WHERE
  [System.TeamProject] = @project
  AND [System.WorkItemType] IN ('User Story', 'Task')
  AND [Microsoft.VSTS.Common.BoardColumn_<YourTeamName>] IS NOT NULL
ORDER BY [System.Id], [System.Rev] ASC

Run this, and you’ll see a row for every revision—compare the column values across rows to spot exactly when and how the column changed.

2. Use the Azure DevOps REST API for Automated/Programmatic Access #

If you need to pull this data into a custom tool or script, the REST API lets you fetch full revision history for work items. Use the Get Work Item Revisions endpoint:

GET https://dev.azure.com/{organization}/{project}/_apis/wit/workitems/{workItemId}/revisions?api-version=7.1-preview.3

In the response, each revision object has a fields property. Look for your custom Kanban column field here, and compare values between consecutive revisions to identify changes. You can loop through all work items in your project to bulk-collect this data if needed.

3. Use Azure DevOps Analytics for Visualization #

If you’re on a newer version of TFS/Azure DevOps (2019+), the Analytics service makes it easy to build visual reports of column changes:

• Go to your project’s Analytics views
• Create a new view that includes work item revisions, selecting fields like Work Item ID, Title, Board Column (Your Team), and Revised Date
• Export the view to Excel or connect it to PowerBI to build charts/tables that show column change timelines for all your work items

Quick Note #

Double-check the field name if your queries aren’t returning data—custom Kanban columns are tied to specific teams, so the field name will include your team’s identifier. The work item history is the most reliable place to confirm this exact name.

内容的提问来源于stack exchange，提问作者Charles Foushee