Querying TFS with TFPT.EXE and PowerShell

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.

Outlook Tasks in PowerShell and Vim

Getting Outlook Tasks in PowerShell is actually pretty easy. All you need to do is use the Office Interop to get access to the default tasks folder, then iterate through the items that are returned.

Create a file called Get-OutlookTask.ps1 in your scripts folder, and paste the following code:

Add-Type -AssemblyName Microsoft.Office.Interop.Outlook
$folders = "Microsoft.Office.Interop.Outlook.OlDefaultFolders" -as [type]
$outlook = New-Object -ComObject outlook.application
$mapi = $outlook.GetNameSpace("mapi")
$tasks = $mapi.getDefaultFolder($folders::olFolderTasks)

foreach($task in $tasks.Items)
{
  if(!$task.complete)
  { 
    $task.subject 
  }
}

Naturally, you could customize this script to work any way you like – for example, you could sort by priorty or date, or you could show an extra column for category information. I have actually set up an alias to this script, so all I have to do is type tasks to see all my current tasks.

If you have set Vim to work with PowerShell, you can import your Outlook Tasks directly into the open document by using the Vim command:

:r! tasks

Pretty cool huh? If you’re looking to add Outlook Tasks in a similar way, you can check out this script by Lee Holmes. I use this one with the alias of nt to save on all that extra typing.

If you want to be able to add Outlook Tasks from Vim, I suggest you add the following function to your vimrc file:

function! Task(args)
  system("nt \\\"" . a:args . "\\\"")
endfunction

command! -nargs=1 Task :call Task('')

Now you just need to call :Task buy milk and it’ll get added – without switching to the shell itself.