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 = "https://tfs.projectleadership.net/tfs";
            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
                try
                {
                    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(
                                vcpath,
                                VersionSpec.Latest,
                                0,
                                RecursionType.Full,
                                String.Empty,
                                null,
                                VersionSpec.Latest,
                                Int32.MaxValue,
                                false,
                                true).Cast<Changeset>().ToList();

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

                    info.Projects.Add(pInfo);
                }
                collections.Add(info);
            }
            WriteToFile(collections);
        }

        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");
            sbLine.AppendLine();
            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) + ",");
                    }
                    else
                    {
                        sbLine.Append(",,,");
                    }
                    sbLine.AppendLine();
                }
            }
            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();
                sb.Append("\"");
                foreach (char nextChar in str)
                {
                    sb.Append(nextChar);
                    if (nextChar == '"')
                        sb.Append("\"");
                }
                sb.Append("\"");
                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; }
    }
}