Welcome to the third post of my series on how to get more results from PowerShell.
- How to force PowerShell to export multi-value properties when exporting to csv files
This tutorial will show you how to force PowerShell to export multi-value properties when exporting to csv files. By default, the export of multi-value properties will fail when being exported to csv.
This will happen even if the multi-valued property only has a single value.
================================================================
All tutorials in this series -
- How to create PowerShell outputs to show all data without truncation - Link
- How to force PowerShell to display all the properties by default - Link
- How to force PowerShell to export multi-value properties when exporting to csv files - Link
Related PowerShell Tutorials
- How to create basic PowerShell scripts - Link
- How to create basic PowerShell scripts with Export-CSV - Link
- How to create basic PowerShell scripts with Import-CSV - Link
================================================================
When you start creating PowerShell reports, you will quickly see that there are many different ways to output your results or queries. In many circumstances you will need to export your reports to csv files, for example when creating reports on users or mailboxes in your Organization.
Unfortunately many user and mailbox properties have multiple values that are not easily captured and exported to csv when using a standard export script that may work in most circumstances (where the properties you choose to export are a single valued property).
In a previous article, you saw that PowerShell would sometimes fail when exporting all the data, in particular when exporting properties with multiple values. As you can see below, when trying to export properties that are multi-valued with Get-AdUser - some properties failed to export correctly.
Local Active Directory query export to CSV.
Get-AdUser user02 -Properties * |
Select DisplayName,
MemberOf,
ProxyAddresses |
Export-csv -NoTypeInformation c:\scripts\User02-AD-Details.csv
Below you can see that the property - MemberOf - is a multi-valued property and fails and exports the value as - Microsoft.ActiveDirectory.Management.ADPropertyValueCollection -
As well as Local Active Directory, the export will also fail for some properties when using Azure AD v1 - MSOL cmdlets.
For example, running the script below to export some basic details on an Office 365 user account using the cmdlet Get-MsolUser will work perfectly as all the properties I have selected only have a single value.
Get-MsolUser -UserPrincipalName user01@teamterry365.com |
Select SignInName,DisplayName |
export-csv -NoTypeInformation c:\scripts\User01-Details.csv
However, if I edit the script to include exporting all email addresses, the export fails to export the multiple values for the property - ProxyAddresses.
Select SignInName,
DisplayName,
ProxyAddresses |
export-csv -NoTypeInformation c:\scripts\User01-Details.csv
As you can see below, the ProxyAddresses property fails to show all values and instead exports -
System.Collections.Generic.List`1[System.String]
Exchange will also fail on properties with multiple values.
For example, I have received many different errors on a simple Get-Mailbox export from my local Exchange Org -
I ran the cmdlet below to export some selected properties for a local mailbox - user02
Get-Mailbox user02 |
Select DisplayName,
GrantSendOnBehalfTo,
EmailAddresses,
ProtocolSettings,
AuditDelegate |
Export-csv -NoTypeInformation c:\scripts\User02-Details.csv
The csv file had errors when reporting multiple values.
| GrantSendOnBehalfTo |
| Microsoft.Exchange.Data.Directory.ADMultiValuedProperty`1 [Microsoft.Exchange.Data.Directory.ADObjectId] |
EmailAddresses |
| Microsoft.Exchange.Data.ProxyAddressCollection |
ProtocolSettings
|
Note the pattern with some of them starting with one of the following values -
- Microsoft.Exchange.Data.MultiValuedProperty`1
- Microsoft.Exchange.Data.Directory.ADMultiValuedProperty`1
There are many more variations of these errors, but the MultiValuedProperty errors can all be resolved the same way. The same solution applies in this instance to the Exchange output error -
Microsoft.Exchange.Data.ProxyAddressCollection.
=========================================================
Resolution
We can force PowerShell to output all the values of a multi-valued property with the Join function before exporting it.
This works for all the previous and following examples in local Active Directory, Exchange and Azure Active Directory.
Local Active Directory
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
Azure Active Directory - MSOL
System.Collections.Generic.List`1[System.String]
Exchange -
Microsoft.Exchange.Data.Directory.ADMultiValuedProperty`1[Microsoft.Exchange.Data.Directory.ADObjectId]
Microsoft.Exchange.Data.ProxyAddressCollection
Microsoft.Exchange.Data.MultiValuedProperty`1[System.String]
Microsoft.Exchange.Data.Directory.ADMultiValuedProperty`1[Microsoft.Exchange.Data.Directory.MailboxAuditOperations]
Use this as a template for updating your scripts that fail
@{name="GrantSendOnBehalfTo";expression={$_.GrantSendOnBehalfTo -join ";"}}
Simply replace the property in your script that you have selected with the Join function above.
Just update the line above with the correct Property (in red).
Read on for a more detailed explanation and some examples
------------------------------------------------------------------------------------------
Error - Local Active Directory
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
The script below was used to export the values of three properties for a local AD user.
The second and third property - MemberOf and ProxyAddresses, are multi-valued properties and will fail to export these values by default.
Get-AdUser user02 -Properties * |
Select DisplayName,
MemberOf,
ProxyAddresses |
Export-csv -NoTypeInformation c:\scripts\User02-AD-Details.csv
As you can see below, both the MemberOf property and the ProxyAddresses property fail to show all values and instead they both export -
Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
MemberOf
MemberOf
ProxyAddresses
To resolve this issue, we can edit the script to join the multiple values in the MemberOf property before exporting.
Use the 'template' below -
@{name="GrantSendOnBehalfTo";expression={$_.GrantSendOnBehalfTo -join ";"}}
- Be careful not to change anything else in the script, like the pipe symbol or commas.
-----------
Line 3 of the script - the property - MemberOf -
Has been replaced with
@{name="MemberOf";expression={$_.MemberOf -join ";"}}
Old Script -
Get-AdUser user02 -Properties * |
Select DisplayName,
MemberOf,
ProxyAddresses |
Export-csv -NoTypeInformation c:\scripts\User02-AD-Details.csv
New Script -
Get-AdUser user02 -Properties * |
Select DisplayName,
@{name="MemberOf";expression={$_.MemberOf -join ";"}},
ProxyAddresses |
Export-csv -NoTypeInformation c:\scripts\User02-AD-Details.csv
This resolves the issue and the script will now export the multiple values for the Property - MemberOf - to the csv file.
Next we update Line 4 with the same 'template' and update with the property as before.
This time we are replacing the property - ProxyAddresses -
Final Script -
Get-AdUser user02 -Properties * |
Select DisplayName,
@{name="MemberOf";expression={$_.MemberOf -join ";"}},
@{name="ProxyAddresses";expression={$_.ProxyAddresses -join ";"}} |
Export-csv -NoTypeInformation c:\scripts\User02-AD-Details.csv
This resolves the issue and the script will now export both of the multiple values for the Property - MemberOf and ProxyAddresses - to the csv file.
------------------------------------------------------------------------------------------
Error - Azure Active Directory - MSOL
System.Collections.Generic.List`1[System.String]
The script below was used to export the values of three properties for an MSOL user.
The third property - ProxyAddresses, has multiple values and will fail to export these values by default.
Select SignInName,
DisplayName,
ProxyAddresses |
export-csv -NoTypeInformation c:\scripts\User01-Details.csv
As you can see below, the ProxyAddresses property fails to show all values and instead exports -
System.Collections.Generic.List`1[System.String]
To resolve this issue, we can edit the script to join the multiple values in the ProxyAddresses property before exporting.
Once again, use the 'template' below -
@{name="GrantSendOnBehalfTo";expression={$_.GrantSendOnBehalfTo -join ";"}}
Line 4 of the script - the property - ProxyAddresses -
Has been replaced with
@{name="ProxyAddresses";expression={$_.ProxyAddresses -join ";"}}
Old Script -
Get-MsolUser -UserPrincipalName user01@teamterry365.com |
Select SignInName,
DisplayName,
ProxyAddresses |
export-csv -NoTypeInformation c:\scripts\User01-Details.csv
New Script -
Get-MsolUser -UserPrincipalName user01@teamterry365.com |
Select SignInName,
DisplayName,
@{name="ProxyAddresses";expression={$_.ProxyAddresses -join ";"}} |
export-csv -NoTypeInformation c:\scripts\User01-Details.csv
This resolves the issue and the script will now export the multiple values to the csv file.
The same process can be applied to resolve issues with Exchange local not exporting multiple values to CSV.
-------------------------------------------------------------------------------------------------------------
Error - Exchange issue exporting multiple value properties.
I ran the cmdlet below to export some selected properties for a local mailbox - user02
Get-Mailbox user02 |
Select DisplayName,
GrantSendOnBehalfTo,
EmailAddresses,
ProtocolSettings,
AuditDelegate |
Export-csv -NoTypeInformation c:\scripts\User02-Details.csv
As you can see in the screen shot below, the csv file shows incorrect data for properties with multiple values.
So let's try updating these one at a time to see the results.
Here is the script in PowerShell ISE so it is easier to read and understand the changes.
I have used Enter after each of the properties that are selected, and this script will work in ISE if the Exchange PowerShell snap-in is loaded.
Just like in the previous examples, we can join all the values of a multi-valued property with the Join function before exporting it.
Once again, use the 'template' below -
@{name="GrantSendOnBehalfTo";expression={$_.GrantSendOnBehalfTo -join ";"}}
In the script below, I have updated the line GrantSendOnBehalfTo to use the join function.
Line 3 of the script - the property - GrantSendOnBehalfTo -
Has been replaced with
@{Name=’GrantSendOnBehalfTo’;Expression={[string]::join(";", ($_.GrantSendOnBehalfTo))}}
Note that the comma is still at the end of the line.
The updated script has successfully exported the properties for the GrantSendOnBehalfTo (which is a multi-value property) -
Now I will update all of the properties that are multi-valued properties and have failed in the original script.
Get-Mailbox user02 |
Select DisplayName,
@{name="GrantSendOnBehalfTo";expression={$_.GrantSendOnBehalfTo -join ";"}},
@{name="EmailAddresses";expression={$_.EmailAddresses -join ";"}},
@{name="ProtocolSettings";expression={$_.ProtocolSettings -join ";"}},
@{name="AuditDelegate";expression={$_.AuditDelegate -join ";"}} |
Export-csv -NoTypeInformation c:\scripts\User02-Details.csv
As you can see below, the script now exports all the multi-valued properties correctly.
GrantSendOnBehalfTo
EmailAddresses
ProtocolSettings
AuditDelegate
===================================================================
- Notes -
I found the 'template' below to work fine with MSOL Azure Active Directory and Exchange, but failed with Local Active Directory on Server 2016.
I have NOT tested this on earlier versions of Windows Server, but my research for this article had this format as the original resolution
@{Name=’ProxyAddresses’;Expression={[string]::join(";", ($_.ProxyAddresses))}}
-------------------------------------------------------------------------------------------
Congratulations !!!
You now know how to force PowerShell to export multi-value properties when exporting to csv files
-------------------------------------------------------------------------------------------
Check out a list of ALL of my tutorials here - Link
No comments:
Post a Comment