CODEWITHSUNDEEP
tfs
ekrako
ekrako

Reputation: 179

Find bug that the severity was changed on TFS

I need to find all the bugs in a project on TFS that the severity was changed to High during the last 2 weeks. I can only find the bugs that are opened in the last 2 weeks with severity High with the following query:

<?xml version="1.0" encoding="utf-8"?>
<WorkItemQuery Version="1">
  <TeamFoundationServer>http://tfs-srv:8080/tfs/defaultcollection</TeamFoundationServer>
  <TeamProject>Project</TeamProject>
  <Wiql>SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo],[System.State] FROM WorkItems WHERE [System.TeamProject] = @project  AND  [System.WorkItemType] = 'Bug'  AND  [Microsoft.VSTS.Common.Severity] = 'High'  AND  [System.CreatedDate] &gt; @today - 14 ORDER BY [System.Id] </Wiql>
</WorkItemQuery>

Upvotes: 1

Views: 421

Answers (2)

e-mre
e-mre

Reputation: 3373

I don't think this can be done by simply defining a query through VS because it involves comparing two work item history records.

On the other hand, TFS database can be queried directly to get the data. Try running this query on TFSWarehouse database on your TFS Server:

SELECT DATEDIFF(DD, H.System_ChangedDate, GETDATE()) as DaysAgo, H.System_Id, H.System_ChangedDate
, H.System_Rev, H2.System_Rev, H.Microsoft_VSTS_Common_Severity, H2.Microsoft_VSTS_Common_Severity
, 'X', H.*
FROM WorkItemHistoryView H INNER JOIN WorkItemHistoryView H2 ON H.System_Id = H2.System_Id 
                                                                AND H.System_Rev = H2.System_Rev + 1
                                                                AND H.Microsoft_VSTS_Common_Severity = 'High' 
                                                                AND H2.Microsoft_VSTS_Common_Severity = 'Low' 
                                                                AND H.System_ChangedDate BETWEEN DATEADD(dd, -14, GETDATE()) AND GETDATE()
                                                                AND H2.RecordCount = 1
WHERE H.ProjectNodeName = 'MyProject'
AND H.System_WorkItemType = 'Bug'
AND H.RecordCount = 1
ORDER BY H.System_Id ASC

Upvotes: 2

Syed Ali
Syed Ali

Reputation: 279

simply go to Work Item Type ........>my query and then make a new query. it is a simple UI which makes your query in easy way. you can find it by following example:

team project = your project

work itemn type = bug

severity = high

Upvotes: 0

Related Questions