PowerShell and TFS Work Items

James Manning has a blog post about PowerShell script that builds a Team Foundation Server Client object. Once you have this basic script, you can do all kinds of cool things with it.

As a side note, I’d be really interested to know if any other Systems Engineers or Administrators are using Team Foundation Server on their projects, and if so, are you using Agile/Scrum?

Anyway, the bottom line is that we use TFS to track a bunch of tasks that we are working on, and I got tired of having to log in and update my tasks every day. So once I saw James’ script, I started poking around a bit more to see what else I could do. I cam up with two functions in addition to the one James had written. The first is Get-TfsWorkItem and the second is Update-TfsWorkItem. (I Suppose that should be set)

Anyhow, here’s what they can do.



function Get-TfsServer {
param([string] $serverName = "itstfs")
# load the required dll
$propertiesToAdd = (
        ('VCS', 'Microsoft.TeamFoundation.VersionControl.Client', 'Microsoft.TeamFoundation.VersionControl.Client.VersionControlServer'),
        ('WIT', 'Microsoft.TeamFoundation.WorkItemTracking.Client', 'Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore'),
        ('CSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.ICommonStructureService'),
        ('GSS', 'Microsoft.TeamFoundation', 'Microsoft.TeamFoundation.Server.IGroupSecurityService')
    # fetch the TFS instance, but add some useful properties to make life easier
    # Make sure to "promote" it to a psobject now to make later modification easier
    [psobject] $tfs = [Microsoft.TeamFoundation.Client.TeamFoundationServerFactory]::GetServer($serverName)
    foreach ($entry in $propertiesToAdd) {
        $scriptBlock = '
            [System.Reflection.Assembly]::LoadWithPartialName("{0}") > $null
        ' -f $entry[1],$entry[2]
        $tfs | add-member scriptproperty $entry[0] $ExecutionContext.InvokeCommand.NewScriptBlock($scriptBlock)
    return $tfs
function Get-TfsWorkItem {
param($title = "*",
      $user="Andy Schneider",
$WIQL = @"
SELECT [System.Id], [System.WorkItemType], [System.State], [System.AssignedTo], [System.Title] 
FROM WorkItems 
where [System.TeamProject] = '$Project'  AND [System.AssignedTo] = '$user' 
ORDER BY [System.WorkItemType], [System.Id] 
$tfs = Get-TfsServer 
$workItems = $tfs.wit.query($WIQL)
return $workItems | where {$_.Title -like $title}
return $tfs
function Update-WorkItem {
if (!$item.IsOpen) {$item.open()}
$item.Fields[$field].Value = $value
return $item
function Update-TfsWorkItemTime {
param ($item,$hours)
begin {}
process {
  if ($_ -is [Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItem]) {
    $item = $_
    if (!$item.IsOpen) {$item.open()}
    if ($item.Fields["State"].Value = "Not Started") {$item.Fields["State"].Value = "In Progress"}
    $remainingWork = $item.Fields["Remaining Work"].Value
    $completedWork = $item.Fields["Completed Work"].Value
    $item.Fields["Completed Work"].Value = $completedwork + $hours
    $item.Fields["Remaining Work"].Value = $remainingWork - $hours
    $item.Save() | out-nulll
end {}

One point in this code that may need some clarification is the $wiql HERE string. WIQL apparently is “Work Item Query Language.’ Just like WMI uses WQL for WMI Query Language. These are just special version of SQL that are geared for a specific domain. You can find out more about WIQL up on MSDN and search TFS to your heart’s content.

The Update-TfsWorkItem takes an item and add hours to Work Completed and also subtracts from Remaining work.

This should give you enough to start messing with TFS if your PM is hounding you to update your tasks!

Comments are closed