6

I've got two CMDlets that return lists of objects. One returns objects of the type SPSolution, which contains the property Id, the other returns objects of the type SPFeature with a property SolutionId.

Now I want to join/merge this data something like this:

$f = Get-Feature
$s = Get-Solution
$result = <JOIN> $f $s
          <ON> $f.SolutionId = $s.Id
          <SELECT> FeatureName = $f.DisplayName, SolutionName = $s.Name
Hinek
  • 9,519
  • 12
  • 52
  • 74

5 Answers5

5

It's not efficient, and it assumes PowerShell 2 but it should do the job:

$solutions = Get-Solution

foreach ($f in Get-Feature) {

    $filteredSolutions = $solutions |
        where-object { $_.Id -eq $f.SolutionId }

    foreach ($s in $filteredSolutions) {
        new-object PSObject -prop @{
            FeatureName = $f.DisplayName
            SolutionName = $s.Name
        }
    }
}

Note that I don't have SharePoint installed so I'm afraid that I can't test this!

Damian Powell
  • 8,655
  • 7
  • 48
  • 58
  • 1
    Thank you, it did the job. But I was really hoping that there is a CMDlet for this kind of operation... – Hinek Apr 20 '10 at 11:52
  • 1
    Here's something very, very old (from when PowerShell was still known as Monad) which describes a Join-Object cmdlet. It's so old it uses a different syntax though: http://www.leeholmes.com/blog/CreatingSQLsJoinlikeFunctionalityInMSH.aspx – Damian Powell Apr 20 '10 at 12:11
  • 1
    There is no Join-Object cmdlet even in PowerShell 2.0. – Keith Hill Apr 20 '10 at 15:28
  • @Kieth Hill: the article describes a potential implementation, not something that is part of PowerShell. – Damian Powell Apr 20 '10 at 16:12
  • thanks for the article, I like the implementation. A pity that it only allows to return the first OR the second data source. I'm thinking about building my own Join-Object from there ... or starting a bounty for building it ;-) – Hinek Apr 23 '10 at 12:26
  • I don't know how code is working but it is working for me too thanks a bunch. – user206168 Sep 25 '14 at 14:58
  • "Join-Object" cmdlet link has been updated: http://www.leeholmes.com/blog/2005/06/22/creating-sqls-join-like-functionality-in-msh/ – Todd Walton Apr 18 '17 at 15:00
3

Building off what Keith Hill said Making it a 2 liner can greatly improve efficiency. This way you only run Get-Solution once instead of again for every object returned by Get-Feature

$Solutions = Get-Solution
Get-Feature | % {$f = $_; $Solutions | ? {$f.SolutionId -eq $_.Id} | 
                 Select Name,@{n='FeatureName';e={$f.DisplayName}}}
Chris Rudd
  • 709
  • 7
  • 13
2

Here's a one-liner that should do the trick (relies on nested pipelines):

Get-Feature | % {$f = $_; Get-Solution | ? {$f.SolutionId -eq $_.Id} | 
                 Select Name,@{n='FeatureName';e={$f.DisplayName}}}
Keith Hill
  • 194,368
  • 42
  • 353
  • 369
  • Nice. Didn't realise you could abbreviate 'Name' and 'Expression' to 'n' and 'e'. Works in PS1 too. Although, not hugely readable! ;) – Damian Powell Apr 20 '10 at 16:15
  • No, not hugely readable but if you're banging stuff out at the prompt, it is nice to be able to do. – Keith Hill Apr 21 '10 at 14:50
1

It's simple and could probably use more work, but it does the job.

function Join-Object {
  param ( [PSObject[]] $objects, $where, $proplist)
    for ($i=0;$i -le $objects.length;$i++) {
      if ($objects[$i+1] -ne $null) {$out += $objects[$i] | %{$o=$_;$objects[$i+1] | where $where | Select -property $proplist} } };
  $out
}

$where is a script block, and $proplist is an array of properties formatted for Select-Object.
It works for passing in two objects. Hope it would work for more, but haven't tried it yet.

Chandra Sekhar
  • 16,256
  • 10
  • 67
  • 90
Chris Kennedy
  • 11
  • 1
0
(Get-Feature | Select @{Name="FeatureName";Expression={$_.DisplayName}) | Join (Get-Solution | Select @{Name="SolutionName";Expression={$_.Name}) SolutionId -eq Id

See: In Powershell, what's the best way to join two tables into one?

iRon
  • 20,463
  • 10
  • 53
  • 79