Powershell Cascading Foreach to export to Csv

November 25, 2021

I’m exporting my AAD users to CSV files, works fine with this code.

$allUsers = Get-AzureADUser -All $true

$users = $allUsers |
              Select-Object -Property ObjectId,ObjectType,UserPrincipalName,DisplayName,AccountEnabled,AgeGroup,City,CompanyName,ConsentProvidedForMinor,Country,CreationType,Department,DirSyncEnabled,FacsimileTelephoneNumber,GivenName,IsCompromised,ImmutableId,JobTitle,LastDirSyncTime,LegalAgeGroupClassification,Mail,MailNickName,Mobile,OnPremisesSecurityIdentifier,PasswordPolicies,PhysicalDeliveryOfficeName,PostalCode,PreferredLanguage,RefreshTokensValidFromDateTime,ShowInAddressList,State,StreetAddress,Surname,TelephoneNumber,UsageLocation,UserState,UserStateChangedOn,UserType,DeletionTimestamp,AssignedLicenses,AssignedPlans,ProvisionedPlans |
              ForEach-Object{
                $_.DisplayName = $_.DisplayName -replace "\n", ' ' -replace '"', '`'
                $_
                }
$users | Export-Csv -Path $TempFileName -NoTypeInformation


$provisionedPlans = $users = $allUsers |
              Select-Object -Property ObjectId,DisplayName,ProvisionedPlans

But, ProvisionedPlans comes out as a list, so I would like to export it for each entry in the list as 1 line.
This is a sample of the field

ProvisionedPlans               : {class ProvisionedPlan {
                                   CapabilityStatus: Enabled
                                   ProvisioningStatus: Success
                                   Service: MicrosoftCommunicationsOnline
                                 }
                                 , class ProvisionedPlan {
                                   CapabilityStatus: Deleted
                                   ProvisioningStatus: Success
                                   Service: MicrosoftCommunicationsOnline
                                 }
                                 , class ProvisionedPlan {
                                   CapabilityStatus: Deleted
                                   ProvisioningStatus: Success
                                   Service: MicrosoftCommunicationsOnline
                                 }
                                 , class ProvisionedPlan {
                                   CapabilityStatus: Enabled
                                   ProvisioningStatus: Success
                                   Service: SharePoint
                                 }
                                 ...}

So bottom line what I would like to see in the output would be

ObjectId,DisplayName,CapabilityStatus,ProvisioningStatus,Service
id1,User1,Enabled,Success,MicrosoftCommunicationsOnline
id1,User1,Deleted,Success,MicrosoftCommunicationsOnline
id1,User1,Deleted,Success,MicrosoftCommunicationsOnline
id1,User1,Enabled,Success,SharePoint
id2,User2,Enabled,Success,Whatever

So please feel free, i’m not a Powershell specialist.

>Solution :

Following your guidance of what you want as a bottom line please see the below adjustments to your script:

$allUsers = Get-AzureADUser -All $true

$Results = Foreach ($user in $allusers){
    Foreach($Plan in $user.ProvisionedPlans){
        $Plan | Select @{Name = 'ObjectId';  Expression = {$user.Objectid}},@{Name = 'DisplayName';Expression = {$user.DisplayName}},CapabilityStatus,ProvisioningStatus,Service
    }
}

$Results | Export-Csv -Path $TempFileName -NoTypeInformation

I am not sure why you are replacing characters in the DisplayName but I have added this to the calculated properties in the Select Statemant.

I have also done this on the iteration of each provisioned plan as this seemed to be the easiest route.

I have removed the initial select with all of the properties as the properties you are interested in are being exported. (If you require all properties I would advise using Select * as it will pull the majority of properties in most cases and will look tidier in the code.)