Advanced Windows PowerShell Scripting Video Training

Advanced Windows PowerShell Scripting Video Training
Advanced Windows PowerShell Scripting Video Training

Wednesday, December 2, 2015

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

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.

No comments: