powershellpropertiespsobjectexport-csv

Powershell referencing property within a property and exporting to CSV


From my RMM tool and using a module written by an awesome dude called HomoTechsual ( NinjaOne ), I can get a list of organisations, which have various properties:

name             : Test Organisation 1
nodeApprovalMode : AUTOMATIC
id               : 123
locations        : {@{name=Test Location 1; address=Test address 1; id=401}, {name=Test Location 2; address=Test address 2; id=402}}
policies         : {@{nodeRoleId=31; policyId=30}, @{nodeRoleId=21; policyId=28}, @{nodeRoleId=116; policyId=24}, @{nodeRoleId=114; policyId=22}…}
settings         : @{trayicon=; splashtop=; teamviewer=; psa=}

The goal is to find the location IDs for a specific list of orgs and write the org name, org id, location name, and location id, but in many cases the orgs will have multiple locations, in which case I need to repeat the org-specific information to my output. Ideally this ends up being exportable to CSV.

At the moment I am running this code to filter out the unwanted information, but from here, because 'locations' is a property that also contains an 'id' (which I am trying to collect) I am not sure how to reference the property within a property (which obviously is problematic as it has the same name as the org id - they're both 'id')

[pscustomobject]$NewOrgs = Get-NinjaOneOrganisations -detailed | where-object { $ItemsToImport.OrgName -contains $_.name } 
$NewOrgs | Select-Object name,id,locations

The commands above generate a list that looks like this:

name                               id  locations
----                               --  ---------
Test Organisation 1                210 {@{name=Test Location 1; address=Test address 1; id=401...
Test Organisation 2                211 {@{name=Test Location 5; address=Test address 5; id=405}}

As you can see from the jump in location numbers, Test Org 1 has multiple locations in that have unique properties. The end goal would be to spread them each out, line by line, and repeat the org information in the left column so that it ends up looking like this:

name                               id  locations
----                               --  ---------
Test Organisation 1                210 {@{name=Test Location 1; address=Test address 1; id=401}}
Test Organisation 1                210 {@{name=Test Location 2; address=Test address 2; id=402}}
Test Organisation 1                210 {@{name=Test Location 3; address=Test address 3; id=403}}
Test Organisation 1                210 {@{name=Test Location 4; address=Test address 4; id=404}}
Test Organisation 2                211 {@{name=Test Location 5; address=Test address 5; id=405}}

And then from there I want to export all of this, and spread out the multiple locations properties into their own respective columns, specifically the names and ids.

I have tried looping over each entry in my $NewOrgs variable but I am not sure how to call a property within a property, I can do $NewOrgs.locations.id, but that just generates a full list of every location id. Perhaps I need to compare it to something?

I'm also struggling with how to repeat the org names as I described above. Some sort of comparison I imagine but my Powershell skills are lacking.


Solution

  • It might just require an inner loop expanding / creating a new object per value in the .locations property:

    Get-NinjaOneOrganisations -Detailed | ForEach-Object {
        if ($ItemsToImport.OrgName -contains $_.name) {
            foreach ($location in $_.locations) {
                [pscustomobject]@{
                    name            = $_.name
                    id              = $_.id
                    locationName    = $location.name
                    locationAddress = $location.address
                    locationId      = $location.id
                }
            }
        }
    }
    

    If this doesn't help it can help if you can provide a Json representation of Get-NinjaOneOrganisations -Detailed that we can work with:

    Get-NinjaOneOrganisations -Detailed | ConvertTo-Json -Depth 5