Create Union of Multiple Tables in Powershell

Question

I'm trying to create a union of multiple tables in Powershell to output in a user-friendly format as a report, similar to a UNION query in SQL.

I have the following code:

$ft = @{auto=$true; Property=@("MachineName", "Status", "Name", "DisplayName")}
$hosts = @("svr001", "svr002")
$report = @()
ForEach ($h in $hosts) {
    $results = Get-Service -CN $h -Name MyService
    $report += $results | Format-Table @ft
    # Other things occur here, which is why I'm creating $report for output later.
}
Write-Output $report

The output of this code is as follows:

MachineName  Status Name       DisplayName
-----------  ------ ----       -----------
svr001      Running MyService  MyServiceDisplayName



MachineName  Status Name       DisplayName
-----------  ------ ----       -----------
svr002      Running MyService  MyServiceDisplayName

Since you simply add arrays to do a union in Powershell (i.e., $union = @(0, 1, 2) + @(3, 4, 5)), my initial thought was that I should get the following output:

MachineName  Status Name       DisplayName
-----------  ------ ----       -----------
svr001      Running MyService  MyServiceDisplayName
svr002      Running MyService  MyServiceDisplayName

In retrospect, I think I understand why I do not get this output, but I'm unclear how to create a union of the two tables from the first output example into a single table as in the second, and I haven't been able to locate anything in the docs or examples online that would send me in the right direction.

Move | Format-Table ....... to the last line after write-output $report. Never save output from format-* cmdlets. — Frode F.
– Frode F., Commented May 17, 2016 at 18:37
For the record, You can pass multiple computer names to Get-Service... The ForEach loop isn't needed for that part of your code. — Tim Ferrill
– Tim Ferrill, Commented May 17, 2016 at 18:59
While I can see the point about not saving the output from Format-* cmdlets, doing that generates the following output: System.ServiceProcess.ServiceController System.ServiceProcess.ServiceController System.ServiceProcess.ServiceControllerSystem.ServiceProcess.ServiceController System.ServiceProcess.ServiceController System.ServiceProcess.ServiceController. — Deacon
– Deacon, Commented May 17, 2016 at 19:00
@TimFerrill--I had tried that, and it only output results back for the first server, so I didn't think that it worked. — Deacon
– Deacon, Commented May 17, 2016 at 19:04

Frode F. · Accepted Answer · 2016-05-17 19:26:19Z

Move the Format-Table to the last command. Format-* cmdlets create special format-objects that you can't work with manually so theres no point in saving them. When you save the result of Format-* to an array, you're saving the "report" which is why you get two tables in the output (array consists of two reports).

Collect the data first, then use Format-Table when you want to display the results.

$ft = @{auto=$true; Property=@("MachineName", "Status", "Name", "DisplayName")}
$hosts = @("svr001", "svr002")
$report = @()
ForEach ($h in $hosts) {
    $results = Get-Service -ComputerName $h -Name MyService
    $report += $results
    # Other things occur here, which is why I'm creating $report for output later.
}

#Write-Output is not necessary as it is default behaviour
$report | Format-Table @ft

Sample output (used wuauserv as servicename):

MachineName  Status Name     DisplayName   
-----------  ------ ----     -----------   
localhost   Stopped wuauserv Windows Update
frode-pc    Stopped wuauserv Windows Update

Tim Ferrill · Accepted Answer · 2016-05-17 19:28:06Z

0

The Get-Service Cmdlet also supports an array of strings for the -ComputerName parameter. This works for me:

Get-Service -CN $hosts -Name MyService | Format-Table @ft

Sample Output using wuauserv:

MachineName  Status Name     DisplayName   
-----------  ------ ----     -----------   
Tim-SRV1    Running wuauserv Windows Update
Tim-SRV2    Stopped wuauserv Windows Update

answered May 17, 2016 at 19:28

Tim Ferrill

1,6941 gold badge12 silver badges16 bronze badges

3 Comments

Frode F. Over a year ago

This doesn't answer the question and belongs as a comment. His sample says that there are more commands in the "foreach host"-loop, so there's no guarantee you could remove the foreach-loop.

Deacon Over a year ago

In my test of this, however, doing Get-Service -CN $hosts -Name MyService | Format-Table @ft only gets the service for the first server in the list. I'm using Powershell 2.0, if that matters, and Powershell version is not something I have control over in this environment.

Frode F. Over a year ago

Yes, in PowerShell 2.0 you only receive the first (at least when testing with powershell -version 2) on a PS 5.0 computer. Don't have a Win7-comp with 2.0 documentation to verify if it's a bug or if array-support is missing. On PS 3+ it should work fine with array-input

Collectives™ on Stack Overflow

Create Union of Multiple Tables in Powershell

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related