Freeware - LogParser.TFSQuery (Team Foundation Server)

I recently found myself needing to query Team Foundatin Server (TFS) like I had previously done with Visual Source Safe (VSS). I found I needed to following capabilities :-
- Querying and notification of recent work items
- Querying and notification of the most recent check-ins
- Querying and notification of check-outs which were over a time threshold
- Querying and notification of shelvesets (parked code) which expired a certain amount of time
- Querying and notification across linked work items

I could have used out of the box TFS functionality to provide some of the above, but some don't exist without reaching into the database. Therefore given I have LogParser.Email capabilities (ability to e-mail LogParser results) I decided to write a LogParser COM input format plugin for TFS to perform all the above.

Initially I thought I could provide the tool using the direct TFS web services, however after reading online documentation I decided against this approach, mostly because Microsoft haven't got any documentation, nor will guarentee they won't break it between releases. I additionally considered simply parsing the output from TF.EXE, however thought this was inelegant as a solution and prone to error and additionally wouldn’t provide Work Item querying I was looking for.

I therefore decided to look at TFS APIs available in VB.Net. The available functionality is extensive and blogs like James Manning make it much easier to navigate various examples of using the APIs.

Below are details on the solution.

Features

• Freeware with VB.NET 2005 source code included
• Can query Team Foundation Server (TFS) work items, check-ins (ChangeSets), check-outs (Pending ChangeSets), and Shelvesets. This has been tested on TFS 2005 - it may work on earlier or later versions.
• For work items supports either querying using existing Stored Queries, or using direct work item query language (WIQL). The queries can be run across a specific project, or multiple projects.
• For check-ins, check-outs and shelveset queries it reports the file details and associated work items. In addition supports a single or multiple project paths in the same query and the recursion level.
• Work items and check-ins (changesets) support iCheckPoint functionality to enable you to retrieve only the latest changes since the last query. Works on recording the time (UTC) and then querying for item greater than that time.
• The Work items query can retrieve specified fields (either in Stored Query, or WIQL) or return all fields available for that work item.
• Can query multiple different TFS projects, or even different TFS servers and combine the results. Internally used Dataset.Merge functionality.
• Work item query allows for LEFT JOIN-ing linked Work Items. You can specific the fields you want returned in the LEFT JOIN.
• To provide extra querying features Implements a number of custom columns (as per standard LogParser).
• Logs any errors to console and to EventLog.Application
• Supports Log Parser data types.
• Uses the ILogParserInputContext interface to enable LogParser COM interface to directly call. i.e, you can code against this plugin.

*************************************************
Download VB.NET source is included in download.
*************************************************
To install:

Assume that TeamFoundation*.dll (Team Explorer or Visual Studio) is installed in the GAC and working on your system. Stand-alone Team Explorer install instructions. Both Team Explorer and Visual Studio install the dlls into the GAC.

1. Copy the LogParser.TFSQuery folder to a location on your harddrive

2. Run the command .\LogParser.TFSQuery\InstalldotNETasCOM.bat.

3. Run your Log Parser query.

e.g. LogParser.exe -i:COM "select * From 'http://tfsserver:8080/TestProject/My Work Items'" -iProgId:Gluegood.LogParser.TFSQuery -o:DataGrid

A number of samples are available in the downloaded file in SampleQuery.bat

Syntax

LogParser.exe -i:COM "select * From '{Path1}','{Path2}',..." -iProgId:Gluegood.LogParser.TFSQuery -iComParams:"iAllFields={TrueFalse},iCheckPoint={Path},iWIQL={WIQL query},iQueryCheckOuts={TrueFalse},iQueryShelvesets={TrueFalse}, iRecursive={TrueFalse},iWorkItemLinksQuery={TrueWIQL queryField list}"

• {Pathx} - a formatted path that adheres to the following standard.
  - Work items – {server:port}/{project}/{query} e.g. ‘http://tfsserver:8080/ProjectTest/All Work Items’ or ‘tfsserver/*/All Work Items’. Expect the 3 slashes as delimiters even when item isn’t required..
  - Check-ins, Check-outs, shelvesets - {server:port}/{path} e.g. ‘http://tfsserver:8080/$/ProjectTest/Release’ or ‘tfsserver/$/’
  Supports multiple Paths as long as they are deliminated with a comma (,).
• iAllFields - Ignore the columns defined in the query (or WIQL) and retrieve all columns. Default = False
• iCheckPoint – Path to a checkpoint file. Default is no checkpoint.
• iWIQL – WIQL query. Overrides any stored queries specified. Defaults to no iWIQL Hint: If using from command line any commas must be escaped with \u002c.
• iQueryCheckOuts - Query the path specified looking for CheckOuts. If iQueryCheckOuts and iQueryShelvesets are not provided, or both false, then assume querying for Check-ins. Default is false.
• iQueryShelvesets - Query the path specified looking for Shelvesets. If iQueryCheckOuts and iQueryShelvesets are not provided, or both false, then assume querying for Check-ins. Default is false.
• iRecursive – defines wether to recusively scan subfolders, or stay within the current folder/project defined. Default is true.
• iWorkItemLinksQuery – Defines whether to LEFT JOIN all linked Work Items. Extra columns returned will be prefixed with ‘LogParserTFSRelated’. Setting to True will return Linked Work Item Id, Type, State, and Title. Can additionally define your own WIQL (with only SELECT and FROM clause) or a field list. Default is "" (not enabled).

Columns returned (Work items)
LogParserTFSServer – Server in which the query is being executed.
LogParserTFSProject – TFS Project which this query has been executed
LogParserTFSQuery – TFS Stored Query, or WIQL query
LogParserTFSWorkItemId – Work Item Id for this item
LogParserRecordNumber – Record number for this item in the query.
LogParserTFSRelated – If using iWorkItemLinksQuery Columns in the query or all Columns

Columns returned (Check-ins / changesets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who checked in this item.
Date – Date that this check-in occurred.
Comment – Comment associated to this check-in
Path – File path (server side) of this check-in
WorkItemId – Associated Work Item Ids. Will display multiple seperated by a comma.
ChangeSetId – ChangeSet Id.
PolicyOverrideComment – Policy override comment if entered.
PolicyFailures – Number of policy failures

Columns returned (Check-outs / pendingsets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who checked out this item.
Date – Date that this check-out occurred.
Path – File path (server side) of this check-out
PendingSetId – PendingSet Id.
Computer – Computer in which this check-out was conducted against.

Columns returned (Shelvesets)
LogParserTFSServer – Server in which the query is being executed.
LogParserRecordNumber – Record number for this item in the query.
ChangeType – Type of change
User – User who shelved this item.
Date – Date that this shelve occurred.
Path – File path (server side) of this shelve
PendingSetId – PendingSet Id.
Computer – Computer in which this Shelve occurred from.
Name – Name of this shelveset

Challenges

CheckPoint, WIQL and store.Query
When using iCheckPoint with Work Items I had to modifying the supplied stored query or WIQL provided with an additional clause returning the latest items based on System.ChangeDate. I ran into a problem using {string}.replace to look for the existence of existing WHERE clause as it’s case sensitive and I didn’t think I could rely on the case, therefore I ended up using Regex.Replace thanks to this. In addition because the CheckPoint stores date and time I had to modify the way I executed the query from the standard store.Query approach listed in many examples to a more obscure method using the Query object and setting the dayPrecision flag.

Performance and WorkItems
There is a lot of documentation stating that performance is degraded if you query every work item field beyond those returned in the WIQL query (as TFS needs to round trip to the server) , therefore I ensure that when returning fields (without the iAllFields setting) I only reference those fields specified. In addition I bumped up the PageSize to 200.

Changeset, Pendingset and Shelveset and querying
There seems to be some complexity around this (using APIs QueryPendingSets, QueryShelvedChanges, and QueryHistory) and I conducted some research and found this entry helpful in providing directions. In addition I leaned heavily on James Manning’s site and numerous examples provided e.g. Shelvesets

Querying Linked WorkItems and Performance
The easy way would have been to return the Linked Work Item details on each Work Item that had a link. However this performs terribly (as multiple trips are made for every Work Item link). Another better way I found was to

1. Add the RelatedLinkCount field to the initial WIQL query (to determine if there were any links),
2. Then loop through the WorkItem.Links collection (performance hit on this one and doesn't seem to be a way around this) determining if any are WorkItems and then storing any found WorkItem link ids into a BatchReadParameter.
3. Then after cycling all Work Items I then execute a new WIQL store.query using the BatchReadParameterCollection (a list of Work Item Ids).
4. Then I link the original Work Item dataset to the Linked Work Item dataset by moving through all the DataTable.rows. There is a few tricks around this code to support when a Work Item cannot be referenced due to permissions.

GACUTIL and REGASM
My current approach with LogParser COM Input addins was to always install them in the GAC as I mistakenly thought that LogParser when reading the COM components both needed the item registered and installed in the GAC. I realised with this project that I really didn’t want to have to get the entire TeamFoundation assemblies in the GAC and played around with the install and I made 2 discoveries.

a) the TeamFoundation assemblies appear to already be installed in the GAC (if you have Visual Studio Installed, or Team Explorer) and

b) that using REGASM with the /codebase (as per MSDN - The Codebase entry specifies the file path for an assembly that is not installed in the global assembly cache) means you don't need to install into the GAC.

*************************************************

** Legal **

This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.