Adding column to existing csv output from vcenter powershell-CodePudding

location vcenter username password abc 10.1 X xxx def 10.2 Y yyy

a csv file which has location column which is a location and in that location IP column indicates the vCenter IP

i need to generate all host details and add location field as the first field in the final csv but not able to do so

Example

Suppose location abc has a vcenter with 5 hosts and location def has a vcenter with 3 hosts

The output shd look like

location name version abc h1 6.5 abc h2 6.5 abc h3 6.5 abc h4 6.5 abc h5 6.5 def hxx 6.7 def hyy 6.7 def hzz 6.7

$inventory = Import-Csv -path path.csv

foreach($i in $inventory){ Connect-VIServer -Server $i.vcenter -User $i.username -Password $i.password Get-VMHost | Select-Object -Property Name,Version | Export-csv tempoutput.csv -NoTypeInformation -Append $ExistingCSV = Import-Csv -Path 'tempoutput.csv' $ExistingCSV | Add-Member -MemberType NoteProperty -Name 'Location' -Value $i.location $ExistingCSV | Export-Csv final.csv -NoTypeInformation
Disconnect-VIServer -Server $i.vcenter -Force -Confirm:$false }

But this is not working

CodePudding user response：

I would only write to the output CSV once provided your memory can hold all of the Get-VMHost data. You may make use of calculated properties to prevent having to use a temporary CSV file.

$inventory = Import-Csv -path path.csv

$output = foreach ($i in $inventory) {
    $null = Connect-VIServer -Server $i.vcenter -User $i.username -Password $i.password
    Get-VMHost | Select-Object -Property Name,Version,@{n='Location';e={$i.Location}}
    $null = Disconnect-VIServer -Server $i.vcenter -Force -Confirm:$false
}

$output | Export-Csv final.csv -NoType

It may be worthwhile to consider a redesign if you have repeated entries of the same vCenter and credentials in the CSV file. Connecting and disconnecting to the same vCenter so many times is likely inefficient. You may make use of Group-Object to only connect and disconnect from vCenter when the vCenter server changes.

$inventory = Import-Csv -path path.csv | Group-Object vcenter,username,password | Foreach-Object {
    $vc = $_.Group[0] | Select-Object vcenter,username,password,location
    $null = Connect-VIServer -Server $vc.vcenter -User $vc.username -Password $vc.password
    Get-VMHost | Select-Object -Property Name,Version,@{n='Location';e={$vc.Location}}
    $null = Disconnect-VIServer -Server $vc.vcenter -Force -Confirm:$false
}

$inventory | Export-Csv final.csv -NoType

In your attempt, you are reading the entire temp CSV in $ExistingCSV during each loop iteration. Then you are adding the property Location to the entire object $ExistingCSV, which adds it to every item in the $ExistingCSV list with the same value. So at the end of the loop, every line of the CSV has a Location value that matches the last item in the loop.