At Branded3 we use Team Foundation Server for source control, task managment, and various other tracking purposes. One of the benefits of this is being able to run queries with WIQL to pull off reports.

As is usually the case with me, I have set up a couple of PowerShell scripts that use TFPT.EXE from the Team Foundation Server Power Tools to make life a little simpler for myself…

Viewing Open WorkItems

$TFSSERVER   = "hq-tfs08-01.branded3.net"

Function Get-WorkItem
{
    $query = "SELECT [System.Id], [System.Title] FROM WorkItems " +
             "WHERE [System.AssignedTo] = 'Julian Kay' " +
             "AND [System.State] <> 'Closed' " +
             "AND [System.State] <> 'Resolved' " +
             "ORDER BY [System.Id]"

    tfpt query /collection:$TFSSERVER /wiql:$query /include:data
}

This little script gets a list of WorkItems which are not closed or resolved from TFS. I find this much faster than opening a copy of Visual Studio to find out which tasks I have assigned to me. True you could run this kind of script by using a batch file, but I like the fact I can use this in conjunction with the various Outlook scripts I use for PowerShell with simple one-liners like foreach ($workItem in Get-WorkItem) { Add-OutlookTask “$workItem” }

Getting Work Hours

Function Get-WorkItemHours
{
    $month = (Get-Date).ToString("MMMM")
    $year  = (Get-Date).Year
    $query = "SELECT [Completed Work] FROM WorkItems " +
             "WHERE [System.AssignedTo] = 'Julian Kay' " +
             "AND [Assigned Month] = '$month' " +
             "AND [Assigned Year] = '$year'"

    $hours = tfpt query /collection:$TFSSERVER /wiql:$query /include:data
    $total = 0.0
    foreach ($hour in $hours)
    {
       $total += $hour
    }
    $total
}

We also have custom fields which help us keep track of the hours we’ve spent on a project, and as shown above, we can even see how many hours have been spent in a month. By running this simple script I can be sure that all my time is correctly recorded to enable us to both bill correctly, and keep track of how long things really take.

Naturally, everyone has their own requirements for scripts like this, but PowerShell gives me the flexibility to create these simple utilities with very little development work.