Exporting Multi-Valued Attributes in PowerShell to Excel - Attempting to Automate Using an Array of Hash Tables

Question

I am trying to automate the unwrapping of multi-valued attributes in PowerShell for the purpose of exporting the information to an excel sheet. The known solution for enabling multi-valued exporting to excel is to manually create a hash table for each multi-valued attribute, which works just well. Example:

Get-ADForest -Server <servername> |

Select-Object @{Name='ApplicationPartitions';Expression={$.ApplicationPartitions}},
              DomainNamingMaster,
              @{Name="Domains";Expression={$.Domains}},
              ForestMode,
              @{Name="GlobalCatalogs";Expression={$.GlobalCatalogs}},
              Name,
              PartitionsContainer,
              RootDomain,
              SchemaMaster,
              @{Name="Sites";Expression={$.Sites}} |

Export-Csv $home'AD Forest Information.csv'

However, I would like to automate this for commands that have many multi-valued attributes. Here is what I have so far (I am using Get-ADForest for testing):

$Objects = @()

$Object = @{}

Get-ADForest | Get-Member | Where-Object {$_.membertype -eq 'property'} |

Select-Object Name |

ForEach-Object {$Object = @{Name=$_.Name;Expression="$" + "_" + "." + $_.Name} ; $Objects +=$Object}

Write-Output $Objects

Get-ADForest -Server <servername> | Select-Object $Objects | Export-Csv $home\'AD Forest Information.csv'

The issue is that the exported .csv has the correct column titles, but the values of the attributes are still Microsoft.ActiveDirectory.Management.ADPropertyValueCollection like they would be without unwrapping the attribute.

Please let me know if you have any questions.

Any suggestions would be awesome.

Thank you!

Austin

Answer 1

Try this for your example, let me know if this is what you expect as output for your csv:

cd $Home\Documents

function parseADObject{
param(
    [cmdletbinding()]
    [parameter(mandatory,valuefrompipeline)]
    $InputObject
)
    $properties=($inputObject|gm -MemberType Property).Name
    $parsedObject=@{}

    foreach($prop in $Properties)
    {
        $thisProp=$inputObject.$prop

        if($thisProp -and $thisProp.GetType() -match 'Collection|\[]')
        {
            $val=($inputObject.$prop|out-string).Trim()
        }
        elseif($thisProp -and $thisProp.GetType() -match 'Int|Boolean|Double')
        {
            $val=$thisProp
        }
        elseif($thisProp)
        {
            $val=($thisProp.ToString()).Trim()
        }
        else
        {
            $val=$null
        }

        $parsedObject.$prop=$val
    }

    return $parsedObject|%{New-Object PSObject -Property $_}
}

Get-ADForest|parseADObject|Export-Csv test.csv -NoTypeInformation;ii test.csv

Explanation, I'm gonna use ADForest for this example:

First you get the properties of the object you want to parse:

$adforest=Get-ADForest

PS C:\> $properties=($adforest|gm -MemberType Property).Name

PS C:\> $properties
ApplicationPartitions
CrossForestReferences
DomainNamingMaster
Domains
ForestMode
GlobalCatalogs
Name
PartitionsContainer
RootDomain
SchemaMaster
Sites
SPNSuffixes
UPNSuffixes

Then you loop on the properties of your object asking for the type of object:

PS C:\> foreach($prop in $properties){$adforest.$prop.gettype()}

IsPublic IsSerial Name                                     BaseType                                                         
-------- -------- ----                                     --------                                                         
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     True     String                                   System.Object                                                    
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     True     ADForestMode                             System.Enum                                                      
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     True     String                                   System.Object                                                    
True     True     String                                   System.Object                                                    
True     True     String                                   System.Object                                                    
True     True     String                                   System.Object                                                    
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     False    ADPropertyValueCollection                System.Collections.CollectionBase                                
True     False    ADPropertyValueCollection                System.Collections.CollectionBase

And then you're just asking what type of object it is:

• If the type matches 'Collection or []' then you're passing the value of that property to Out-String this is useful for properties like proxyAddresses on the AD Users.
• ElseIf the type matches a Boolean or Integer or Double then you're leaving the value as is without any changes.
• ElseIf the the property has any Value, meaning, is not $null, I'm using the ToString() method. This is useful for properties like ObjectGUID or ObjectSID

Notice I'm using Trim() each time I use Out-String or ToString() to eliminate any empty blank spaces at the beginning or the end

The value of each property loop, stored in the $val variable is then being added to the hashTable $parsedObject and then after looping is finished the variable is being converted to a PSObject (if you don't do this the export will not show as you expect it, it will show the type of the values instead of the actual data).