How to find the list of test cases that are associated with n attachments

One of the customer wanted to clean-up attachments from their tfs server using the test attachment cleaner tool. They wanted to find out more details about the attachments that will get cleaned and one of the detail was the list of test cases that will be touched. Since the test attachment cleaner does not provide that list, Nipun from the team created a power shell script to emit out the list of test cases associated with a list of attachments. Here is the script he created.


# parameters
Write-Host "CollectionUrl:" $tfsCollectionUrl
Write-Host "Project name:" $projectName
Write-Host "Attachment Ids:" $attachmentIds

if (!$tfsCollectionUrl -or !$projectName -or !$attachmentIds)

    Write-Host "Usage: GroupAttachmentsByTestCase.ps1 -tfsCollectionUrl `"collectionUrl`" -projectName `"projectName`" -attachmentIds `"attachmentIds`""
    Write-Host "Example: GroupAttachmentsByTestCase.ps1 -tfsCollectionUrl " "`http://myserver:8080/tfs/testdefault` " "-projectName " "`"myproject`" " "-attachmentIds " "`"4,5,6,46,67`""

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

# Connect to tfs
$tfsCollection = [Microsoft.TeamFoundation.Client.TfsTeamProjectCollectionFactory]::GetTeamProjectCollection($tfsCollectionUrl);
$tcmService = $tfsCollection.GetService([Microsoft.TeamFoundation.TestManagement.Client.ITestManagementService]);
$tcmProject = $tcmService.GetTeamProject($projectName);
$query = "SELECT * FROM Attachment WHERE Id IN (" + $attachmentIds +")"
$attachments = $tcmProject.QueryAttachments($query);
foreach ($attachment in $attachments)
    if ($attachment.TestRunId -gt 0 -and $attachment.TestResultId -gt 0)
        $testResult = $tcmProject.TestResults.Find($attachment.TestRunId, $attachment.TestResultId);
        Write-Host "AttachmentId:" $attachment.Id " TestCaseId:" $testResult.TestCaseId
        Write-Host "AttachmentId:" $attachment.Id " is not associated with testcase"