how to format output of Get-MsolUser

[mfranhind115]

Hi all,

I'm getting info of the users under my tenant, from powershell with Module MSOnline.

I'm wondering how to format the output, this is the command I run:

Get-MsolUser -DomainName mydomain.com | Select-Object * | Out-file C:\mydomain.csv

but this command gets me data in a single column, like the following:

ExtensionData : System.Runtime.Serialization.ExtensionDataObject
AlternateEmailAddresses : {}
AlternateMobilePhones : {}
AlternativeSecurityIds : {}
BlockCredential : False
City :
CloudExchangeRecipientDisplayType : 1073741824
Country :
Department :
DirSyncProvisioningErrors : {}
DisplayName : Sally Nana
Errors :
Fax :
FirstName : Sally
ImmutableId :
....

Is there a way to format with one column for each data instead of a single column?

thanks!

[#2 · Accepted solution]

@mfranhind115 

To export to a CSV file, you want to use Export-Csv, not Out-File.

You can try to leverage Format-Table in conjunction with Out-File but I wouldn't recommend doing so as you're more than likely going to run into width issues and the data won't be re-importable as objects.

If you want to work with the data in something like Excel, PowerBI or something else like that, you want to work with Export-Csv.

That said, Export-Csv also has its nuances.

For example, it doesn't handle arrays or nested objects gracefully, and a good number of the attributes you're retrieving fit this category (i.e. the ones featuring the "{ }" braces):

• AlternateEmailAddresses
• AlternateMobilePhones
• AlternativeSecurityIds
• DirSyncProvisioningErrors

Note: Forget about retrieving ExtensionData using Get-MsolUser - you need to use one of the other Microsoft Azure AD modules (or the Graph API directly) to expand that.

Here's a quick example using the "usual" combination of PSCustomObject and a hash table to deal with attributes that are arrays of simple data types - like AlternateEmailAddresses.

Get-MsolUser -DomainName mydomain.com |
    ForEach-Object {
        [PSCustomObject] @{
            Id = $_.ObjectId;
            UserPrincipalName = $_.UserPrincipalName;
            AlternateEmailAddresses = $_.AlternateEmailAddresses -join "`n";
        }
    }

On line 6, you can see that we've taken the array attribute name AlternateEmailAddresses and used "-join" to place each e-mail address on a new line.

This looks messy if you view it with Notepad, but "proper" CSV editors like Microsoft Excel display the file just fine.

You can join using other separators like a semicolon or comma to avoid splitting across lines but that becomes a headache very quickly where there's more than two or three entries in the array, as the resulting line grows long quickly. But ultimately, it's your choice.

I won't provide an example of how to handle nested objects, but the process is quite similar. All that changes is what comes after each equals ("=") sign within the hash table (i.e. lines 4 to 6 in the example above.)

Alternative approach

Given you're selecting all attributes (by the way, you don't need the "| Select-Object *" section from your command at all if you want all attributes), I can't help but wonder if this is for reporting or some kind of downstream data consumption.

If it's the latter, then neither Out-File nor Export-Csv are your friends in this case. Export-Csv can be fine for many simpler situations but this isn't one of them.

If you're looking to preserve data fidelity (excluding cases like the ExtensionData attribute since it can't be read in the first place by Get-MsolUser) while avoiding all the complications found with Out-File (on its own) and Export-Csv, then you want to use ConvertTo-Json in conjunction with Out-File.

Here's an example that leverages ConvertTo-Json and Out-File to produce a data file that can be consumed by downstream systems, or even brought back into PowerShell via Get-Content in conjunction with ConvertFrom-Json:

Exporting full object(s) to a JSON file

Get-MsolUser -Domain mydomain.com | ConvertTo-Json -Depth 7 | Out-File -FilePath .\msol-users-mydomain.com.json;

Reading the file data back into a usable variable

$Users = Get-Content -Path .\msol-users-mydomain.com.json | ConvertFrom-Json;

Again, the primary use case for the JSON method would be for further data processing. It's not useful for reporting and so on since people will find it hard to read, sort on, comprehend, etc.

Cheers,

Lain

[#3]

thank you so much Lain!

[#4]

just a quick question: is there a way to combine:

ForEach-Object {
[PSCustomObject] @{
...

with

Select-Object

I mean, if I would extract some "basic" data like "UserPrincipalName, DisplayName, licenses, islicensed" and others nested like AlternateEmailAddresses, is there a way?

I tried right now something like this, without success:

Get-MsolUser -DomainName myDomain.com |
    Select-Object UserPrincipalName, DisplayName, licenses, islicensed |
	ForEach-Object {
       [PSCustomObject] @{
            AlternateEmailAddresses = $_.AlternateEmailAddresses -join "`n";
        }
    }  |  Where-Object {
        $Skus = $_.Licenses.AccountSku.SkuPartNumber;
        if ($Skus -notcontains "ATP_ENTERPRISE" -and ($Skus -contains "O365_BUSINESS_ESSENTIALS" -or $Skus -contains "O365_BUSINESS_PREMIUM")) { $true };
    }  

@mfranhind115