How to format the Out-GridView display using calculated property?

Question

The Powershell script below works great to show the Azure User properties, except the license column which needs to be formatted to remove the company domain name.

This is the script.

#Import Module
If (!(Get-Module "*MSOnline*")) {Import-Module MSOnline}
If (!(Get-Module "*Exchange*")) {Import-Module $((Get-ChildItem -Path $($env:LOCALAPPDATA + "\Apps\2.0\") -Filter Microsoft.Exchange.Management.ExoPowershellModule.dll -Recurse).FullName | ?{ $_ -notmatch "_none_" } | select -First 1)}

#Set admin UPN
$UPN = 'Global.Admin@domain.com'

#This connects to Azure Active Directory & Exchange Online
Connect-MsolService
$EXOSession = New-ExoPSSession -UserPrincipalName $UPN
Import-PSSession $EXOSession -DisableNameChecking -AllowClobber

$startsWith = @(
    'Test'
    'Sync_'
)

$endsWith = @(
    '365'
    '\$'
    'svc'
    'Sync'
    'user'
)

$pattern = '^({0})|({1})$' -f $($startsWith -join '|'), $($endsWith -join '|')

# Member Outputs for Microsoft.Online.Administration.User based on https://docs.microsoft.com/en-us/powershell/module/msonline/get-msoluser?view=azureadps-1.0
$allUsers = @()
$allUsers = Get-MsolUser -All -EnabledFilter EnabledOnly | Where-Object {
        ($_.UserPrincipalName -notmatch $pattern) -and
        ($_.UserPrincipalName -notlike '*#EXT#*') -and
        ($_.DisplayName -notmatch 'Admin|Calendar|Room|Prod|Account|Fax|Team|Office|Test|User')
} | Select-Object FirstName, 
    LastName, 
    UserPrincipalName,
    @{Label = 'SMTP Address(es)'; 
        Expression = { 
            If (( $_.UserPrincipalName -match 'onmicrosoft.com$')) {
              ($_.proxyAddresses | Where-Object { ($_ -like 'SMTP*') -and ($_ -like '*onmicrosoft.com') }) -replace 'smtp:' -join ';'
            } Else {
                ($_.proxyAddresses | Where-Object { ($_ -like 'SMTP*') -and ($_ -notlike '*onmicrosoft.com') }) -replace 'smtp:' -join ';'
            }
        } 
    },
    AlternateEmailAddresses, 
    UsageLocation, 
    isLicensed,
    Licenses,
    @{Label = 'License(s)'; 
        Expression = {
            ($_.Licenses | ForEach-Object { ($_.AccountSkuId | Where-Object { $_.AccountSkuId -like 'CorpFinanceLtd*' }) -replace 'CorpFinanceLtd:' } ) -join ';'
        }
    },
    PasswordNeverExpires, 
    BlockCredential

$allUsers | Out-GridView

I believe this section needs some rework, but not sure how to do that?

@{Label = 'License(s)'; 
    Expression = {
        ($_.Licenses | ForEach-Object { ($_.AccountSkuId | Where-Object { $_.AccountSkuId -like 'CorpFinanceLtd*' }) -replace 'CorpFinanceLtd:' } ) -join ';'
    }
},

The default Licenses column shows the license information like: {CorpFinanceLtd:MCOPSTNC, CorpFinanceLtd:ENTERPRISEPREMIUM, CorpFinanceLtd:EMSPREMIUM, CorpFinanceLtd:RIGHTSMANAGEMENT_ADHOC…}

So how do I remove the CorpFinanceLtd: prefix part, so it makes a more meaningful result?

You may share an example. How does it look now and what's the desired result? It doesn't need to be a screenshot - text is enough I think. — Olaf, Apr 30 '20 at 07:14

score 1 · Accepted Answer · answered Apr 30 '20 at 09:56

You pass the value of $_.AccountSkuId as pipeline input, but keep looking for an AccountSkuId property on the resulting object(s) - which won't work, because it's just a string.

You can simplify the expression as follows:

@{
    Label = 'License(s)'; 
    Expression = {
        ($_.Licenses | ForEach-Object { $_.AccountSkuId  -replace '^CorpFinanceLtd:' } ) -join ';'
    }
},

The ^ in front of CorpFinanceLtd: ensure the regex engine will only match at the start of the string, no need for the wildcard filter anymore

How to format the Out-GridView display using calculated property?

1 Answers1