Export array with "sub-array"

Question

I am currently trying to automate license counting in Office 365 across multiple partner tenants using PowerShell.

My current code (aquired from the internet) with some modifications gives me this output:

Column A     Column B     Column C
--------     --------     --------
CustA        LicA,LicB    1,3
CustB        LicA,LicB    7,3
CustC        LicA         4

But the output I want from this code is:

Column A     Column B     Column C
--------     --------     --------
CustA        LicA         1
             LicB         3
CustB        LicA         7
             LicB         3

Here is my current code which is exported using Export-Csv -NoType:

$tenantID = (Get-MsolPartnerContract).tenantid        

foreach($i in $tenantID){
    $tenantName = Get-MsolPartnerInformation -TenantId $i
    $tenantLicense = Get-MsolSubscription -TenantId $i
    $properties = [ordered]@{
        'Company' = ($tenantName.PartnerCompanyName -join ',')
        'License' = ($tenantLicense.SkuPartNumber -join ',')
        'LicenseCount' = ($tenantLicense.TotalLicenses  -join ',')
    }

    $obj = New-Object -TypeName psobject -Property $properties
    Write-Output $obj
}

I have tried this along with several other iterations of code which all fail catastophically:

$properties = [ordered]@{
    'Company' = ($tenantName.PartnerCompanyName -join ','),
    @{'License' = ($tenantLicense.SkuPartNumber -join ',')},
    @{'LicenseCount' = ($tenantLicense.TotalLicenses -join',')}
}

I was thinking about making a "sub-array" $tenantLicense.SkuPartnumber and $tenantLicense.TotalLicenses, but I'm not quite sure how to approach this with appending it to the object or "main-array".

Matt · Accepted Answer · 2017-04-11 11:50:21Z

1

A second loop for each $tenantLIcense should do the trick for you. I don't have access to an environment like yours so I cannot test this.

$tenantID | ForEach-Object{
    $tenantName = Get-MsolPartnerInformation -TenantId $_
    $tenantLicense = Get-MsolSubscription -TenantId $_

    # Make an object for each $tenantLicense
    $tenantLicense | ForEach-Object{
        $properties = [ordered]@{
            'Company' = $tenantName.PartnerCompanyName
            'License' = $_.SkuPartNumber
            'LicenseCount' = $_.TotalLicenses
        }

        # Send the new object down the pipe.
        New-Object -TypeName psobject -Property $properties
    }
}

Since you have multiple $tenantLicenses that have the same company name lets just loop over those and use the same company name in the output. Assuming this worked it would not have the same output as you desired since there no logic to omit company in subsequent rows. I would argue that is it better this way since you can sort the data now with out loss of data / understanding.

Notice I change foreach() to ForEach-Object. This makes it simpler to send object down the pipe.

edited Apr 11, 2017 at 11:50

answered Apr 11, 2017 at 11:44

Matt

47k9 gold badges90 silver badges125 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joakim Over a year ago

Thanks for the feedback, I was unaware of the ForEach-Object function and your solution really simplified my code, and was also a good answer and gave me a hint to what i need to read up on, my code from when i first posted this question untill you posted this had reached well above 50 lines of and three hours of trial and error.

Alex Sarafian · Accepted Answer · 2017-04-11 11:27:35Z

0

Without providing the code solution I can say that you need to build up the array. In terms of programming, you will need to iterate the array ARRAY1 and populate another one ARRAY2with the extra rows. For example if columns A,B are simple value and C is an array of 3 items, then you would add 3 rows in the new table with A,B,C1, A,B,C2 and A,B,C3. On each iteration of the loop you need to calculate all the permutations, for example in your case the ones generated by columnB and columnC.

This should be also possible with pipelining using the ForEach-Object cmdlet but that is more difficult and as you mentioned your relatively new relationship with powershell I would not pursuit this path, unless of coarse you want to learn.

answered Apr 11, 2017 at 11:27

Alex Sarafian

6746 silver badges17 bronze badges

3 Comments

Joakim Over a year ago

Hello, i have tried to make Arrays, This is how i've done it. But i don't get the expected output from it ? = Backtick

@lisensantall = @() foreach($LisensA in $tenantLicense.TotalLicenses) {  $Lisensantall += $LisensA }  $properties = [ordered]@{   'Company' = ($tenantName.PartnerCompanyName -join "?n")   'License' = ($Lisenser -join "?n")   'LicenseCount' = ($Lisensantall -join "?n") } $obj = New-Object -TypeName psobject -Property $properties

The return from this is like this Company License Licenesecount Name LicA.... 1.....

Joakim Over a year ago

While those arrays that hold only one license show normaly, those that containt several licenses just shows it as "..." instead of actually displaying the second license on a new line.

Alex Sarafian Over a year ago

I'm happy you found a solution by @Matt. To be clear, a ForEach-Object is the same as with any other loop. There are some basic differences that have to do with piped execution but that is advanced. For you requirements it would had been the same thing

Collectives™ on Stack Overflow

Export array with "sub-array"

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related