Export-CSV output all values in one row

Question

When I run my script it outputs the Problem,Solution, and Impact values all in one row each instead of seperate rows. I would like to have Name,Location,RG, and VMSize to match with it's respectable Problem, Solution and Impact rows. Any ideas?

![$vms= Get-AzVM   
  
$outputfinal = @()  
 foreach ($vms in $allvms){  
 $outputtemp = "" | Select-Object  Name, Location, ResourceGroup, VMSize, Problem , Solution , Impact , Compliance  
 $outputtemp.Name = $vms.Name  
 $outputtemp.Location = $vms.Location  
 $outputtemp.ResourceGroup = $vms.ResourceGroupName  
 $outputtemp.VMSize = $vms.hardwareProfile.VmSize  
 $recommendations=(Search-AzGraph -Query $query)  
 $query ='advisorresources | where type == "microsoft.advisor/recommendations"  and properties.impactedField=="Microsoft.Compute/virtualMachines" and properties.impactedValue== "'+ $vms.Name +'"'  
 $outputtemp.problem= $recommendations | Select-Object @{l='Problem';e={$_.properties.shortDescription.problem}} | ForEach-Object {$_.Problem} |Out-String  
 $outputtemp.solution= $recommendations | Select-Object @{l='Solution';e={$_.properties.shortDescription.solution}}| ForEach-Object {$_.Solution} |Out-String  
 $outputtemp.impact= $recommendations | Select-Object @{l='Impact';e={$_.properties.impact}}| ForEach-Object {$_.Impact} |Out-String  
 if($solution){$compliance="No"}else{$compliance="Yes"}  
 $outputfinal += $outputtemp  
        }  
   
  
$outputfinal | Format-Table  
$outputfinal | Export-Csv C:\users\Public\VMsExport_"$((Get-Date).ToString("yyyyMMdd_HHmmss")).csv" -NoTypeInformation][1]  

Answer 1

You reference the $query variable on line 10 before you define it on line 11.
Your compliance column is empty because you set $compliance instead of $outputtemp.compliance. Not that it matters because you test $solution and that variable does not exist.

If you want 3 rows, you need to generate 3 objects. The main question that you need to resolve is what columns you want in the output.

 $outputtemp = "" | Select-Object  Name, Location, ResourceGroup, VMSize, Problem , Solution , Impact , Compliance 

Here you define the Problem, Solution and Impact columns in each row. So for a "Problem row", what do want in in the Solution and Impact columns? Blanks?

  $outputtemp = "" | Select-Object  Name, Location, ResourceGroup, VMSize, Problem , Solution , Impact , Compliance
  $outputtemp.Name = $vms.Name
  $outputtemp.Location = $vms.Location
  $outputtemp.ResourceGroup = $vms.ResourceGroupName
  $outputtemp.VMSize = $vms.hardwareProfile.VmSize
  $query = 'advisorresources | where type == "microsoft.advisor/recommendations"  and properties.impactedField=="Microsoft.Compute/virtualMachines" and properties.impactedValue== "'+ $vms.Name +'"'
  $recommendations = (Search-AzGraph -Query $query)
  $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
  if($solution){$outputtemp.compliance="No"}else{$outputtemp.compliance="Yes"}
  $outputtemp.Problem = ""
  $outputtemp.Solution = ""
  $outputtemp.Impact = ""

  # The base values for all columns have been set. Now clone $outputtemp
  $outputtemp2 =  $outputtemp.psobject.copy()
  $outputtemp3 =  $outputtemp.psobject.copy()

  # Generate the problem row
  $outputtemp.Problem = $problem
  $outputfinal += $outputtemp

  # Generate the solution row
  $outputtemp2.solution = $solution
  $outputfinal += $outputtemp2

  # Generate the impact row
  $outputtemp3.Impact = $impact
  $outputfinal += $outputtemp3

I don't know how you intend to process the data, but I think that if it were me, I would just leave it as one row and 3 columns.

Another option would be to define type and value columns.

$outputtemp = "" | Select-Object  Name, Location, ResourceGroup, VMSize, RowType, Description , Compliance 

Here you would populate the Rowtype and Description for a problem row. Do the same for the other 2 rows.

$outputtemp.RowType = "Problem"
 $outputtemp.Description = $Problem