Add additional VMhost information to an existing .csv file

[dbutch1976]

Hello,

I have an existing .csv file that contains a variety of information on ESXi hosts. The file is currently missing IP address and vCenter information. I would like to connect to all the vCenters in my environment, then run a script that will import the existing information from the .csv file, then add the two missing fields (VCENTER,HOSTIP). This is what I have so far:

$hostnames = Import-Csv C:\output\vmhosts.csv

Foreach ($hostname in $hostnames) {
    $vmhost = get-vmhost -Name $hostname.name

    $vCenter    = $vmhost.Uid.Split('@')[1].Split(':')[0].Split('.')[0]
    $cluster    = $vmhost.Parent
    $hostIP     = ($vmhost.ExtensionData.Config.Network.Vnic | ? {$_.Device -eq "vmk0"}).Spec.Ip.IpAddress
    write-host $vmhost $vCenter $cluster $hostIP
        
}

The is pulling the correct information, how do I now add the two new fields to the CSV file and then add the IP and Vcenter info it back to the original CSV file?

Sample CSV file contents:

Name	Parent
blue.lebrine.local	VSAN
grey.lebrine.local	VSAN
extreme.lebrine.local	VSAN
pink.lebrine.local	VSAN

Also, the purpose of doing it this way is to compare against the existing inventory, so many of these hostnames will not be found and will error out. Is it possible to just mark this as 'not found' or something like that when the script runs?

[LucD]

Try something like this

Import-Csv C:\output\vmhosts.csv -PipelineVariable row |
ForEach-Object -Process {
   $esx = Get-VMHost -Name $row.Name
   $row.Add('vCenter',([System.Uri]$esx.ExtensionData.Client.ServiceUrl).Host)
   $row.Add('IP',($esx.ExtensionData.Config.Network.Vnic | Where-Object {$_.Device -eq "vmk0"}).Spec.Ip.IpAddress)
   $row
} |
Export-Csv -Path C:\output\new-vmhosts.csv -NoTypeInformation

------------------------------

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

------------------------------

[dbutch1976]

Hi LucD,

I got this error:

Method invocation failed because [System.Management.Automation.PSCustomObject] does not contain a method named 'Add'.
At C:\Users\dbutcher\OneDrive - McKesson Corporation\Desktop\PowerShell\getVMhost_fromCSV_ExportCSV.ps1:4 char:4
+    $row.Add('vCenter',([System.Uri]$esx.ExtensionData.Client.ServiceU ...
+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Add:String) [], RuntimeException
    + FullyQualifiedErrorId : MethodNotFound
 

Also, what will happen if it's unable to find one of the VMhosts from the original list? Will it just move onto the next one and leave those columns blank?

[LucD]

My bad, the $row object is not the type I assumed.
Try like this

Import-Csv C:\output\vmhosts.csv -PipelineVariable row |
ForEach-Object -Process {
    $esx = Get-VMHost -Name $row.Name
    $vc = ([System.Uri]$esx.ExtensionData.Client.ServiceUrl).Host
    $ip = ($esx.ExtensionData.Config.Network.Vnic | Where-Object {$_.Device -eq "vmk0"}).Spec.Ip.IpAddress
    $row |
    Add-Member -Name 'vCenter' -Value $vc -MemberType NoteProperty -PassThru |
    Add-Member -Name 'IP' -Value $ip -MemberType NoteProperty -PassThru
} |
Export-Csv -Path C:\output\new-vmhosts.csv -NoTypeInformation

------------------------------

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

------------------------------

[dbutch1976]

Looks great. Is there a way to have it skip to the next host if it can't find the host? Right now it throws an error:

Get-VMHost :     Get-VMHost        VMHost with name 'myvm1.lebrine .com' was not found using the specified filter(s).    

[LucD]

You could use a Try-Catch construct

Import-Csv C:\output\vmhosts.csv -PipelineVariable row |
ForEach-Object -Process {
   try {
      $esx = Get-VMHost -Name $row.Name -ErrorAction Stop
      $vc = ([System.Uri]$esx.ExtensionData.Client.ServiceUrl).Host
      $ip = ($esx.ExtensionData.Config.Network.Vnic | Where-Object { $_.Device -eq "vmk0" }).Spec.Ip.IpAddress
      $row |
      Add-Member -Name 'vCenter' -Value $vc -MemberType NoteProperty -PassThru |
      Add-Member -Name 'IP' -Value $ip -MemberType NoteProperty -PassThru
   } catch {
      Write-Host "ESXi node $($row.Name) not found"
   }
} |
Export-Csv -Path C:\output\new-vmhosts.csv -NoTypeInformation

------------------------------

Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

------------------------------