InCycle Software's Application Modernization and DevOps Blog

Query for TFS Version Control Command Calls Based on Source Path

Written by Ryan Riehle | Jul 9, 2009 5:51:00 PM

Say you wanted to create a report that included all commands users issue against the source code repository, based on its path in source control. Such a reports might contain data like this:

Username Source Control Folder Start Time Command
Darren $/ScrumIsAwesome/ 07/09/2009 19:38 Download

The first thing you would need to do is increase the verbosity of the logging being performed in Team Foundation Server; TFS inserts additional data into the TfsActivityLogging database if you go to the global web.config file (usually found in C:\program files\Microsoft Visual Studio 2008 Team Foundation Server\Web Services\), and change the commandLogging option in the appSettings section to “All”, like this:

<span style="color: #0000ff;">&lt;</span><span style="color: #800000;">appSettings</span><span style="color: #0000ff;">&gt;</span>

<span style="color: #008000;">&lt;!-- WEB METHOD LOGGING</span><span style="color: #008000;">Specify web method logging behavior. The default value is 'None'. </span><span style="color: #008000;">Valid values and their meaning are:</span><span style="color: #008000;"> None (Never log web methods)</span><span style="color: #008000;"> OnError (Include web methods that encounter errors)</span><span style="color: #008000;"> ReadWrite (Include web methods that change database(s))</span><span style="color: #008000;"> Normal (Above, plus web methods that don't change database(s))</span><span style="color: #008000;"> LightWeight (Above, plus web methods that have minimal database access)</span><span style="color: #008000;"> All (Above, plus always include web method request details when avail.)</span><span style="color: #008000;">--&gt;</span><span style="color: #0000ff;">&lt;</span><span style="color: #800000;">add</span> <span style="color: #ff0000;">key</span><span style="color: #0000ff;">="commandLogging"</span> <span style="color: #ff0000;">value</span><span style="color: #0000ff;">="All"</span><span style="color: #0000ff;">/&gt;</span>

Then, restart the TFS Application Pool in IIS:


Then, run some source control operations (check in some files, get the latest version of files you don’t have already, etc…).

Finally, execute the following SQL query on the TfsActivityLogging database:

SELECT identityname "UserName"

 

,parametervalue "Source Control Root Folder"

 

, starttime "Start Time"

 

,Command

 

FROM tbl_command (nolock) c

 

LEFT JOIN tbl_parameter (nolock) p

 

ON c.commandid = p.commandid

 

WHERE p.parametervalue LIKE '$/%'

 

I tested this on Team Foundation Server 2008, Service Pack 1. The verbose logging may effect the performance of your server, so make sure you monitor it.