Export User Names and Proxy Addresses to CSV File

Export User Names and Proxy Addresses to CSV File

  • Comments 7
  • Likes

Summary: Microsoft Scripting Guy Ed Wilson talks about using Windows PowerShell to export user names and proxy addresses to a CSV file from Active Directory.

Hey, Scripting Guy! Question Hey, Scripting Guy! I am trying to produce a report of our users in Active Directory and their associated proxy addresses. I want it in Excel, so I am using the Export-CSV cmdlet. The issue is that although I can get the user names just fine, the proxy addresses come back with:

Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

This does not happen when I print to the screen, only when I export it to a CSV file. Help! This is quite annoying.

—JF

Hey, Scripting Guy! Answer Hello JF,

Microsoft Scripting Guy, Ed Wilson, is here. It is a new week down here in Charlotte, North Carolina in the United States. The weather has taken a turn for the worse, so instead of getting a daily deluge of rain, we now have hot and humid. Personally, I think I already miss the rain—I am not a huge fan of hot and humid. Luckily, I do not have to go out too often (one of the great things about working from home), so I can sit in front of a couple of fans, check my email, and get right to work.

The issue with a multivalued attribute

The issue with a multivalued attribute, such as the ProxyAddresses attribute, is that it is an array. This means it contains multiple values that are associated with a single attribute.This makes sense for something like ProxyAddresses because there could be one or more proxy addresses defined for any particular user in Active Directory Domain Services (AD DS). For something like street address, there is only one value permitted for that attribute because it accepts a single value only.

When I run the following commands, I can easily replicate the issue with the multivalued attribute. (This is a single, logical line command. I broke it at the pipe to display in the blog).

Get-ADUser -Filter * -SearchBase 'ou=testou,dc=iammred,dc=net' -Properties proxyaddresses |

select name, proxyaddresses | Export-CSV -Path c:\fso\proxyaddresses.csv –NoTypeInformation

When I run the command, and open the CSV file in Microsoft Excel, I am greeted with the following output: 

Image of spreadsheet

Fixing the issue with multivalued attributes

Perhaps the easiest way to fix the issue with the multivalued ProxyAddresses attribute is to create a custom Select-Object property, then index directly into the array to pull out proxy address 1 and proxy address 2. To do this, I use a hash table to create a new property. The hash table requires two elements: the label and the expression. The label is a string, and the expression is a script block. In Windows PowerShell terms, this means that I can basically do anything I need to do inside the expression element.

Get-ADUser -Filter * -SearchBase 'ou=testou,dc=iammred,dc=net' -Properties proxyaddresses |

select name, @{L='ProxyAddress_1'; E={$_.proxyaddresses[0]}},

@{L='ProxyAddress_2';E={$_.ProxyAddresses[1]}} |

Export-Csv -Path c:\fso\proxyaddresses.csv -NoTypeInformation

I then open the CSV file in Microsoft Excel. The command is shown here:

PS C:\> Get-ADUser -Filter * -SearchBase 'ou=testou,dc=iammred,dc=net' -Properties pr

oxyaddresses | select name, @{L='ProxyAddress_1'; E={$_.proxyaddresses[0]}}, @{L='Pro

xyAddress_2';E={$_.ProxyAddresses[1]}} | Export-Csv -Path c:\fso\proxyaddresses.csv -

NoTypeInformation

Image of command

The Excel spreadsheet appears, and now I have two columns worth of proxy addresses as shown in the following image:

Image of spreadsheet

JF, that is all there is to using Windows PowerShell to retrieve multivalued attributes and write them to a CSV file. Join me tomorrow when I will talk about more cool Windows PowerShell stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • This answers a question I have been trying to work on but this only works on a fixed number of addresses. I have some accounts that have more proxy addresses than others. What would be the best way of extracting these into a csv file. I need these in one column, proxyAddresses, with each address separated by a semicolon.

  • AC: Replace the Select-Object command with:

    Select-Object Name, @{L = "ProxyAddresses"; E = { $_.ProxyAddresses -join ";"}}

    Be aware that ";" is used as a column delimiter in some CSV variations.

    I don't really understand why one should export only 2 proxy addresses like in the example in the article.

  • i agree with AC. If I don't know the number of addresses, it's useless at all. Why not extend it to a more common scenario?

  • @AC, Camlost, WB That is tomorrows blog article.

  • We can execute command to export AD users in CSV format.

    csvde -r objectClass=user -f filename.csv

    Regards,

    MD

  • Hey, Thanks a bunch for this info. I was struggling with how to dump user memberships like this. I had to add a bunch more entries, but this was awesome.

  • thank you