Monitoring Check-in Policy Overrides

I’m often asked by customers why there is no way to prevent check-in policy overrides in TFS. Usually I respond along the lines of, “Well, it should be a really rare occurrence (otherwise why have the policy?) and besides, every action against TFS is tracked, so you can monitor overrides and beat ‘chat nicely’ to the developer who is overriding the policies”. Which is all well and fine, but exactly how do you monitor policy overrides?

This weekend I checked Amazon to see if I could find Professional Team Foundation Server 2010 for my Kindle – and to my surprise, there it was! Ed Blankenship, one of the authors, is a colleague at Notion Solutions and is an absolute genius on all things TFS. The other authors are, of course, TFS legends too! I instantly purchased the book and while going through it, found a section in Chapter 7 that speaks about monitoring check-in policy overrides.

There are 2 main ways of doing so – via email alerts and via the warehouse.

Email Notification

Install the latest version of the Team Foundation Power Tools – anyone using TFS should always have this installed! One of the features that gets installed with these tools is a richer notifications manger – the Alerts Explorer.

In VS, go to Team->Alerts Explorer and add a “Check-In to specific folder with policy overriden” alert (under the Check-in alerts section).


Now you’ll get an email whenever someone does something nefarious and overrides the check-in policy you’ve so meticulously planned and put in place…

Monitoring via the Warehouse

The second method of monitoring check-in overrides is to query the warehouse. In the warehouse database (called Tfs_Warehouse by default) there is a table called


. One of the columns on this table is


, which is null whenever there is no override and which contains the policy override comment (which is mandatory when overriding) otherwise. So you can easily craft a query (and turn it into a report) that looks for check-ins with a


. Here’s the T-SQL:

<span style="color: blue">SELECT<br>    </span><span style="color: teal">[ChangesetSK]</span><span style="color: gray">,<br>    </span><span style="color: teal">[ChangesetID]</span><span style="color: gray">,<br>    </span><span style="color: teal">[ChangesetTitle]</span><span style="color: gray">,<br>    </span><span style="color: teal">[PolicyOverrideComment]</span><span style="color: gray">,<br>    </span><span style="color: teal">[LastUpdatedDateTime]</span><span style="color: gray">,<br>    </span><span style="color: teal">[TeamProjectCollectionSK]</span><span style="color: gray">,<br>    </span><span style="color: teal">[CheckedInBySK]<br></span><span style="color: blue">FROM<br>    </span><span style="color: teal">[Tfs_Warehouse]</span><span style="color: gray">.</span><span style="color: teal">[dbo]</span><span style="color: gray">.</span><span style="color: teal">[DimChangeset]<br></span><span style="color: blue">WHERE<br>    </span><span style="color: teal">[PolicyOverrideComment] </span><span style="color: gray">IS NOT NULL<br></span>

Happy monitoring!