PowerShell - How to add the properties of two custom objects together to create a single merged custom object using

Question

I am using some PowerShell functions to get data from some REST API endpoint. Iterating through the results of the first query allows me to use an id field to retrieve matching results in the second query, however, the returned results of the second query do not have any properties that would match the original set of results, so I don't think I could perform a JOIN or something like that.

For example, assume the results of querying the first REST endpoint are stored in $a.

$a = 
ID  Title                  Location  Internal IP Address
--  -----                  --------  -------------------
1   Endpoint 1             UK        10.0.0.1
2   Endpoint 2             US        10.1.0.1
3   Endpoint 3             AUS       10.2.0.1

And using ID = 1 to query the second REST endpoint, we get the following stored in $b:

$b = 
NATed IP Address Last Provisioned Time
---------------- ---------------------
1.2.3.4          2022-02-10T04:09:31.988909+01:00

What I could like to do is combine the properties of both, one record at a time, so after I have retrieved $b I would create $c, which would look something like this:

$c =
ID  Title       Location  Internal IP Address NATed IP Address Last Provisioned Time
--  -----       --------  ------------------- ---------------- ---------------------
1   Endpoint 1  UK        10.0.0.1            1.2.3.4          2022-02-10T04:09:31.988909+01:00

As the code then iterates through the results of $a, so I should build up a full merged object and after three iterations of $a, we should end up with something like:

$d =
ID  Title       Location  Internal IP Address NATed IP Address Last Provisioned Time
--  -----       --------  ------------------- ---------------- ---------------------
1   Endpoint 1  UK        10.0.0.1            1.2.3.4          2022-02-10T04:09:31.988909+01:00
2   Endpoint 2  US        10.1.0.1            1.2.3.5          2022-02-10T04:09:31.988909+01:00
3   Endpoint 3  AUS       10.2.0.1            1.2.3.6          2022-02-10T04:09:31.988909+01:00

It feels to me that this would be reasonably simple, however, I have looked at many different posts and sites, but none seem to help me achieve what I want, or more probably I am simply misunderstanding how methods such as Add-Member might function within multiple piped loops, or missing the blindly obvious.

The closest I have come is the following code, which actually results in an Array or Hashtable (depending on how I initialise the $collection variable), but the results are similar:

$collection = @{}

foreach ($item in $a) {
    $collection += @{
        "Title" = $item.Title
        "Location" = $item.Location
        "Internal IP Address" = $item."Internal IP Address"
    }
}

foreach ($item in $b) {
    $collection += @{
        "NATed IP Address" = $item."NATed IP Address"
        "Last Provisioned Time" = $item."Last Provisioned Time"
    }
}

Which results in a key/value table such as:

Name                           Value
----                           -----
ID                             1
Title                          Endpoint 1
Internal IP Address            10.0.0.1 
Location                       UK
NATed IP Address               1.2.3.4
Last Provisioned Time          2022-02-10T03:21:29.265257+01:00

What this means is that I will end up with an array of arrays (or a hashtable of hashtables). While I think I could still work with this type of data format, I would be intrigued to understand what I need to do to achieve my initial goal.

UPDATE

The code to get $a and $b is as follows. There are some functions that wrap the API calls:

$a = Get-Endpoints -Creds $Credentials -URI $FQDN

$a| ForEach-Object {
    $b = Get-ProvisioningInfoEndpoint -Creds $Credentials -URI $FQDN -id $_.id |
        Select-Object -Property @{Name="NATed IP Address";Expression={$_.ip}}, @{Name="Last Provisioned Time";Expression={$_.ts_created}}
}

UPDATE 2

Here is the full code, which works but is kinda long winded and probably really inefficient, and results in a hashtable of hashtables:

$a = Get-Endpoints -Creds $Credentials -URI $FQDN

$a| ForEach-Object {
    $b = Get-ProvisioningInfoEndpoint -Creds $Credentials -URI $FQDN -id $_.id |
        Select-Object -Property @{Name="NATed IP Address";Expression={$_.ip}}, @{Name="Last Provisioned Time";Expression={$_.ts_created}}

    $EndpointSelectedData = $_ | Select-Object -Property ID, @{Name="Title";Expression={$_.title}}, @{Name="Location";Expression={$_.location}}, @{Name="Internal IP Address";Expression={$_.ip}}

    $c = @{}
    foreach ($EndpointData in $EndpointSelectedData) {
        $c += @{
            "ID" = $EndpointData.id
            "Title" = $EndpointData.Title
            "Location" = $EndpointData.Location
            "Internal IP Address" = $EndpointData."Internal IP Address"
        }
    }

    foreach ($ProvisionedD in $ProvisionedData) {
        $collection += @{
            "NATed IP Address" = $ProvisionedD."NATed IP Address"
            "Last Provisioned Time" = $ProvisionedD."Last Provisioned Time"
        }
    }

    $d = $d + @{"Node_$i" = $c}
    $i = $i + 1
}

UPDATE 3

After a suggested answer from @Santiago, and a pointer from @iRon, I now have the following code:

$Endpoints = Get-Endpoints -Creds $Credentials -URI $FQDN

foreach($i in $Endpoints) {
    foreach($z in Get-ProvisioningInfoEndpoint -Creds $Credentials -URI $FQDN -Id $i.id) {
        [pscustomobject]@{
            'ID'                    = $i.Id
            'Title'                 = $i.Title
            'Location'              = $i.Location
            'Internal IP Address'   = $i.Ip
            'NATed IP Address'      = $z.Ip
            'Last Provisioned Time' = $z.Ts_Created
        }
    }
}

This results in a dump to the screen (which I guess is the pipeline clearing) of:

ID                    : 1
Title                 : Endpoint 1
Location              : UK
Internal IP Address   : 10.0.0.1
NATed IP Address      : 1.2.3.4 
Last Provisioned Time : 2022-02-10T04:09:32.126357+01:00

ID                    : 2
Title                 : Endpoint 2
Location              : US
Internal IP Address   : 10.1.0.1
NATed IP Address      : 1.2.3.5
Last Provisioned Time : 2022-02-10T04:21:32.657364+01:00

ID                    : 3
Title                 : Endpoint 3
Location              : Aus
Internal IP Address   : 10.2.0.1
NATed IP Address      : 1.2.3.6
Last Provisioned Time : 2022-02-10T04:09:31.990202+01:00

...

So, I guess this is getting close to what I want, but still not really there. I now need to figure out how to handle the pipeline output... This is as much to do with a mindset change, so it might take some head scratching.

Update 4 - Possible solution

OK, so extending the chunk of code above, I have created some additional logic to pipe the output of the pscustomobject to a CSV file. The ultimate intention is to check the status of the various endpoints against their configuration from the previous day to see if anything had changed (as it does and often without the admin's knowledge!).

A variable is assigned to store the result of the nested foreach loops. This is then used to export to a CSV. I added some further logic at the beginning of the script to check to see if the file exists, and delete today's configuration file if it did, just so the script would create a fresh configuration file if it was run more than once a day by accident.

Once the main loops are complete, today's and yesterday's files are imported back in and the Compare-Object CmdLet is used to see if there are any changes. I will probably add some send email code if there is a diff between the two files.

$DateStamp = get-date -Format FileDate
if (Test-Path -Path ".\endpoint-$($DateStamp).csv" -PathType Leaf) {
    Remove-Item -Path ".\endpoint-$($DateStamp).csv"
}
$Endpoints = Get-Endpoints -Creds $Credentials -ERMMgr $MgrFQDN

$result = foreach($i in $Endpoints) {
    foreach($z in Get-ProvisioningInfoEndpoint -Creds $Credentials -URI $FQDN -Id $i.id) {
        [pscustomobject]@{
            'ID'                    = $i.Id
            'Title'                 = $i.Title
            'Location'              = $i.Location
            'Internal IP Address'   = $i.Ip
            'NATed IP Address'      = $z.Ip
            'Last Provisioned Time' = $z.Ts_Created
        }
    }
}

$result | Export-Csv ".\endpoint-$DateStamp.csv"

if (Test-Path -Path ".\endpoint-$($DateStamp-1).csv" -PathType Leaf) {
    $InFile2 = Import-CSV -Path ".\endpoint-$($DateStamp-1).csv"
    Compare-Object -ReferenceObject $result -DifferenceObject $InFile2 -Property Title, "NATed IP Address" | Export-Csv ".\endpoint_diff-$DateStamp.csv"
}

share the code to query both `$a` and `$b` this can be done at runtime instead of collecting everything and then merging them. — Santiago Squarzon, Jun 18 '22 at 01:20
[Try to avoid using the increase assignment operator (`+=`) to create a collection](https://stackoverflow.com/q/60708578/1701026) as it is exponential expensive. — iRon, Jun 18 '22 at 06:01
@iRon Quick test that confirms it for hashtables: `$ht1=@{x=42}; $ht2=$ht1; $ht1+=@{y=23}; $ht1 -eq $ht2` outputs `$false`, which means the references are no longer equal, i. e. `$ht1` has been recreated. I assumed that PowerShell would merge hashtables more intelligently. :-( — zett42, Jun 18 '22 at 08:51
@zett42, Yes, I just did a performance test and can confirm that for **1,000** iterations, simply `$HashTable[$_] = "Name$_"` is about **4** times faster as `$HashTable += @{ $_ = "Name$_" }`. For **10,000** iterations this is a factor **95**! — iRon, Jun 18 '22 at 09:17
Thanks, @iRon. My biggest issue when I come to write any of this stuff is usually, the last time I wrote any of this stuff was 6-12 months ago and my ageing brain has forgotten anything I might have learned. I will try to remember this one through. — Swinster, Jun 18 '22 at 09:28
I have further updated the question, including a modified version of the proposed answer from @Santiago Squarzon and the pointer from iRon. Like with most things, it now leads to other head scratchings :). — Swinster, Jun 18 '22 at 11:45

Santiago Squarzon · Answer 1 · 2022-06-18T13:13:06.400

1

By the looks of it, this might get you what you're looking for:

$result = foreach($i in Get-Endpoints -Creds $Credentials -URI $FQDN) {
    foreach($z in Get-ProvisioningInfoEndpoint -Creds $Credentials -URI $FQDN -Id $id.id) {
        [pscustomobject]@{
            'ID'                    = $i.Id
            'Title'                 = $i.Title
            'Location'              = $i.Location
            'Internal IP Address'   = $i.Ip
            'NATed IP Address'      = $z.Ip
            'Last Provisioned Time' = $z.Ts_Created
        }
    }
}

Basically the object can be created at runtime instead of collecting both results $a and $b to then join / merge them.

edited Jun 18 '22 at 13:13

answered Jun 18 '22 at 01:44

Santiago Squarzon

41,465
5
14
37

a couple of issues here. In the initial `Foreach` loop, for some reason, `$i` gets assigned as the entire array, not an element within the returned array. This I fixed by simply pre-assigning a variable to the result, so `$EndPoints = Get-Endpoints -Creds $Credentials -URI $FQDN)`, then `foreach($i in $EndPoints )`. FWIW, running `$EndPoints.GetType()` returns that is is a `System.Array`. `$z` is also assigned as the entire result not an element, however, `$z.GetType()` is `System.Object` (PSCustomObject). I don't think I need to look through this and can just assign directly. – Swinster Jun 18 '22 at 10:17
I also used a variable to capture the new `pscustomobject`, so something like, `$EndpointCombinedData = [pscustomobject]@{....` This works fine although now every iteration of $i overwrites the previous value, so I still need to add new records. – Swinster Jun 18 '22 at 10:29
Hmm, I think I am misunderstanding what is supposed to be happening here based on the link from @iRon above. I need to understand what should be occurring and how I can access and see the results when watching the code run. – Swinster Jun 18 '22 at 11:04
RIght, I screwed up and one of my assignments was incorrect :| I have now got an output, so I have to figure out how to deal with that without using a variable. I will update the original question with your modified code @Santiago Squarzon – Swinster Jun 18 '22 at 11:23
Just assign a variable to the beginning of the outer loop to capture all output. See the output. @Swinster – Santiago Squarzon Jun 18 '22 at 13:14
@Swinster what would you like to do with the output? Export to Csv or just hold it in memory? If, hold it in memory, my update should suffice – Santiago Squarzon Jun 18 '22 at 14:11
Hey @Santiago Squarzon, the code in your suggested answer doesn't quite work (see my comments above), however, I have made the necessary modifications and got things functional (see **Update 3** in the original question). I have also added some new code to pipe the output to a CSV and I think I have a workable solution - I will add this as a **Potential Solution** to the question. The intention would be that this script would run once a day, and the produced CSV would be compared with the CSV from the previous day to see if anything had changed. I'm not sure how good this is, but it's a start. – Swinster Jun 18 '22 at 17:27
@Swinster you're really over complicating the solution instead of following the code I have posted. By assigning `$result =` before the outer loop you're already capturing all output, then you can simply do `$result | Export....` after that – Santiago Squarzon Jun 18 '22 at 17:54
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/245722/discussion-between-swinster-and-santiago-squarzon). – Swinster Jun 18 '22 at 20:16

PowerShell - How to add the properties of two custom objects together to create a single merged custom object using

1 Answers1