PowerShell脚本问题：二次Invoke-WebRequest数据合并至数组并匹配VM

Hey there, let's get that annotation data properly linked to your VMs! The core problem right now is that you're trying to reference $annotation in your VM loop, but you never actually fetch the specific annotation entry that matches the current VM's Name. Here's how to fix this, plus some optimizations to make your script run smoother:

Step 1: Create a Lookup Hash Table for Annotations #

First, we'll convert your $annotations collection into a hash table (dictionary) where the key is the VM Name—this lets us instantly look up the right annotation for any VM, instead of searching the entire list every time (way faster for large datasets).

Add this right after you import and process $annotations:

# Convert annotations to a hash table for fast lookups (key = VM Name)
$annotationLookup = @{}
foreach ($anno in $annotations) {
    # Only add entries where Name exists to avoid null keys
    if (-not [string]::IsNullOrEmpty($anno.Name)) {
        $annotationLookup[$anno.Name] = $anno
    }
}

Step 2: Fetch Matching Annotation in Your VM Loop #

Now, inside your ForEach($vm in $vms) block, grab the annotation that matches the current VM's Name from the hash table. We'll also handle cases where a VM has no matching annotation (so properties don't end up as null unexpectedly):

Modify that loop like this:

ForEach($vm in $vms) {
    # Get the matching annotation for this VM
    $currentAnnotation = $annotationLookup[$vm.Name]

    $data = New-Object System.Object
    $data | Add-Member -MemberType NoteProperty -Name "Name" -Value $vm.Name
    $data | Add-Member -MemberType NoteProperty -Name "PowerState" -Value $vm.PowerState
    # Use the matched annotation properties, or empty string if no match
    $data | Add-Member -MemberType NoteProperty -Name "Annotationserverdescription" -Value $currentAnnotation.Annotationserverdescription ?? ""
    $data | Add-Member -MemberType NoteProperty -Name "Annotationapowner" -Value $currentAnnotation.Annotationapowner ?? ""
    $data | Add-Member -MemberType NoteProperty -Name "Annotationclient" -Value $currentAnnotation.Annotationclient ?? ""
    $data | Add-Member -MemberType NoteProperty -Name "Volume Name" -Value $lun.'Volume Name'
    $data | Add-Member -MemberType NoteProperty -Name "LUN UID" -Value $lun.'LUN UID'
    $data | Add-Member -MemberType NoteProperty -Name "Capacity (GiB)" -Value $lun.'Capacity (GiB)'
    $data | Add-Member -MemberType NoteProperty -Name "Storage Pool Name" -Value $lun.'Storage Pool Name'
    $data | Add-Member -MemberType NoteProperty -Name "Storage System name" -Value $lun.'Storage System name'
    $data | Add-Member -MemberType NoteProperty -Name "Storage Tier" -Value $lun.'Storage Tier'
    
    $arr1 += $data
}

Bonus: Optimize Your Array Collection #

A quick tip: Using $arr1 += $data can be slow if you have a lot of entries because PowerShell creates a new array every time you append. Instead, use an ArrayList which is designed for dynamic additions:

Replace $arr1 = @() with:

$arr1 = New-Object System.Collections.ArrayList

Then replace $arr1 += $data with:

[void]$arr1.Add($data)

Full Modified Script #

Here's the complete script with all the fixes and optimizations:

$StorageSystemName = "storageName"
$StoragePoolName = "storagepool"
$ReportName = "~\Reports\ServerList_$((Get-Date).ToString('yyyy-MM-dd')).xlsx"

# Fetch and import LUN data
Invoke-WebRequest -Uri http://srv1/location/Report_volume_storage.csv -OutFile .\Report_volume_storage.csv
$luns = Import-Csv .\Report_volume_storage.csv -Delimiter ";" | 
    Where-Object {$_.'Storage System name' -eq $StorageSystemName -and $_.'Storage Pool Name' -eq $StoragePoolName -and $_.'Volume Name'} | 
    Sort-Object "Storage Pool Name", "Volume Name"

# Fetch and import annotation data
Invoke-WebRequest -Uri http://srv2/addmdata/addmdata.csv -OutFile .\addmdata.csv
$annotations = Import-Csv .\addmdata.csv -Delimiter "," | 
    Select @{n='Name';e={$_.name.Split('.')[0]}}, 
           @{n="Annotationserverdescription";e={$_.'Server Description'}}, 
           @{n="Annotationapowner";e={$_.'Annotationapowner (Annotationappowner)'}}, 
           @{n="Annotationclient";e={$_.'Client'}} | 
    Sort-Object Name

# Create lookup hash table for annotations
$annotationLookup = @{}
foreach ($anno in $annotations) {
    if (-not [string]::IsNullOrEmpty($anno.Name)) {
        $annotationLookup[$anno.Name] = $anno
    }
}

# Use ArrayList for faster dynamic collection
$arr1 = New-Object System.Collections.ArrayList

ForEach($lun in $luns) {
    $dsnaa = "*$($lun.'LUN UID')*"
    $datastore = Get-Datastore | Where {($_.ExtensionData.Info.Vmfs.Extent).DiskName -like $dsnaa}
    
    $VMs = @()
    $datastore | ForEach-Object {
        $dstore = $_.name
        $VMs += get-VM -datastore $dstore | Where {$_.PowerState -eq "PoweredOn"} | 
            Select @{n="Name";e={$_.name}}, 
                   @{n="PowerState";e={$_.PowerState}}, 
                   @{n="Datastore_Name";e={$dstore}}
    }

    ForEach($vm in $vms) {
        $currentAnnotation = $annotationLookup[$vm.Name]

        $data = New-Object System.Object
        $data | Add-Member -MemberType NoteProperty -Name "Name" -Value $vm.Name
        $data | Add-Member -MemberType NoteProperty -Name "PowerState" -Value $vm.PowerState
        $data | Add-Member -MemberType NoteProperty -Name "Annotationserverdescription" -Value $currentAnnotation.Annotationserverdescription ?? ""
        $data | Add-Member -MemberType NoteProperty -Name "Annotationapowner" -Value $currentAnnotation.Annotationapowner ?? ""
        $data | Add-Member -MemberType NoteProperty -Name "Annotationclient" -Value $currentAnnotation.Annotationclient ?? ""
        $data | Add-Member -MemberType NoteProperty -Name "Volume Name" -Value $lun.'Volume Name'
        $data | Add-Member -MemberType NoteProperty -Name "LUN UID" -Value $lun.'LUN UID'
        $data | Add-Member -MemberType NoteProperty -Name "Capacity (GiB)" -Value $lun.'Capacity (GiB)'
        $data | Add-Member -MemberType NoteProperty -Name "Storage Pool Name" -Value $lun.'Storage Pool Name'
        $data | Add-Member -MemberType NoteProperty -Name "Storage System name" -Value $lun.'Storage System name'
        $data | Add-Member -MemberType NoteProperty -Name "Storage Tier" -Value $lun.'Storage Tier'
        
        [void]$arr1.Add($data)
    }
}

$arr1 | Export-Excel $ReportName

Key Notes: #

• The ?? "" operator is the null-coalescing operator—if the annotation property is null (because no matching entry was found), it will use an empty string instead, preventing errors in your Excel export.
• We cleaned up the $annotations Select statement to only include the properties you actually need (no need to include * then exclude, just pick the calculated properties directly).

内容的提问来源于stack exchange，提问作者Troy Hector