I am having the below code to write the data to csv

$myVMs = @(Get-VM -Server $vc | where {$_.PowerState -eq 'PoweredOn'} | select -Unique)        
        foreach ($myVM in $myVMs){        
         
            $VMDKs = $myVM | get-HardDisk
            $VMDISKMode = $myVM
            foreach ($VMDK in $VMDKs) {
                if ($VMDK -ne $null){
                $Diskmode = $VMDK.Persistence
                     if($Diskmode -ne 'Persistent')
                      {
                    $Report = [PSCustomObject] @{
                            Name = $myVM.name 
                            Server = $vc
                            PowerState = $myVM.PowerState
                            Disk = $VMDK.Name
                            DiskMode = $VMDK.Persistence
                        }
                    $Report | Export-CSV -NoTypeInformation $File -Append
                      }                          
                    }   
                }                
            }            
        }

the output i am getting

Name                Server          PowerState  Disk           DiskMode
171_A92SV095    192.168.1.5         PoweredOn   Hard disk 6    IndependentPersistent
171_A92SV095    192.168.1.5         PoweredOn   Hard disk 7    IndependentPersistent
171_A92SV095    192.168.1.5         PoweredOn   Hard disk 8    IndependentPersistent
171_A92SV095    192.168.1.5         PoweredOn   Hard disk 10   IndependentPersistent
171_A92SV096    192.168.1.5         PoweredOn   Hard disk 5    IndependentPersistent
171_A92SV096    192.168.1.5         PoweredOn   Hard disk 10   IndependentPersistent

want the data like below

Name                Server          PowerState  Disk           DiskMode
171_A92SV095    192.168.1.5         PoweredOn   Hard disk 6    IndependentPersistent
                                                Hard disk 7    
                                                Hard disk 8    
                                                Hard disk 10   
171_A92SV096    192.168.1.5         PoweredOn   Hard disk 5    IndependentPersistent
                                                Hard disk 10   

Please let me know what changes I need to make in the code.

CodePudding user response：

Taking the csv file you created with your code, you can use that as a basis to get the output you need like this:

$data = Import-Csv -Path 'D:\Test\VMInfo.csv'
# first group the data on the Name property
$data | Group-Object Name | ForEach-Object {
    $name = $_.Name
    # next, group that on property PowerState
    foreach ($groupPowerState in ($_.Group | Group-Object PowerState)) {
        $powerState = $groupPowerState.Name
        # finally, group that on property DiskMode
        foreach ($groupDiskMode in ($groupPowerState.Group | Group-Object DiskMode)) {
            [PsCustomObject]@{
                Name       = $name
                Server     = $groupDiskMode.Group[0].Server
                PowerState = $powerState
                Disk       = $groupDiskMode.Group.Disk -join [environment]::NewLine
                DiskMode   = $groupDiskMode.Name

            }
        }
    }
} | Export-Csv -Path 'D:\Test\GroupedVMInfo.csv' -UseCulture -NoTypeInformation

This should give you a new CSV file you can double-click to open in Excel

Using your example data this would open in Excel as:

Of course, you don't need to write the report out to CSV and then read it back in if you would capture the output in variable $data like this:

$data = foreach ($myVM in $myVMs) {
    $VMDKs = $myVM | Get-HardDisk
    $VMDISKMode = $myVM
    foreach ($VMDK in $VMDKs) {
        if ($VMDK -ne $null) {
            $Diskmode = $VMDK.Persistence
            if($Diskmode -ne 'Persistent') {
                [PSCustomObject] @{
                    Name       = $myVM.name
                    Server     = $vc
                    PowerState = $myVM.PowerState
                    Disk       = $VMDK.Name
                    DiskMode   = $VMDK.Persistence
                }
            }
        }
    }
}