Select-object with AD object appears to be buggy

[Tarjei-stavanger]

Prerequisites

• Write a descriptive title.
• Make sure you are able to repro it on the latest released version
• Search the existing issues.
• Refer to the FAQ.
• Refer to Differences between Windows PowerShell 5.1 and PowerShell.

Steps to reproduce

I'm trying to produce a list pair of SamAccountName and ServicePrincipalName as a CSV file. I have tried the code on all PowerShell implementations I have available : ISE, Ordinary Powershell, Powershell 7.4.

The original code I use is as follows

$FormatEnumerationLimit = -1;
$aa = Get-ADComputer -Filter { ServicePrincipalName -ne "$null"} -Properties ServicePrincipalName
   $aa | select-object -Property SamAccountName -ExpandProperty ServicePrincipalName |
     export-CSV -NoTypeInformation -delimiter ';' C:\Temp\server_spn.csv;

What I get as a result is a list of hostnames and the length of the ServicePrincipalName. This is a bit surprising.

When I tried to locate the problem I use the following code.

$aa = Get-ADComputer -Filter { ServicePrincipalName -ne "$null"} -Properties ServicePrincipalName
   $aa | select-object -Property SamAccountName -ExpandProperty ServicePrincipalName

The result is a list of ServicePrincipalName. There is no corresponding SamAccountName. The content of the $aa variable is as expected.

The results that I'm getting from the code is rather surprising and to me, totally unexpected.

Expected behavior

See above.

Actual behavior

See also above

Error details

No obvious errors.

Environment data

Windows 10 Enterprise

Name                           Value
----                           -----
PSVersion                      7.4.0
PSEdition                      Core
GitCommitId                    7.4.0
OS                             Microsoft Windows 10.0.19045
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0, 5.0, 5.1, 6.0, 7.0}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Visuals

No response

[MartinGC94]

This: select-object -Property SamAccountName -ExpandProperty ServicePrincipalName is the problem.
Normally you either use Property to specify a list of properties you want from the input object, or ExpandProperty to get the raw/expanded value from the object. Combining the 2 apparently gets the raw value and then adds the specified properties as NoteProperties to that value.

If you want to create a CSV with SamAccountName and ServicePrincipalName then you just need to provide both values to the Property parameter like this: select-object -Property SamAccountName, ServicePrincipalName.
The reason you got the length of ServicePrincipalName is because Export-CSV exports all of the properties of the input object.
In your case, the input object was a string where you had added the SamAccountName property + the standard Length property that all strings have.

[Tarjei-stavanger]

select-object -Property SamAccountName, ServicePrincipalName does not unbundle ServicePrincipalName. It retains it as an array and thus is not what I want.

[MartinGC94]

Then how do you want to represent those multiple values in the CSV file? The way I would do it is to join them with some other separator, for example: Select-Object -Property SamAccountName, @{Name='ServicePrincipalName';Expression={$_.ServicePrincipalName -join ';'}} that's called a "Calculated property" if you want to search the web for more info about it.

[Tarjei-stavanger]

You still end up with the SamAccountName and the length of the ServicePrincipalName in the file.

So the situation has not improved.

[MartinGC94]

Then you must be doing something wrong.

[Tarjei-stavanger]

I take it that you have not actually checked.

[SeeminglyScience]

I take it that you have not actually checked.

If you're still seeing the length rather than the value, it's most likely you still have -ExpandProperty somewhere. That typically happens when one is attempting to export a string directly (even one wrapped in a PSObject with an extra property)

[Tarjei-stavanger]

Notice that the entire script is in the problem description. It is hard to avoid seeing an -ExpandProperty.

[Tarjei-stavanger]

I have been thinking and re-read the documentation of Select-Object.

The image shows part of Get-Member produces from the Select-Object statement in the description.

I see three issues with this that should perhaps be documented. 1. ServicePrincipalName is not a named property. 2. chars is not a Property. 3. The length of the Chars property is a property.

The Export-CSV function works fine and will output SamAccountName and Length. It appears that ParameterizedProperty is not a Property it knows about.

The conclusion is that Select-Object is broken. Export-CSV is only slightly broken since it does not know about ParameterizedProperty .

[SeeminglyScience]

Notice that the entire script is in the problem description. It is hard to avoid seeing an -ExpandProperty.

So take this command

$a | Select-Object -Property Name -ExpandProperty Date

What that translates to is

$a.Date | Add-Member -NotePropertyName Name -NotePropertyValue $a.Name -PassThru

The use cases for using both Property and ExpandProperty together are exceedingly rare, it's a very unusual thing to do. I think you might be inferring functionality based on the name that doesn't match what it's actually for.

I see three issues with this that should perhaps be documented. 1. ServicePrincipalName is not a named property. 2. chars is not a Property. 3. The length of the Chars property is a property.

Chars is the indexer for a string. Length is a property containining how many characters is in the string. This is why you don't want -ExpandProperty. You're getting the value of the property ServicePrincipalName (which is a string) and then you're adding the property SamAccountName to that string. The content you're getting in the CSV is expected, System.String by default only has a single (non-parameterized) property (Length).

The Export-CSV function works fine and will output SamAccountName and Length. It appears that ParameterizedProperty is not a Property it knows about.

That is correct, ParameterizedProperty has a parameter. For instance, if you do 'something'[0] and get the System.Char of s back, you're using the parameterized property called Chars with an argument of 0. It's basically a fancy way of saying indexer, with the added complication that COM supports parameterized properties that aren't explicitly indexers so we don't call it that.

---

I would recommend trying the snippet Martin shared, if you run into issues with it you can try Stack Overflow or the PowerShell discord.

[microsoft-github-policy-service]

This issue has been marked as answered and has not had any activity for 1 day. It has been closed for housekeeping purposes.