Where to find "Change Type" for changed files

Question

As part of our annual external auditing, we've been tasked to report on any files that have changed since the beginning of the year, and provide tracability back to the original business request for the change, as well as the proof of testing and signoff before deployment to production.  I am trying to satify part of this by identifying the changed files.  Here's what I started with:

use tfswarehouse
SELECT     [File Path], __LastUpdatedTime AS [Last Updated]
FROM         [File]
WHERE     ([File Path] LIKE '$/MyTeamProject/Branches%') AND (__LastUpdatedTime >= @FromDate) AND ([File Extension] <> 'dir')
ORDER BY [File Path], [Last Updated]

This is great, except that it includes all the files that have changed as a result of branching.  In other words, these files didn't really change, they were simply checked in to a new branch.

When I view item history, there's a column that indicates "add", "delete", "edit", "branch", "merge", etc.  I'd like to be able to use this value to limit my result set, but I cannot for the life of me figure out where this is stored.  It looks like it may only be in the tfsVersionControl database, but where?

If anyone has an answer to this, or can offer ways to answer the general question "What changed and why did it change?" I would appreciate it.

---

Tim Dallmann

Answer 1

Hi Tim,

The change type is persisted in tfsVersionControl.tbl_Version.Command column. Branch command is 128 (0x80). This value is undocumented and it may be changed in the future release. It is not recommended directly write SQL query. Instead, you can use TFS SDK API to query all the version and check-in information.

You can alos consider using code churn to determine if one file has been changed or not in OLAP database.

 

Hongye Sun [MSFT]

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact msdnmg at microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi Tim,

Is this issue resolved? Please feel free to let me know if you need any further help on this. Thanks.

Have a nice day.

 

Hongye Sun [MSFT]
MSDN Subscriber Support in Forum
If you have any feedback on our support, please contact msdnmg @ microsoft.com

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 3

I opted to use the CodeChurn approach:

SELECT 
	  F.[File Path]
	  ,CodeChurn.[__LastUpdatedTime] As [Change Date]
	  ,P.[Person] As [Checked In By]
	  ,CodeChurn.[Changeset]
	  ,WI.[System_Title] As [Work Item Desc]
  FROM 
	[dbo].[Code Churn] CodeChurn
	inner join dbo.[Person] P
		on CodeChurn.[Checked In By] = P.[__ID]
	inner join [dbo].[File] F 
		on [Filename] = F.[__ID]
		AND F.[File Path] LIKE '$/PAS/Branches%'
		AND [File Extension] NOT IN 
			(  
				   '.vssscc'   -- source control file
				 , '.vspscc'   -- source control file
				 , '.vbproj'   -- project file (container for code)
				 , '.csproj'   -- project file (container for code)
				 , '.sln'      -- solution file (container for project files)
				 , '.resx'     -- resource file (managed by developmennt environment - not directly changed by developer)
				 , '.msbuild'  -- build script used to automate the compilation of code
				 , '.nsi'      -- build script used to package the installation files
				 , '.licx'     -- infragistics license file (managed by developmennt environment - not directly changed by developer)
				 , '.cmd'      -- utility command files used to perform developer desktop functions
			 )		
	inner join dbo.[Work Item Changeset] WIC 
		ON CodeChurn.ChangeSet = WIC.__ID
	inner join dbo.[Work Item] WI
		ON WIC.ChangeSet = WI.__ID
  WHERE 
	CodeChurn.[Team project] = (Select __ID from dbo.[Team project] where [Team Project] = 'PAS')
	AND CodeChurn.[__LastUpdatedTime] >= @FromDate
	-- remove branch creation
	AND (([Lines Added] = [Net Lines Added] AND ([Lines Modified] > 0 OR [Lines Deleted] > 0))
		OR 
		([Lines Added] <> [Net Lines Added]))
	-- remove branch deletion
	AND (([Lines Deleted] + [Net Lines Added] = 0 AND ([Lines Modified] > 0 OR [Lines Added] > 0))
		OR
		([Lines Deleted] + [Net Lines Added] <> 0))
  order by F.[File Path], [Change Date]

---

Tim Dallmann