Get-MsolUser primary SMTP and other info

[SauPom]

I need to get primary SMTP address and also other info like country etc.

I run this command, but I don´t know how to add other info after Select.

Get-MsolUser -All | Select -Expand Proxyaddresses | ? {$_ -cmatch '^SMTP\:.*'} | Out-GridView

With this command I´ll only get the email address of user and nothing else

[#2]

@SauPom 

There's a couple of different topics going on here.

First, I'd encourage you to get off the MSOnline module and onto the Microsoft.Graph.* series of modules wherever possible. You can read more about why in the following (re-)announcement:

• Important: Azure AD Graph Retirement and Powershell Module Deprecation - Microsoft Community Hub

That said, the initial question doesn't relate to MSOnline - or any other module, as it's purely a question of the impact of using -ExpandProperty, which is best demonstrated with an example.

The first example does not use -ExpandProperty and therefore shows the requested properties.

In the second example, I've added -ExpandProperty and now all I see - as you already noted in your original post - is an expansion of proxyAddresses, with the other attributes nowhere to be found.

This is expected behaviour and is well-described in the documentation:

• Select-Object (Microsoft.PowerShell.Utility) - PowerShell | Microsoft Learn

So, in short, the properties are there but you can't easily see them, and this is reflected in your Out-GridView.

You can work around the standardised formatting as illustrated below, but it feels like this part of the discussion is unnecessary, so after pasting in the example (I have bothered including the SMTP filtering), I'm going to drop this part of the conversation.

Example

Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property * |
    Select-Object -Property id, userPrincipalName, proxyAddresses -ExpandProperty proxyAddresses |
        Select-Object -Property id, userPrincipalName, @{ n="proxyAddress"; e={ $_ } };

Output

Since the primary SMTP address is already reflected in the "Mail" attribute, why don't you simply pull that and dispense with the case-sensitive matching against proxyAddresses altogether?

Taking this approach gets you back to easily being able to then add additional attributes to pull such as "Country", etc.

Example

Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property * |
    Select-Object -Property id, userPrincipalName, country, mail;

Output

This would reduce your command to the following:

Get-MgBetaUser -UserId "email address removed for privacy reasons" -Property * |
    Select-Object -Property id, userPrincipalName, country, mail |
        Out-GridView;

Cheers,

Lain

[#3]

Thank you for your reply. Actually that last works really well other than I also need to add to script licenses property and I cannot find that from Get-MgBetaUser. Any advice on that?

It´s like I need to get all the users with E3 license sorted by country and primary SMTP.

[#4]

@SauPom 

It can be done but it's not "easy" - particularly if you want the resulting data to mean something to the people reading the final report.

Here's an example script that illustrates one approach.

Note: A user can - and almost always will - have more than one product licenced to them, hence productNames is an array.

Example

# Import the Microsoft CSV containing the translations. I'm going to use a Dictionary for storage as it's faster on lookups.
$MsProducts = [System.Collections.Generic.Dictionary[[guid],[string]]]::new();

Import-Csv -Path "D:\Data\ms-products-and-plans.csv" |
    ForEach-Object {
        if (-not $MsProducts.ContainsKey($_.GUID))
        {
            $MsProducts.Add($_.GUID, $_.Product_Display_Name);
        }
    }

# Pull the list of users from Graph and output just the data we're interested in.
Get-MgBetaUser -All -Property * |
    ForEach-Object {
        [PSCustomObject] @{
            id = $_.id;
            userPrincipalName = $_.userPrincipalName;
            country = $_.country;
            mail = $_.mail;
            productNames = $_.AssignedLicenses |
                ForEach-Object {
                    if ($null -ne ($Name = $MsProducts[$_.SkuId]))
                    {
                        $Name;
                    }
                    else
                    {
                        "Unlisted product ($($_.SkuId))";
                    }
                }
        }
    }

Output

Cheers,

Lain

[#5]

Thanks for the reply.

I actually don´t need to know, what licenses user has. I only I need list of all users, that has E3 license assigned.

For me list would look like:

Mail, country

I only need mail and country info for all the users that has E3 assigned.

[#6]

@SauPom 

That doesn't change the script all that much.

The efficient thing to do is apply the "E3" filter on the $MsProducts list (using Excel, there was circa 270 products featuring "E3"), after which it's mostly the same process for comparing the users to the product list.

Example

<#
    To derive something meaningful from the dog's breakfest that is the Graph licencing commandlets, we have to:

    1. Import the CSV Microsoft provides containing the GUID-to-product mappings, to be used as a lookup table.
       The CSV URL can be found in the following doco:

        https://learn.microsoft.com/en-us/azure/active-directory/enterprise-users/licensing-service-plan-reference

    2. Get your users via Graph and translating the SkuId GUID into something human-readable by lookup up this GUID from the CSV data above.
#>

# Import the Microsoft CSV containing the translations. I'm going to use a Dictionary for storage as it's faster on lookups.
$MsProducts = [System.Collections.Generic.Dictionary[[guid],[string]]]::new();

Import-Csv -Path "D:\Data\ms-products-and-plans.csv" |
    ForEach-Object {
        if ((-not $MsProducts.ContainsKey($_.GUID)) -and ($_.Product_Display_Name.Contains("E3") -or $_.Service_Plan_Id.Contains("E3")))
        {
            $MsProducts.Add($_.GUID, $_.Product_Display_Name);
        }
    }

# Pull the list of users from Graph and output just the data we're interested in.
Get-MgBetaUser -All -Property mail, country, assignedLicenses |
    ForEach-Object {
        foreach ($Licence in $_.AssignedLicenses)
        {
            if ($MsProducts.ContainsKey($Licence.SkuId))
            {
                [PSCustomObject] @{
                    mail = $_.mail;
                    country = $_.country;
                }

                break;
            }
        }
    }

Output

Cheers,

Lain