Group By Commonalities

user_6258802 0
Viewed 45

Trying to figure out how to get common configurations (product installed and version) for a dataset of machines.

The data is structured like this:

Device Name     Product Name    Product Version     OS
Computer 1      Visio           16.0.11929          Windows 10
Computer 2      Visio           16.0.11929          Windows 10
Computer 3      Visio           16.0.11000          Windows 7
Computer 4      Visio           16.0.11929          Windows 7
Computer 5      Visio           16.0.11000          Windows 10
Computer 1      PowerBI         2.72.5556           Windows 10
Computer 2      PowerBI         2.72.5556           Windows 10
Computer 5      PowerBI         2.72.5556           Windows 10
Computer 1      WebEx           40.1.8.5            Windows 10
Computer 2      WebEx           40.1.8.5            Windows 10
Computer 3      WebEx           38.1.6.3            Windows 7
Computer 5      WebEx           40.1.8.5            Windows 10

Trying to get an output like this:

Count   OS          Product Name    Product Version
2       Windows 10  Visio           16.0.11929
                    PowerBI         2.72.5556
                    WebEx           40.1.8.5

1       Windows 7   Visio           16.0.11000
                    WebEx           38.1.6.3

1       Windows 7   Visio           16.0.11929

1       Windows 10  Visio           16.0.11000
                    PowerBI         2.72.5556
                    WebEx           40.1.8.5

I thought I could use something like this but, its not grouping the correct way:

group-object -property 'Product Name', 'Product Version', OS
powershell
1 Answers

Start by re-arranging your data so that all rows belonging to the same computer are grouped together:

$Computers = $profiles |Group-Object 'Device Name' |ForEach-Object {
  [pscustomobject]@{
    Name = $_.Name
    OS   = $_.Group[0].OS
    Software = $_.Group |ForEach-Object {
      "$($_.'Product Name') $($_.'Product Version')" 
    } |Sort-Object
  }
}

Now that the list of Software installed on each machine is a sorted list of strings, turning the list into a single string with -join will allow us to compare and group by that now, much closer to your intended output:

PS C:\> $Computers |Group-Object OS,{$_.Software -join ', '} -NoElement |Sort Count -Descending |Format-Table -AutoSize

Count Name
----- ----
    2 Windows 10, PowerBI 2.72.5556, Visio 16.0.11929, WebEx 40.1.8.5
    1 Windows 10, PowerBI 2.72.5556, Visio 16.0.11000, WebEx 40.1.8.5
    1 Windows 7, Visio 16.0.11000, WebEx 38.1.6.3
    1 Windows 7, Visio 16.0.11929
user_7126490
Related