ALM Ranger’s release DevOps Guidance for PowerShell DSC – perfectly timed for DDDNorth

In a beautiful synchronicity the ALM Rangers DevOps guidance for PowerShell DSC has been release at at the same time as I am doing my DDDNorth session ‘What is Desired State Configuration and how does it help me?’

This Rangers project has been really interesting to work on, and provide much of the core of my session for DDDNorth.

Well worth a look if you want to create your own DSC resources.

PowerShell Summit Europe 2014

I find I am spending more time with PowerShell these days, as we aim to automated more of our releases and specifically with DSC in PowerShell 4, as I am sure many of us are

Give that fact, the PowerShell Summit Europe 2014 at the end of the month looks interesting. I only found out about it too late and I have diary clashes but might be of interest to some of you. Looks like a really good hands event.

Listing all the PBIs that have no acceptance criteria

Update 24 Aug 2014:  Changed the PowerShell to use a pipe based filter as opposed to nested foreach loops

The TFS Scrum process template’s Product Backlog Item work item type has an acceptance criteria field. It is good practice to make sure any PBI has this field completed; however it is not always possible to enter this content when the work item is initially create i.e. before it is approved. We oftan find we add a PBI that is basically a title and add the summary and acceptance criteria as the product is planned.

It would be really nice to have a TFS work item query that listed all the PBIs that did not have the acceptance criteria field complete. Unfortunately there is not way to check a rich text or html field is empty in TFS queries It has been requested via UserVoice, but there is no sign of it appearing in the near future.

So we are left the TFS API to save the day, the following PowerShell function does the job, returning a list of non-completed PBI work items that have empty Acceptance Criteria.


# Load the one we have to find, might be more than we truly need for this single function
# but I usually keep all these functions in a single module so share the references
$ReferenceDllLocation = "C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\ReferenceAssemblies\v2.0\"
Add-Type -Path $ReferenceDllLocation"Microsoft.TeamFoundation.Client.dll" -ErrorAction Stop -Verbose
Add-Type -Path $ReferenceDllLocation"Microsoft.TeamFoundation.Common.dll" -ErrorAction Stop -Verbose
Add-Type -Path $ReferenceDllLocation"Microsoft.TeamFoundation.WorkItemTracking.Client.dll"  -ErrorAction Stop –Verbose


function Get-TfsPBIWIthNoAcceptanceCriteria { <# .SYNOPSIS This function get the list of PBI work items that have no acceptance criteria .DESCRIPTION This function allows a check to be made that all PBIs have a set of acceptance criteria .PARAMETER CollectionUri TFS Collection URI .PARAMETER TeamProject Team Project Name .EXAMPLE Get-TfsPBIWIthNoAcceptanceCriteria -CollectionUri "http://server1:8080/tfs/defaultcollection" -TeamProject "My Project" #> Param ( [Parameter(Mandatory=$true)] [uri] $CollectionUri , [Parameter(Mandatory=$true)] [string] $TeamProject ) # get the source TPC $teamProjectCollection = New-Object Microsoft.TeamFoundation.Client.TfsTeamProjectCollection($CollectionUri) try { $teamProjectCollection.EnsureAuthenticated() } catch { Write-Error "Error occurred trying to connect to project collection: $_ " exit 1 } #Get the work item store $wiService = $teamProjectCollection.GetService([Microsoft.TeamFoundation.WorkItemTracking.Client.WorkItemStore]) # find each work item, we can't check for acceptance crieria state in the query $pbi = $wiService.Query("SELECT [System.Id] FROM WorkItems WHERE [System.TeamProject] = '{0}' AND [System.WorkItemType] = 'Product Backlog Item' AND [System.State] <> 'Done' ORDER BY [System.Id]" -f $teamproject) $pbi |  where-Object { $_.fields | where-object {$_.ReferenceName -eq 'Microsoft.VSTS.Common.AcceptanceCriteria' -and $_.Value -eq ""}} # Using a single piped line to filter the wi # this is equivalent to the following nested loops for those who like a more winded structure # $results = @() # foreach ($wi in $pbi) # { # foreach ($field in $wi.Fields) # { # if ($field.ReferenceName -eq 'Microsoft.VSTS.Common.AcceptanceCriteria' -and $field.Value -eq "") # { # $results += $wi # } # } # } # $results }

Creating a TFS Team Project Collection using Powershell

Grant Holiday posted on how to create a TPC using Powershell. However his post did not address how to set the SharePoint or Reporting Services parameters. His example used for the form

Dictionary<string, string> servicingTokens = new Dictionary<string, string>();
servicingTokens.Add(“SharePointAction”, “None”); // don’t configure sharepoint
servicingTokens.Add(“ReportingAction”, “None”); // don’t configure reporting services

So not much use if like ourselves you have your TFS integrated with the company wide SharePoint farm

Finding the Parameters (the hard way)

If you want configure SharePoint or Reporting Services it turns out there is not source of documentation for the process. So thanks then to Chris Sidi for telling me how to find out the required options. This is his process to find out what parameters a TPC needs to be created with:

  1. Create a TPC via the TFS Administration Console, specifying a custom configuration for Sharepoint and Reporting for your system.
  2. In the Administration Console at the bottom of the screen, switch to the Status tab. Double-click “Create Collection” to load the servicing log.
  3. In the first ~15 lines of the servicing log, find the JobId (e.g. “Setting token. Key: JobId. Value: 9638bd57-f494-4ac3-a073-dd1548ab24dc.”)
  4. Using Powershell, query the servicing tokens used:

[Reflection.Assembly]::Load(“Microsoft.TeamFoundation.Client, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”)
$credProvider = new-object Microsoft.TeamFoundation.Client.UICredentialsProvider
#Update the TFS url as necessary
$tfsConnection = new-object Microsoft.TeamFoundation.Client.TfsConfigurationServer “
http://localhost:8080/tfs“, $credProvider
$jobService = $tfsConnection.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamFoundationJobService])

# Replace the JobId. Use the one in the servicing log.
$jobId = ‘9638bd57-f494-4ac3-a073-dd1548ab24dc’
$servicingJob = $jobService.QueryJobs([Guid[]] @($jobId))[0]

This script gets the list of parameters as shown below which we can pass into the PowerShell script used to create a new  TPC.

SharePointAction UseExistingSite
SharePointServer 3eYRYkJOok6GHrKam0AcSA==wytV0xS6vE2uow3gjrzAEg==
SharePointSitePath sites/test
ReportingAction CreateFolder
ReportServer 3eYRYkJOok6GHrKam0AcSA==KRCi2RTWBk6Cl1wAphaxWA==
ReportFolder /TfsReports/test

It is shame the SharePoint and Reporting Services servers are returned as hash codes not their URLs, but as these will probably be fixed for any TFS implementation this is not a major issue as they can just be hardcoded.

Finding the Parameters (the easy way)

Once I ran this script I actually noticed that I already had access to this information without running  PowerShell or creating any trial TPCs. Isn’t that so often the case the information is under your nose but you don’t recognise it.

Actually all the parameters are actually shown at the start of the creation log for any TPC creation on a given TFS server. Just look for the log files via the TFS admin console.

[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational] Creating dictionary with 14 initial tokens:
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     FinalHostState => Started
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     VMM_SHARES (Value is an empty string.)
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     VMM_HOSTS (Value is an empty string.)
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     INT_USRACC (Value is an empty string.)
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     SharePointAction => UseExistingSite
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     SharePointServer => 3eYRYkJOok6GHrKam0AcSA==wytV0xS6vE2uow3gjrzAEg==
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     SharePointSitePath => sites/test
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     ReportingAction => CreateFolder
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     ReportServer => 3eYRYkJOok6GHrKam0AcSA==KRCi2RTWBk6Cl1wAphaxWA==
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     ReportFolder => /TfsReports/test
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     DataTierConnectionString => Data Source=sqlserver;Initial Catalog=Tfs_Configuration;Integrated Security=True
[Info   @11:12:34.876] [2011-05-04 20:32:36Z][Informational]     CollectionName => test
[Info   @11:12:34.876] [2011-05-04 20:32:37Z][Informational]     InstanceId => 0cf30e1d-d8a6-4265-a854-9f6c5d288f7e
[Info   @11:12:34.876] [2011-05-04 20:32:37Z][Informational]     DefaultDatabase => Data Source=sqlserver;Initial Catalog=Tfs_test;Integrated Security=True

The revised TPC creation script

So given this new set of parameters we can edit Grant’s script to pass in all the extra parameters as shown below

    [string]$tpcName = $( throw “Missing: parameter tpcname”),
    [string]$serverUrl = http://localhost:8080/tfs/ ,
    [string]$sqlServer = “sqlserver” ,
    [string]$spBase = “sites”, 
    [string]$spServer = “3eYRYkJOok6GHrKam0AcSA==wytV0xS6vE2uow3gjrzAEg==”, 
    [string]$rsBase = “TfsReports”,
    [string]$rsServer = “3eYRYkJOok6GHrKam0AcSA==KRCi2RTWBk6Cl1wAphaxWA==” )

Write-Host “Using the TPC with the following settings”
Write-Host “tpcName:   $tpcName”
Write-Host “serverUrl: $serverUrl”
Write-Host “sqlServer: $sqlServer”
Write-Host “spBase:    $spBase”
Write-Host “spServer:  $spServer”
Write-Host “rsBase:    $rsBase”
Write-Host “rsServer:  $rsServer”

# Load client OM assembly.
[Reflection.Assembly]::Load(“Microsoft.TeamFoundation.Client, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”);

# Get the server
$tfsServer = new-object Microsoft.TeamFoundation.Client.TfsConfigurationServer $serverUrl;

# Create the token set
$servicingTokens = New-Object “System.Collections.Generic.Dictionary“2[System.String,System.String]”

$spPath = $spBase +”/” +  $tpcName;
Write-Host “Sharepoint path is  $spPath”
$servicingTokens.Add(“SharePointAction”, “UseExistingSite”);
$servicingTokens.Add(“SharePointServer”, $spServer);
$servicingTokens.Add(“SharePointSitePath”, $spPath);

$rsPath = “/” + $rsBase + “/” + $tpcName
Write-Host “Reporting Services path is  $rsPath”
$servicingTokens.Add(“ReportingAction”, “CreateFolder”);
$servicingTokens.Add(“ReportServer”, $rsServer);
$servicingTokens.Add(“ReportFolder”, $rsPath);

# Create and run the job
$tpcSvc = $tfsServer.GetService([Microsoft.TeamFoundation.Framework.Client.ITeamProjectCollectionService]);

$sqlString =”Server=$sqlServer;Integrated Security=SSPI;”
Write-Host “SQL connection string is  $sqlString “

$job = $tpcSvc.QueueCreateCollection(
    $tpcName,      # collection name.
    “”,                  # description.
    $false,              # don’t make this the default collection.
    “~/” + $tpcName + “/”,   # virtual directory.
    “Started”,           # State after creation.
    $servicingTokens,               # tokens for other services.
    $sqlString,       # The SQL instance to create the collection on. Specify SERVER\INSTANCE if not using default instance
    $null,               # null because the collection database doesn’t already exist.
    $null)               # null because the collection database doesn’t already exist.

Write-Host “Creating TPC (this could take a few minutes)”
$collection = $tpcSvc.WaitForCollectionServicingToComplete($job)
Write-Host “Compeleted”

If you get any errors from the script the best place to go is the TFS Admin console to look at the logs (the same ones you used to find the parameters). You should see the detailed create process log that gives normal TF errors as to why the process has failed; usually rights on SharePoint for Reporting Services from my experience.

Signing Powershell scripts with a Thawte code signing certificate

I hit a problem today when trying to sign a powershell script as detailed on Scott Hanselman’s blog with a Thawte code signing certificate.

The basic issue was that the certificate could be seen in the Personal section of the Certificates MMC snap-in. Also it was listed if I issued the Powershell command

Get-ChildItem cert:\CurrentUser\My

but if I added the flag to only show usable codesigning (Class III) certificates it was not listed.

Get-ChildItem cert:\CurrentUser\My -codesigning

Turns out the issue was the same as you see when trying to sign Office 2000 VBA scripts. You have to have imported the certificate with it’s key as detailed on the Thawte site, using the PVKIMPRT.EXE tool. This means you need the MYCERT.P7B and the MYKEY.PVK for the import.

This is made a bit more complex if using the Thawte web site and a Vista client PC as your purchased certificate is installed into you local registry automatically (you don’t get a separate key file). So it would work on the PC used to purchase the certificate but you could not export it. Hence the tip here is to purchase the certificate on an XP client PC so you get both the certificate and key files; Ok you have to manually install the certificate and the key but is is easier in the long term.

Once this is done you can sign the powershell script using the command

Set-AuthenticodeSignature file.ps1 @(Get-ChildItem cert:\CurrentUser\My -codesigning)[0]

Powershell and SourceSafe

I posted yesterday on using Powershell to email a TFS user if they had files checked out. Well, we still run a legacy set of SourceSafe databases for old projects that are under maintenance, but not major redevelopment. (Our usual practice is to migrate projects to TFS at major release points).

Anyway, these SourceSafe repositories are just as likely, if not more so, to have files left checked out as TFS. The following script email a list of all checked out files in a SourceSafe DB

# to run this script without signing need to first run
#  Set-ExecutionPolicy  Unrestricted
# (the other option is to sign the script)

#  then run it using
#  .\VSSstatus.ps1

function CheckOutVSSFileForUser(
    [string]$server    )
    # get the open file list
    [char]10 + “Checking checked out file in ” + $ssdir

    # set the environment variable without this you cannot access the DB

    # we assume the logged in user has rights, as the -Yuid,pwd ss.exe
    # parameter does not work due to the ,
    # could used a named user that takes no password as another option
    # can use the -U option to limit the user listed
    $filelist = &”C:\Program Files\Microsoft Visual Studio\VSS\win32\ss.exe” status $/ -R

    # we have the results as an array of rows, so insert some line feeds
    foreach ($s in $filelist) { $emailbody = $emailbody + [char]10 + $s }
    # not the strange concatenation for the email to field, not a + as I suspected!
    $title = “File currently have checked out in ” + $ssdir
    SendEmail $to  $from $server  $title  $emailbody

function SendEmail(
    [string]$body    )

    # send the email
    $SmtpClient = new-object
    $ = $server
    “Email sent to ” + $to

# the main body
$emailServer = “”
$from = “ “
$to = “ “
$ssdir = (“\\server\sourcesafe1”,

foreach ($s in $ssdir) {
    CheckOutVSSFileForUser $s $to $from $emailServer

Update 10-0ct-2008: Corey Furman has extended script this on his blog


Using Powershell to remind users of checked out files from TFS

With any source control system it is possible to leave files checked out. This is especially true if your IDE does the checking out behind the scenes. This is made worse still by the fact you can have a number of workspaces on the same PC in TFS. It is too easy to forget.

It is therefore a good idea to check from time to time that the files you have checked out are the ones you think you have. There is nothing worse than trying to work on a project to find a key file is checked out or locked to another user or PC.

To this end I have written the following Powershell script to check for the files checked out by a team of developers. In this version you have to list the users by name, but I am sure it could be extended to pickup users for an AD or TFS Group


# To run this script without signing need to first run
#     Set-ExecutionPolicy  Unrestricted
# If you want run it from a timer you will need to sign it

# Then run it using
#    .\TFstatus.ps1

function CheckOutTFSFileForUser(
    [string]$from     )
    # get the open file list,
    # we put a newline at the start of the line,
used an ASCii code as ‘n did not seem to work
    [char]10 + “Checking checked out file for ” + $user
    $filelist = &”C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\tf.exe” status /user:$user /s:

    # we have the results as an array of rows
so insert some line feeds
    foreach ($s in $filelist) { $emailbody = $emailbody + [char]10 + $s }
    # note the strange concatenation for the email to field, not a + as I suspected being new to Powershell
    $title = “Files currently have checked out to ” + $user + ” in TFS”
    if ($user -eq “*” )
        # if they have asked for all user email send to the admin/from account
        $to = $from
    } else
        $to = $user + $domain
    SendEmail $to  $from $server  $title  $emailbody

function SendEmail(
    [string]$body    )

    # send the email
    $SmtpClient = new-object
    $ = $server
    “Email sent to ” + $to

# the main body
$domain = “”
$emailServer = “”
$from = “ “

# the list of users to check, the * means all
$users = (“*”,”anne”,”bill”,”chris”)
# loop through the list of users
foreach ($u in $users) { CheckOutTFSFileForUser $u $domain $emailServer $from }