Powershell - Create new line for multiple array objects using Export-csv

Question

I have an odd one that I haven't seen much writing on. Anyway, here goes.

I'm trying to build an array and export it to CSV. The problem is, if there is more than one result returned, I can't figure out how to add it to the CSV as a new line. I am currently using a -join to throw all the results into the same cell, but that's not optimal. What I'd really like to do is add a new row and throw the extra results underneath it in the same column. Does that make sense? Here's what I have now:

# Grab all VMs, put into variable
$vms = Get-VM


# Use to build report
foreach ($vm in $vms){
    New-Object PSObject -Property @{
    VMName = $vm.Name
    UsedSpaceGB = $vm.UsedSpaceGB
    StorageAllocatedGB = ($vm.HardDisks.capacitygb | Measure-Object -Sum).Sum
    NumberOfCPUs = $cm.NumCpu
    MemoryGB = $vm.MemoryGB
    Datastores = Get-Datastore -VM $vm
    Application = ($vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue).Value | select
    } | select VMName,@{label="Application";expression={$_.Application -join ","}},UsedSpaceGB,StorageAllocatedGB,NumberOfCPUs,MemoryGB,@{l="Datastores";e={$_.Datastores -join ","}}  | Export-Csv -Path C:\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation

} 

By the way, this is using VMware's PowerCLI snapin. Any help is greatly appreciated.

Answer 1

Ok, looks like datastores and applications are the only fields that are going to return arrays according to your previous code. Assuming that the $cm.NumCpu was supposed to be $vm.NumCpu the following code should do what you want. It will figure out if you have more datastores or applications, and then loop through expanding the arrays for those fields creating new records for the same VM listing additional datastores and applications until it runs out of records. I set it to only list all details of a VM on the first record, but I'm sure you can figure out how to alter that if needed. Try this code and see how it looks to you:

# Grab all VMs, put into variable
$vms = Get-VM


# Use to build report
foreach ($vm in $vms){
    $TempVM = New-Object PSObject -Property @{
        VMName = $vm.Name
        UsedSpaceGB = $vm.UsedSpaceGB
        StorageAllocatedGB = ($vm.HardDisks.capacitygb | Measure-Object -Sum).Sum
        NumberOfCPUs = $cm.NumCpu
        MemoryGB = $vm.MemoryGB
        Datastores = Get-Datastore -VM $vm
        Application = ($vm | Get-Annotation -CustomAttribute Applications -ErrorAction SilentlyContinue).Value
    } 
    $Records = if($TempVM.Application.count -gt $TempVM.Datastores.Count){$TempVM.Application.Count}else{$TempVM.Datastores.Count}
    $ExpandedVM = @()
    $ExpandedVM += $TempVM|select Name,UsedSpaceGB,StorageAllocatedGB,NumberOfCPUs,MemoryGB,@{l="Datastores";e={$TempVM.Datastores[0]}},@{l="Application";e={$TempVM.Application[0]}}
    for($i=1;$i -lt $Records;$i++){$ExpandedVM += $TempVM|select Name,@{l="Datastores";e={$TempVM.Datastores[$i]}},@{l="Application";e={$TempVM.Application[$i]}}}
    $ExpandedVM | Export-Csv -Path C:\script\VMCapacityUsedByApp.csv -NoClobber -Append -NoTypeInformation
} 

There may be a more elegant way to do it, but that should be functional for you at the very least. I don't have VM machines to test against, or the plugin you use, so I made up data that should be in line with what you're feeding it (strings for all fields except datastores and application both of which have their own array of strings) and ended up with output like this:

Name        UsedSpaceGB StorageAllo NumberOfCP MemoryGB   Datastores Applicatio
                        catedGB     Us                               n         
----        ----------- ----------- ---------- --------   ---------- ----------
TestVM      250         500         4          16         Store1     Word      
TestVM                                                    Store2     Excel     
TestVM                                                    Store3     Access    
TestVM                                                               Outlook   
TestVM2     487         500         4          32         StoreA     WoW       
TestVM2                                                   StoreB     SC2       
TestVM2                                                   StoreC     D3        
TestVM2                                                   StoreD               
TestVM2                                                   StoreE               

That is what you were looking for I think.