Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report

Question

I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It is only printing the output of first commands. Can someone please help me with joining the result of all three variables? What I'm trying to do is writing a script to automate generating our SQL Server Inventory and Capacity assessment report.

Here's my code so far:

$Servers = Get-Content 'C:\Users\temp\ServerList.txt'
$reportData = @()
foreach ($Server in $Servers) {
 if (Test-Connection -ComputerName $Server -Count 1 -ErrorAction 'SilentlyContinue') {
 $OSInfo=Get-DbaOperatingSystem -ComputerName MyServer| Select ComputerName, OSVersion, Version, Architecture
 $DiskSPace=Get-DbaDiskSpace -ComputerName MyServer| Select ComputerName, Label, Name, Capacity, Free
 $SQLInstanceInfo=Connect-DbaInstance -SqlInstance MyServer | Select DbaInstanceName, Edition, NetPort, IsClustered, Processors, ProductLevel, ServiceName, 
 $reportData +=$OSInfo
 $reportData +=$DiskSPace
 $reportData +=$SQLInstanceInfo
 }
 else {
 write-host $Server "not connected"
 }
}
Write-Output $reportData | Format-Table

Answer 1

TL;DR

Create a PSCustomObject with the properties of your variables.

---

PowerShells output formatter uses the first object in the output set as a template for displaying subsequent objects in table format.

If the first object has properties A, B, and C those will be the table headers.
If the second object has only properties D, E, and F then a blank line will be shown.

You can see it for yourself with this example:

$TestResults = @()
$Test1 = [PSCustomObject]@{
 Name = 'Test1'
 Type = 'PSCustomObject'
}
$Test2 = [PSCustomObject]@{
 Name = 'Test2'
 Type = 'PSCustomObject'
}
$Test3 = [PSCustomObject]@{
 Label = 'Test3' # <-- This is "Label", not "Name".
 Type = 'PSCustomObject'
}
$TestResults += $Test1
$TestResults += $Test2
$TestResults += $Test3
# Show output:
$TestResults

Results of the $TestResults variable

---

Option 1 - Calculated Properties (Wouldn't recommend in your scenario):

So, you could rename the properties in your variables to have the same name using calculated properties?

$Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type

Renaming the "Label" property to "Name"

Then when you add this to your array, all the values should show:

$TestResult_02 = @()
$TestResult_02 += $Test1
$TestResult_02 += $Test2
$TestResult_02 += $Test3 | Select-Object @{ Name = 'Name'; Expression = { $_.Label }}, Type
$TestResult_02

Calculated Properties shows all the values

Why not this method for your scenario?

All your properties mean separate things. The Architecture property of $OSInfo doesn't match up with any property of $DiskSpace for example. Using calculated properties here would likely end up confusing everyone in time.

Option 2 - Creating a PSCustomObject (Would recommend)

It's easy to build up a PSCustomObject type, especially if you know the output of commands or variables in advance as you do in your scenario.

$ResultObject = [PSCustomObject]@{
 Test1_Name = $Test1.Name
 Test1_Type = $Test1.Type
 Test2_Name = $Test2.Name
 Test2_Type = $Test2.Type
 Test3_Name = $Test3.Label
 Test3_Type = $Test3.Type
}
$ResultObject

Using PSCustomObject to show all results

Using your examples with the information on my machine, we can create the following PSCustomObject

$FullResults = [PSCustomObject]@{
 ComputerName = $OSInfo.ComputerName
 OSVersion = $OSInfo.OSVersion
 Version = $OSInfo.Version
 Architecture = $OSInfo.Architecture
 InstanceName = $SQLInstanceInfo.DbaInstanceName
 Edition = $SQLInstanceInfo.Edition
 NetPort = $SQLInstanceInfo.NetPort
 IsClustered = $SQLInstanceInfo.IsClustered
 Processors = $SQLInstanceInfo.Processors
 ProductLevel = $SQLInstanceInfo.ProductLevel
 ServiceName = $SQLInstanceInfo.ServiceName
 Disks = $DiskSPace
}
$FullResults

Full results of creating a PSCustomObject

Answer 2

Assuming $Servers is a list of the SQL Server instances, rather than a list of Windows servers, the following will return all the information in the $Results object which can then be written out however you may need.

$Results = $Servers |
 ForEach {
 $i = Connect-DbaInstance -SqlInstance $_ -TrustServerCertificate
 $d = Get-DbaDiskSpace (Connect-DbaInstance -SqlInstance $_ -TrustServerCertificate)
 $o = Get-DbaOperatingSystem (Connect-DbaInstance -SqlInstance $_ -TrustServerCertificate)
 $properties = @{
 ComputerName = $i.ComputerName
 OSVersion = $o.OSVersion
 OSVersionNo = $o.Version
 Architecture = $o.Architecture
 InstanceName = $i.Name
 SQLEdition = $i.Edition
 SQLNetPort = $_.NetPort
 SQLIsClustered = $i.IsClustered
 SQLProcessors = $i.Processors
 SQLProductLevel = $i.ProductLevel
 SQLServiceName = $i.ServiceName
 Disks = $d
 }
 New-Object -TypeName PSObject -Property $Properties
 }

The Connect-DbaInstance -SqlInstance $_ -TrustServerCertificate sections are used here to avoid getting the error message: WARNING: ... Failure | The certificate chain was issued by an authority that is not trusted. May not be needed in your environment.

Should also note that this query will duplicate a lot of the server information if you have any multi instance servers set up.