0

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
Improve this question

2 Answers 2

Reset to default
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

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.