Trying to script OS/Cluster report into single table.

[xavtrix]

Been pulling my hair out all week trying to figure this out.  I am trying to create a single table that shows the guest OS and counts for each cluster - similar to this...

OS	Total	Cluster-1	Cluster-2
Server 2012	22	15	7
RHEL 7	9	5	4
SUSE	1	0	1
TOTALS	32	20	12

Made individual scripts to report for individual clusters, but trying to combine into one table.  This would be easily achievable if there was something similar to Join-Object in PowerShell or join on in SQL, but not having any luck finding similar method in PowerCLI.  There has to be a way to do this! 

[LucD · Accepted solution]

Try something like this

$clusterName = 'Cluster1','Cluster2'

$report = @()

Get-Cluster -Name $clusterName -PipelineVariable cluster | Get-VM | Select @{N='Cluster';E={$cluster.Name}},@{N='OS';E={$_.Guest.OSFullName}} |

Group-Object -Property OS | %{

    $obj = [ordered]@{

        OS = $_.Name

        Total = $_.Group.Count

    }

    foreach($name in $clusterName){

        $vm = $_.Group | where{$_.Cluster -eq $name}

        $obj.Add($name,$vm.Count)

    }

    $report += (New-Object -TypeName PSObject -Property $obj)

}

$obj = [ordered]@{

    OS = 'Totals'

    Total = $report | Measure-Object -Property Total -Sum | select -ExpandProperty Sum

}

foreach($name in $clusterName){

    $obj.Add($name,($report | Measure-Object -Property $name -Sum | select -ExpandProperty Sum))

}

$report += (New-Object -TypeName PSObject -Property $obj)

$report

[xavtrix]

LucD, you are a saint - thank you!  Very much appreciate your help to others over the years, have learned a lot from you.

[xavtrix]

Only thing I'm working on now with this script is that if there is a count of 1, it shows blank result (though 0 does return a result of 0 as expected) and the totals column calculates as if it were there.  Interesting

OS	Total	Cluster-1	Cluster-2
Server 2008	1		0
Server 2012	15	15	0
RHEL 6	6	0	6
Totals	22	15	6

[LucD]

Are you by any chance using a pre-v3 PowerShell version?
Check the content of $PSVersionTable

Before PowerShell v3 the Count property of a scalar returned nothing.
Since PS v3, the Count returns 1.

The following should return 1 and 2

$t = 'A'

$t.Count

$t = 'A','B'

$t.Count

[xavtrix]

Hey, LucD.  See below.  Haven't had a chance to tshoot this week, hope to have some today or tomorrow and will shout back.

Name                           Value

----                           -----

PSVersion                      4.0

WSManStackVersion              3.0

SerializationVersion           1.1.0.1

CLRVersion                     4.0.30319.42000

BuildVersion                   6.3.9600.17400

PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0}

PSRemotingProtocolVersion      2.2

PowerCLI C:\workfile\vCheck> $t = 'A'

PowerCLI C:\workfile\vCheck> $t.Count

1

PowerCLI C:\workfile\vCheck> $t = 'A','B'

PowerCLI C:\workfile\vCheck> $t.Count

2

[xavtrix]

Hey LucD​

Was able to get the 1 count returning null issue resolved by making slight modification to this:

Original:

    foreach($name in $clusterName){

        $vm = $_.Group | where{$_.Cluster -eq $name}

        $obj.Add($name,$vm.Count)

Modified:

    foreach($name in $clusterName){

        $vm = $_.Group | where{$_.Cluster -eq $name}

        $obj.Add($name,($vm | Measure-Object).Count)

Again, MANY thanks for your help!!