I have seen this question (and derivatives) asked many times: "I would like to know when X folder changes in a given branch of TFS". The most typical answer ends up along the lines of "write your own check-in policy" or "setup alerts on the folder". There are several drawbacks (in my opinion) to this approach:
- Writing a check-in policy requires that you deploy the policy to all of the client machines. This isn't so bad in a small environment, but it can be problematic in a larger environment.
- It requires development and testing effort. In my particular scenario, I don't have any developers with down time to currently address this.
- Alerts have to be manually setup on folders, and you can't specify a wildcard alert such that you get notified when the same folder in all different branches gets modified.
- Alerts fire every single time a change is made; this quickly becomes unmanageable
My solution to overcome these drawbacks was to create a custom stored procedure inside the TFS database that I run nightly as SQL Agent Job. The stored procedure examines the tables in the TfsVersionControl database to determine which files (if any) have changed in the given folders, and sends a report via email to the appropriate recipients.
SELECT
i.DisplayName AS [Developer],
cs.ChangeSetId AS [Changeset],
row3.Value AS [BranchName],
REPLACE(row8.Value, '>', '_') AS [FileName],
cs.CreationDate AS [CreatedDate],
REPLACE(LEFT(v.FullPath, LEN(v.FullPath) - 1), '>', '_') AS [FilePath]
FROM tbl_Version v
JOIN tbl_VersionedItem vi ON v.ItemId = vi.ItemId
JOIN tbl_ChangeSet cs ON v.VersionFrom = cs.ChangeSetId
JOIN tbl_Identity i ON cs.CommitterId = i.IdentityId
LEFT JOIN tbl_File f ON v.FileId = f.FileId
CROSS APPLY func_SplitString(v.FullPath, '\') row3
CROSS APPLY func_SplitString(v.FullPath, '\') row8
WHERE
v.FullPath LIKE '$\<project>\%\<folder_to_monitor>\%'
AND cs.CreationDate BETWEEN (GETUTCDATE() - 1) AND GETUTCDATE()
--The third row in the temp table should always be the branch
AND row3.ID = 3
--The last column will always be the filename, but it is a variable element in the path
AND row8.ID = (SELECT MAX(id) FROM func_SplitString(v.FullPath, '\'))
ORDER BY cs.ChangeSetId DESC
func_SplitString is just a function that splits a string and returns a two column (ID, Value) wide table. The only thing I don't like about this query is the multiple calls to CROSS APPLY, but I couldn't think of a better way to extract the Branch Name and File Name from the FullPath without doing a bunch of string manipulation.
Some quick explanation:
- v.FullPath LIKE '$\< project >\%\< folder_to_monitor >\%' The first wildcard is used to look at all branches in the given team project. The second wildcard looks for all files and folders underneath the folder I want to monitor.
- AND row3.ID = 3 The branch name (in my case) will always be the third row in the temp table returned by the function.
- AND row8.ID = (SELECT MAX(id) FROM func_SplitString(v.FullPath, '\')) In most cases the filename is going to be in the 8th row of the table returned by the function, but there are cases where it could be more or less. Thus I need to make another call to the split function to determine what my max value is.
Any feedback on how to make this query better would be greatly appreciated, and I hope that someone else finds it useful.
