VMware Cloud Community
burvil
Enthusiast
Enthusiast

Combining results from different queries (hardware and ESXi version) into one csv file

I'm trying to get data for my hosts in a given vCenter for both ESXi version and hardware information in one CSV file.  I tried the following, but can't quite get the results I need.  I'm getting somewhat close, but still not there.  Any thoughts on how to go about this would be greatly appreciated.

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

First, when I pull the information into separate csv files, I get what I expect.  In other words, I use this, and get the data I want, but in two separate files:

[string]$Outfile =  "$($Basedir)\ESXiVersion.$($vCenter).csv"
get-view -ViewType HostSystem -Property Name, Config.Product | select Name,{$_.Config.Product.FullName},{$_.Config.Product.Build},$vCenter,@{N="Datacenter";E={Get-datacenter -VMHost $_.Name}}  | Export-Csv -NoTypeInformation -UseCulture -Path $Outfile 
[string]$Outfile =  "$($Basedir)\Hardware.$($vCenter).csv"
Get-VMHost |Sort Name |Get-View | Select Name, @{N="Type";E={$_.Hardware.SystemInfo.Vendor+ " " + $_.Hardware.SystemInfo.Model}},@{N="Serial Number";E={$_.Hardware.SystemInfo.OtherIdentifyingInfo[0].IdentifierValue+ " " }}| Export-Csv -NoTypeInformation -UseCulture -Path $Outfile 

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

So my first thought was to put what I want into one array, like this:
$Fields = @{
        Name = $hostinfo.Name
        FullName = $hostinfo.FullName
        Build = $hostinfo.Build
        Datacenter = $hostinfo.Datacenter 
        Model = $hwinfo.Model
        Serial_Number = $hwinfo.Serial_Number    
        vCenter = $($vCenter)
    }
[string]$Outfile =  "$($Basedir)\Hardware.$($vCenter).csv"
$Fields | Export-Csv -NoTypeInformation -UseCulture -Path $Outfile

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

But that didn't print the expected data, only a metadata representation of it, enclosed in curly brackets. 
So my next thought was to do a nested object, to do this first query, and nest the second object as part of the first object, like this:
$hostinfo = get-view -ViewType HostSystem -Property Name, Config.Product | select Name,
        @{N="FullName";E={$_.Config.Product.FullName}},
        @{N="Build";E={$_.Config.Product.Build}},
        @{N="vCenter";E={$vCenter}},
        @{N="Datacenter";E={Get-datacenter -VMHost $_.Name}},
        Write-Host "This is closer to what I want ...."    
        @{N="Model";E={Get-VMHost -Name $_.Name | Get-View  | Select { $_.Hardware.SystemInfo.Model } }} 
Write-Host "Host info is ...."
$hostinfo


Running this yields:
Host info is ....

Name       : hostname1-here
FullName   : VMware ESXi 6.0.0 build-3620759
Build      : 3620759
vCenter    : vcenter-name-here
Datacenter : datacenter-name-here
Model      : @{ $_.Hardware.SystemInfo.Model =PowerEdge R630}
Name       : hostname2-here
FullName   : VMware ESXi 6.0.0 build-3620759
Build      : 3620759
vCenter    : vcenter-name-here
Datacenter : datacenter-name-here
Model      : @{ $_.Hardware.SystemInfo.Model =PowerEdge R630}
Name       : hostname3-here
FullName   : VMware ESXi 6.0.0 build-3620759
Build      : 3620759
vCenter    : vcenter-name-here
Datacenter : datacenter-name-here
Model      : @{ $_.Hardware.SystemInfo.Model =PowerEdge R630}

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

So, the data is there, but as an array, and not a string. I only want the actual model in as a value.  How can I do that?  Anyone see what I'm missing? 

Reply
0 Kudos
2 Replies
LucD
Leadership
Leadership

You didn't use the correct syntax for the calculated properties.

Try like this

$Outfile =  "$($Basedir)\ESXiVersion.$($vCenter).csv"

Get-View -ViewType HostSystem -Property 'Name','Config.Product','Hardware.SystemInfo' |

Select Name,

    @{N='Product';E={$_.Config.Product.FullName}},

    @{N='Build';E={$_.Config.Product.Build}},

    @{N='vCenter';E={$global:defaultViServer.Name}},

    @{N='Datacenter';E={Get-datacenter -VMHost $_.Name}},

    @{N="Type";E={$_.Hardware.SystemInfo.Vendor+ " " + $_.Hardware.SystemInfo.Model}},

    @{N="Serial Number";E={$_.Hardware.SystemInfo.OtherIdentifyingInfo[0].IdentifierValue+ " " }} |

Export-Csv -NoTypeInformation -UseCulture -Path $Outfile


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

Reply
0 Kudos
burvil
Enthusiast
Enthusiast

Thanks!  That works great!

Reply
0 Kudos