Skip to main content

Why does my CSV file contain Microsoft.ActiveDirectory.management.ADPropertyValueCollection

By

The answer to this has to do with how a CSV file is used with PowerShell.

In a CSV file, you are importing objects to be used with PowerShell.  The first line list all of the property names of the objects.  Each line after that is a unique object with the value of the property.  Take a look below:

Name GivenName SurName City Office
Jason Yoder Jason Yoder Indianapolis HQ
Matt Myers Matt Myers Tampa Regional

 

In this example, we are creating 2 objects.  Each object will have 5 properties:

  • Name
  • GivenName
  • SurName
  • City
  • Office

CSV property values can only hold a singular value.  Many properties that you come across will contain multiple properties.  Take a look at this example:

PS C:\> Get-ADUser -Identity Administrator -Properties MemberOf

 

 

DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com

Enabled           : True

GivenName         :

MemberOf          : {CN=Organization Management,OU=Microsoft Exchange Security

                    Groups,DC=PowerIT,DC=com, CN=Group Policy Creator

                    Owners,CN=Users,DC=PowerIT,DC=com, CN=Domain

                    Admins,CN=Users,DC=PowerIT,DC=com, CN=Enterprise

                    Admins,CN=Users,DC=PowerIT,DC=com...}

Name              : Administrator

ObjectClass       : user

ObjectGUID        : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c

SamAccountName    : Administrator

SID               : S-1-5-21-2015014380-1668822753-3530896179-500

Surname           :

UserPrincipalName : Administrator@PowerIT.com

 

Take note of the MemberOf property.  This user is a member of multiple groups.  In other words, this property contains a collection (array).  You can also see the { and } at the beginning and end of the property value.  This tells you that this property contains (or could contain) multiple values.  Because of this, it is not a good candidate for a CSV file.

Let’s export this to a CSV file and see the results.

Get-ADUser -Identity Administrator -Properties MemberOf | Export-csv -Path c:\temp\admin.csv

 

And now let’s read the CSV intoPowerShell

PS C:\> Import-Csv -Path C:\Temp\admin.csv

 

 

DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com

Enabled           : True

GivenName         :

MemberOf          : Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

Name              : Administrator

ObjectClass       : user

ObjectGUID        : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c

SamAccountName    : Administrator

SID               : S-1-5-21-2015014380-1668822753-3530896179-500

Surname           :

UserPrincipalName : Administrator@PowerIT.com

 

Take a look at the MemberOf property.  You can see that instead of getting all the groups the user is a member of, we get Microsoft.ActiveDirectory.Management.ADPropertyValueCollection.  To objects to disk that have properties that contain collections, you need to use XML.

PS C:\> Get-ADUser -Identity Administrator -Properties MemberOf | Export-Clixml -Path c:\temp\admin.xml

 

PS C:\> Import-Clixml -Path C:\Temp\admin.XML

 

 

DistinguishedName : CN=Administrator,CN=Users,DC=PowerIT,DC=com

Enabled           : True

GivenName         :

MemberOf          : {CN=Organization Management,OU=Microsoft Exchange Security

                    Groups,DC=PowerIT,DC=com, CN=Group Policy Creator

                    Owners,CN=Users,DC=PowerIT,DC=com, CN=Domain

                    Admins,CN=Users,DC=PowerIT,DC=com, CN=Enterprise

                    Admins,CN=Users,DC=PowerIT,DC=com...}

Name              : Administrator

ObjectClass       : user

ObjectGUID        : 65b7e8a6-b040-4e6c-8dda-d408eca9f22c

SamAccountName    : Administrator

SID               : S-1-5-21-2015014380-1668822753-3530896179-500

Surname           :

UserPrincipalName : Administrator@PowerIT.com

 

I often get told that the user needs to consume the data in Excel.  Well, you can’t. The problem is that a CSV file is not designed to do this.  Unfortunately life is not always far.  The default cmdlets that PowerShell comes with cannot help you.  uses, you could create a custom object with a unqiue property for each potential value of MemberOf.  The problem  with that is that A CSV file header is created from the properties of the first object.  You would have to read all objects and make sure that your first object has a place holder for the maximum expected number of values in MemberOf.  Sound complex, well it is.  The other option is to filter all all values of the object that do not contain singular data.

Labels:

Comments

Post a Comment

Popular posts from this blog

Adding a Comment to a GPO with PowerShell

By
As I'm writing this article, I'm also writing a customization for a PowerShell course I'm teaching next week in Phoenix.  This customization deals with Group Policy and PowerShell.  For those of you who attend my classes may already know this, but I sit their and try to ask the questions to myself that others may ask as I present the material.  I finished up my customization a few hours ago and then I realized that I did not add in how to put a comment on a GPO.  This is a feature that many Group Policy Administrators may not be aware of. This past summer I attended a presentation at TechEd on Group Policy.  One organization in the crowd had over 5,000 Group Policies.  In an environment like that, the comment section can be priceless.  I always like to write in the comment section why I created the policy so I know its purpose next week after I've completed 50 other tasks and can't remember what I did 5 minutes ago. In the Group Policy module for PowerShell V3, th
Post a Comment
Read more

Return duplicate values from a collection with PowerShell

By
If you have a collection of objects and you want to remove any duplicate items, it is fairly simple. # Create a collection with duplicate values $Set1 = 1 , 1 , 2 , 2 , 3 , 4 , 5 , 6 , 7 , 1 , 2   # Remove the duplicate values. $Set1 | Select-Object -Unique 1 2 3 4 5 6 7 What if you want only the duplicate values and nothing else? # Create a collection with duplicate values $Set1 = 1 , 1 , 2 , 2 , 3 , 4 , 5 , 6 , 7 , 1 , 2   #Create a second collection with duplicate values removed. $Set2 = $Set1 | Select-Object -Unique   # Return only the duplicate values. ( Compare-Object -ReferenceObject $Set2 -DifferenceObject $Set1 ) . InputObject | Select-Object – Unique 1 2 This works with objects as well as numbers.  The first command creates a collection with 2 duplicates of both 1 and 2.   The second command creates another collection with the duplicates filtered out.  The Compare-Object cmdlet will first find items that are diffe
Post a Comment
Read more

How to list all the AD LDS instances on a server

By
AD LDS allows you to provide directory services to applications that are free of the confines of Active Directory.  To list all the AD LDS instances on a server, follow this procedure: Log into the server in question Open a command prompt. Type dsdbutil and press Enter Type List Instances and press Enter . You will receive a list of the instance name, both the LDAP and SSL port numbers, the location of the database, and its status.
Post a Comment
Read more