Skip to main content

Querying TFS for Data

At my company we are in the process of migrating to a new TFS environment. As part of that we wanted to do some house cleaning and only migrate the projects that have been used recently.

We’ve got a lot of clients, each with their own project that have been created over the past 3 years. We wanted an easy way to see what the latest activity was on the project. That will give us a good view into what projects can be archived and which ones need to be moved.

I could have just gone to Source Control and did a “view history” for each project, but that would have taken a long time (and be very tedious). Instead I wrote a simple console app that digs into each project collection and project and exports some simple project/Check-in data to a CSV file. I can then open up the CSV in Excel and add other info like “Code Status”, and “Migration Status” easily.

Here is the code I used. This is a pretty basic example and TFS provides a lot more, but it did what I needed it to. As I always say, Keep it simple!

using Microsoft.TeamFoundation;
using Microsoft.TeamFoundation.Client;
using Microsoft.TeamFoundation.Framework.Client;
using Microsoft.TeamFoundation.Framework.Common;
using Microsoft.TeamFoundation.VersionControl.Client;

namespace TFSInterrogator
    class Program
        static void Main(string[] args)
            //Set up the TFS SErver URL
            string tfsServer = "";
            tfsServer = tfsServer.Trim();
            var tfsUri = new Uri(tfsServer);

            //Get the Configuration Server
            var configurationServer = TfsConfigurationServerFactory.GetConfigurationServer(tfsUri);

            // Get the catalog of team project collections
            Guid[] gVar = new Guid[] { CatalogResourceTypes.ProjectCollection };
            var collectionNodes = configurationServer.CatalogNode.QueryChildren(gVar, false, CatalogQueryOptions.None);

            List<TfspcInfo> collections = new List<TfspcInfo>();

            //Iterate over the list of collections
            foreach (var collectionNode in collectionNodes)
                Guid collectionId = new Guid(collectionNode.Resource.Properties["InstanceID"]);

                var teamProjectCollection = configurationServer.GetTeamProjectCollection(collectionId);
                TfspcInfo info = new TfspcInfo();
                info.Name = teamProjectCollection.Name;

                Console.WriteLine("Checking Project Collection:" + info.Name);

                VersionControlServer versionControl = null;

                //If a project collection is offline, the Version Control cannot be retrieved. This catches this scenario
                    versionControl = teamProjectCollection.GetService<VersionControlServer>();
                catch (TeamFoundationServiceUnavailableException)

                // Get a catalog of team projects for the collection
                Guid[] hVar = new Guid[] { CatalogResourceTypes.TeamProject };
                var projectNodes = collectionNode.QueryChildren(hVar, false, CatalogQueryOptions.None);

                info.Projects = new List<TfsProjectInfo>();

                // List the team projects in the collection
                foreach (var projectNode in projectNodes)
                    TfsProjectInfo pInfo = new TfsProjectInfo();
                    pInfo.Name = projectNode.Resource.DisplayName;

                    Console.WriteLine("Checking last check in for Project :" + pInfo.Name);

                    // get the path to the project.
                    string vcpath = @"$/" + pInfo.Name + "/";

                    //Gets all changesets for the project and returns the most recent one.
                    if (versionControl != null)
                        List<Changeset> items = versionControl.QueryHistory(

                        if (items.Count > 0)
                            pInfo.LastCheckin = items.FirstOrDefault();


        private static void WriteToFile(List<TfspcInfo> collections)
            var sbLine = new StringBuilder();
            sbLine.Append("Project Collection,Project,Last Check-in User, Last Check-in Date,Last Check-in Comment");
            foreach (var projectCollection in collections)
                Console.WriteLine("Packaging up Project Collection " + projectCollection.Name + " For Export.");
                foreach (var project in projectCollection.Projects)
                    sbLine.Append(projectCollection.Name + ",");
                    sbLine.Append(project.Name + ",");

                    if (project.LastCheckin != null)
                        sbLine.Append(project.LastCheckin.Committer + ",");
                        sbLine.Append(project.LastCheckin.CreationDate + ",");
                        sbLine.Append(StringToCSVCell(project.LastCheckin.Comment) + ",");
            Console.WriteLine("Exporting to File...");
            File.AppendAllText("c:\\TFSExport.csv", sbLine.ToString());

        public static string StringToCSVCell(string str)
            bool mustQuote = (str.Contains(",") || str.Contains("\"") || str.Contains("\r") || str.Contains("\n"));
            if (mustQuote)
                StringBuilder sb = new StringBuilder();
                foreach (char nextChar in str)
                    if (nextChar == '"')
                return sb.ToString();

            return str;


    public class TfspcInfo
        public string Name { get; set; }
        public List<TfsProjectInfo> Projects { get; set; }

    public class TfsProjectInfo
        public string Name { get; set; }
        public Changeset LastCheckin { get; set; }


Popular posts from this blog

Build/Deploy Windows service with TFS

Building and deploying a web service or website via TFS is pretty straight forward, but automatically deploying a windows service or console application takes a b it of setup. I’ve outlined the steps below to get your service deployed. Step 1: Set up Automated Build and MSDeploy on the Target server. If you are doing any sort of automated build/deploy I recommend using MSDeploy. You can follow steps 1-3 from a previous post here.Step 2: Edit the .csproj fileThe project file for the app you are trying to deploy needs to be edited so that TFS will create a directory for the output of the project. Otherwise the project will be built, but the assemblies will be placed in the code drop location with no organization to it.To edit the file, go to the open file dialog and locate the csproj file. Select it but don’t click “Open. Instead click on the dropdown arrow next to the Open button, select “Open with”, and choose the XML (Text) editor as shown:Once the file is open, add the following “Pr…

Quickly and Easily Deploy Websites/Web Services with TFS Build via Web Deploy (MSDeploy)

When I first started deploying code from TFS I took the simple approach and created a batch file and deployed the websites via RoboCopy. I’m a very “Keep it simple” kind of guy, so this worked for us for a long time and so nothing was changed. With my most recent project however, we were deploying code over a slow VPN tunnel from our servers in Chicago to servers located in Europe. Due to this, the RoboCopy was taking over 4.5 hours to complete. I needed a better/faster way so I started looking into Web Deploy (MSDeploy). I was able to get it working fairly easily and I was pleasantly surprised how easy it was to get it working, and how much time its saved me! I can now deploy the code in less than 20 minutes!I’ve outlined the process in detail below, but in general you only need to do this:Add MSBuild parameters to your automated buildCustomize the deployment parameters for the websiteCreate a batch file to an auto-generated MSDeploy scriptExecute the batch file from the automated bu…

MSDEPLOYAGENTSERVICE 401 unauthorized–Resolution

We recently migrated a production environment for a client to new Servers. I had previously been using MSDeploy to deploy the websites/services to the servers so I figured all I had to do was install MSDeploy, point Update my deploy scripts to point to the new servers, and deploy! I was using MSDeploy 2 on the previous servers so I figured it would work on the new ones. Unfortunately it didn’t turn out to be that easy.
When I ran the updated scripts I got the following error:
Fatal: Request to remote agent URL 'http://myserver/MSDEPLOYAGENTSERVICE' failed.
Fatal: The remote server returned an error: (401) Unauthorized. Fatal count: 1
I was using an admin account and I could hit that URL above in a browser so I knew it wasn’t an authorization issue.
Here are the things I tried that DIDN’T work:
Uninstall/Reinstall MSDeploy 2Install MSDeploy 3Create the fake user group on the server per these instructions:…