Search code examples
sqlsql-servertfstfs-2015tfs-workitem

TFS2015 - List how many times tags were used in TFS 2015 On-Premise work items

​Hello,

​I am trying to write an SQL query to list all tags along with the number of how many times they were used in Work Items in TFS2015. I cannot find the connection between tbl_WorkItemCoreLatest, tbl_PropertyDefinition and tbl_TagDefinition that would point me to tags used on work items. I tried to go with the solution from here, but with no success. The query results seems not to match what I see directly in VSTS (eg. searching for a particular tag returns me work items from which some don't have that particular tag, almost as if TFS would store previous states of work items...?) If there's a way to do that with REST API, that will work as well.

Any help will be much appreciated.

Solution

  • You can use TFS REST API below to get all work items with System.Tags field, then calculate how many times they were used：

    `http://tfs2015:8080/tfs/DefaultCollection/teamproject/_apis/wit/reporting/workitemrevisions?fields=System.Tags&includeLatestOnly=true&api-version=2`

    Another simple way is creating a work item query with Tags column and open this query in Excel to filter the Tags column.

    enter image description here