How to get git like statistics from TFS

Question

I've been working with TFS for a few months now and would like to get some basic statistics and make them available to our team. In git, i could retrieve statistics on "commit by author" and "commit by date" etc.

I'd like to show similar statistics from TFS (or from TeamCity).

Is this possible?

Answer 1

You can use the TFS API to create any queries you like. You can quite easily iterate through the changesets looking for all commits by a certain author, or commits in a certain date:

TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(new Uri("http://tfsserver:8080/"));
tpc.EnsureAuthenticated();
VersionControlServer vcs = tpc.GetService<VersionControlServer>(); 

int latest = vcs.GetLatestChangesetId();
DateTime earliestDate = new DateTime(2011, 1, 1);
do
{
    var changeset = vcs.GetChangeset(latest);                
    if (changeset.CreationDate < earliestDate)
    {
        break;
    }
    // do analysis of the changeset here,
    // e.g. use changeset.Committer or changeset.Changes
} while (latest-- > 0);

Answer 2

There is a TFS Performance Report Pack which will give you information, but it is not exactly the information you requested

• Execution Time Summary
• Server Status - Source Control Request Queue
• Server Status - Top Users Bypassing Proxies
• Server Status - Historical Performance Trends
• Server Status - Recent Performance Trends

Answer 3

The easiest way I find for poking around in TFS statistics is to play with Excel reports - you can pivot on just about anything you can imagine. For a guide to getting started take a look at this blog post I wrote:

http://www.woodwardweb.com/vsts/getting_started.html

TFS provides a full data warehouse over the top of the regular repository data for you to dig into.

Answer 4

Here you can find some queries you can execute on the TFS database to get some statistics.

-- Change this to the name of your collection DB. You’ll need to run these queries for each of your collection DBs. 
USE Tfs_DefaultCollection 
GO

-- Recent Users 
select count(distinct IdentityName) as [Recent Users] from tbl_Command with (nolock)

-- Users with Assigned Work Items 
select count(distinct [System.AssignedTo]) AS [Users with Assigned Work Items] from WorkItemsAreUsed with (nolock)

-- Version Control Users 
select COUNT(*) AS [Version Control Users] from [Tfs_Configuration].[dbo].tbl_security_identity_cache as ic JOIN tbl_Identity as i ON i.TeamFoundationId=ic.tf_id where ic.is_group = 0

-- Total Work Items 
select count(*) AS [Total Work Items] from WorkItemsAreUsed with (nolock)

-- Areas and Iterations 
select count(*) AS [Areas and Iterations] from tbl_nodes with (nolock)

-- Work Item Versions 
select count(*) AS [Work Item Versions] from (select [System.Id] from WorkItemsAreUsed with (nolock) union all select [System.Id] from WorkItemsWereUsed with (nolock)) x 
-- Work Item Attachments 
select count(*) AS [Work Item Attachments] from WorkItemFiles with (nolock) where FldID = 50 
-- Work Item Queries 
select count(*) AS [Work Item Queries] from QueryItems with (nolock)

-- Files 
select count(*) as [Files] from tbl_VersionedItem vi with (nolock) join tbl_Version v with (nolock) on v.ItemId = vi.ItemId where VersionTo = 2147483647

-- Compressed File Sizes 
select (sum(convert(bigint,OffsetTo - OffsetFrom + 1)) / (1024 * 1024)) AS [Compressed File Sizes] from tbl_Content with (nolock)

-- Uncompressed File Sizes 
select (sum(FileLength) / (1024 * 1024)) AS [Uncompressed File Sizes] from tbl_File with (nolock)

-- Checkins 
select max(ChangeSetId) AS [Checkins] from tbl_ChangeSet with (nolock)

-- Shelvesets 
select COUNT(*) AS [Shelvesets] from tbl_Workspace with (nolock) where type='1'

-- Merge History 
select SUM(st.row_count) AS [Merge History] from sys.dm_db_partition_stats st WHERE object_name(object_id) = 'tbl_MergeHistory' AND (index_id < 2)

-- Pending Changes 
select count(*) AS [Pending Changes] from tbl_PendingChange pc with (nolock) join tbl_Workspace w with (nolock) on pc.WorkspaceId = w.WorkspaceId where w.Type = 0 
-- Workspaces 
select COUNT(*) AS [Workspaces] from tbl_Workspace with (nolock) where type='0' 
-- Local Copies 
select SUM(st.row_count) AS [Local Copies] from sys.dm_db_partition_stats st WHERE object_name(object_id) = 'tbl_LocalVersion' AND (index_id < 2)

-- Command Counts 
select Command, count(*) as [Execution Count] from tbl_Command with (nolock) WHERE Command IN ('QueryWorkitems', 'Update', 'GetWorkItem', 'Get', 'VCDownloadHandler', 'Checkin', 'Upload', 'Shelve') GROUP BY Command, Application ORDER BY [Application],[Command]

Answer 5

I found that there is a change set view in Visual Studio Team System Web Access under the "source" tab. Selected desired project and select version history from the project dropdown.

This is sufficient for my needs.