PowerShell - Need to test if item contained in a list

Question

I have an object that holds a list of patches (actually 2 lists) One of the patches in the list contains the words "Cumulative Update" in his title so I run in a loop and find the one that holds this string in the TFS. In the next step I take the patch ($match, in my case is 1307131) and fetch all the relations URLs. from now on I don't know how to continue :). Each url holds at the end the number of the patch that contained in the CU path ($match). The goal is to find if any patch in $patchesList is listed in the CU patch (1307131) and if it does need to print them (except the CU himself of course). For an example if 1292054 and 1295392 are part of 1307131 so the result should be that 1292054 and 1295392 already contained in the CU patch. This solution should be a part of a big script so if there is result (means that one of the patches in $patchesList is already listed under the CU) the script should stop

Connect-PnPOnline -Url $SharePointSiteURL # -Credentials $PSCredentials 
    $ExcelObject = New-Object -ComObject Excel.Application
    $ExcelWorkBook = $ExcelObject.Workbooks.Open($SharePointSiteURL)  
    $ExcelWorkSheet = $ExcelWorkBook.Sheets.Item("VIP List")
    
    Write-host "Worksheet was opened"
    Write-host
    $rowMax = $ExcelWorkSheet.UsedRange.Rows.Count # get the number of rows in the sheet
    $colMax = $ExcelWorkSheet.UsedRange.Columns.Count # get the number of columns in the sheet

$patches = for ($row = 1; $row -le $rowMax; $row++) {
        $color = $ExcelWorkSheet.Cells.Item($row, [uint16]$columnID).Interior.ColorIndex # 2 is column B
        $value = $ExcelWorkSheet.Cells.Item($row, [uint16]$columnID).Value2
        if ($color -eq $searchcolorForPatch -and $value -eq $valueToSearchForPatches ) {
            # output an object with both values from columns A and B
            [PsCustomObject]@{Patch = $ExcelWorkSheet.Cells.Item($row, 1).Value2 }
        }
    }

# In real life this list of objects comes from an Excel sheet
$patchesList = @(ConvertFrom-Csv @'
Patch
1292054
1295392
1307131
1259868
1322467
1324214
1292054
1295392
1307131
1259868
1322467
1324214
'@)

foreach ($patches in $patchesList) {
    foreach ($patch in $patches) {

        $title = (Get-VSTeamWorkItem -Id $patch.Patch).fields.'system.Title'
        if ($title -like "Cumulative Update") {
            $match = $patch.Patch
        }
    }

}
$match

$CU_title = Get-VSTeamWorkItem -Id $match -Expand All
$Urls = $CU_title.relations
foreach ($url in $Urls) {
    $url | Where-Object { $_.rel -eq 'System.LinkTypes.Dependency-Reverse' } 
   
}

The result of this is:

rel                                 url                                                                   attributes                                                    
---                                 ---                                                                   ----------                                                    
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1292054 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1295392 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1295145 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1289076 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1297571 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1298737 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1293404 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1297096 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1296487 @{isLocked=False; comment=Included in Patch; name=Predecessor}
System.LinkTypes.Dependency-Reverse https://tfs.bandit.com/DefaultCollection/_apis/wit/workItems/1284666 @{isLocked=False; comment=Included in Patch; name=Predecessor}



CU          patch
1307131     1292054,1295392
        
Or 
        
CU          patch
1307131     1292054
1307131     1295392


Not

CU          patch
1307131     1292054
1307131     1295392
1307131     1307131

Answer 1

I think you want to change your double foreach loop into

$match = foreach ($patch in $patchesList) {
    $title = (Get-VSTeamWorkItem -Id $patch.Patch).fields.'system.Title'
    if ($title -like '*Cumulative Update*') {  # use wildcards for -like
        # output the patch ID. It will collect in variable $match
        [int]$patch.Patch
    }
}

Seeing that Get-VSTeamWorkItem can take an array of patch ids [int32[]], you may even try this (untested)

$match = Get-VSTeamWorkItem -Id ([int[]]$patchesList.Patch) -Expand All | 
         Where-Object { $_.fields.'system.Title' -like '*Cumulative Update*' }
foreach ($url in $match.relations) {
    $url | Where-Object { $_.rel -eq 'System.LinkTypes.Dependency-Reverse' } 
}

---

I cannot test this myself, but you could try to match the PatchId's with their cumulative update ID's like below:

$match = Get-VSTeamWorkItem -Id ([int[]]$patchesList.Patch) -Expand All | 
         Where-Object { $_.fields.'system.Title' -like '*Cumulative Update*' } | 
         ForEach-Object {
            $patch   = $_
            $relUrls = ($patch.relations | Where-Object { $_.rel -eq 'System.LinkTypes.Dependency-Reverse' }).url
            # output objects
            foreach ($url in $relUrls) {
                [PsCustomObject]@{
                    CU    = ($url -split '/')[-1]
                    Patch = $patch.Id
                }
            }
         }

# output on screen
$match

# output to CSV file
$match | Export-Csv -Path 'X:\Somewhere\Patches.csv' -UseCulture -NoTypeInformation