Values export-csv into single columns

PowerShell Help
#1

Hi,

My script is not sorting the values into seperate columns for Problems,Solutions, and Impacts. Any suggestions or ideas on how I can fix this?

$allvms=Get-AzVM
$AllVmsInformation=@()
$recommendations=(Search-AzGraph -Query $query).Data
foreach($vm in $allvms) 
{

    $VmInfo=[PSCustomObject]@{
        Name = $vm.Name
        ResourceGroupName = $vm.ResourceGroupName
        Location = $vm.Location
        VMSize = $vm.HardwareProfile.VmSize
        $query ='advisorresources | where type == "microsoft.advisor/recommendations"  and properties.impactedField=="Microsoft.Compute/virtualMachines" and properties.impactedValue== "'+ $vm.Name +'"'
        problem = $recommendations | Select-Object @{l='Problem';e={$_.properties.shortDescription.problem}} | ForEach-Object {$_.Problem} |Out-String
        solution = $recommendations| Select-Object @{l='Solution';e={$_.properties.shortDescription.solution}}| ForEach-Object {$_.Solution} |Out-String
        impact = $recommendations | Select-Object @{l='Impact';e={$_.properties.impact}}| ForEach-Object {$_.Impact} |Out-String
    }
    $AllVmsInformation+=$VmInfo
}

$AllVmsInformation | Export-Csv -Encoding ascii -Path C:\Users\Public\Resource.csv -Force
#2

Graham,
Welcome to the forum. :wave:t4:

You have a typo in your PSCustomObject. It should be query - not $query

Regardless of that - just a tip - not a solution for your issue: Instead of using the += operator inside the loop it is better to collect the output of the loop in a variable like this:

$AllVmsInformation =
foreach ($vm in $allvms) {
    [PSCustomObject]@{
        Name              = $vm.Name
        ResourceGroupName = $vm.ResourceGroupName
        Location          = $vm.Location
        VMSize            = $vm.HardwareProfile.VmSize
        query            = 'advisorresources | where type == "microsoft.advisor/recommendations"  and properties.impactedField=="Microsoft.Compute/virtualMachines" and properties.impactedValue== "' + $vm.Name + '"'
        problem           = $recommendations | Select-Object @{l = 'Problem'; e = { $_.properties.shortDescription.problem } } | ForEach-Object { $_.Problem } | Out-String
        solution          = $recommendations | Select-Object @{l = 'Solution'; e = { $_.properties.shortDescription.solution } } | ForEach-Object { $_.Solution } | Out-String
        impact            = $recommendations | Select-Object @{l = 'Impact'; e = { $_.properties.impact } } | ForEach-Object { $_.Impact } | Out-String
    }
}
1 Like