Write Users and Proxy Addresses to CSV by Using PowerShell

Write Users and Proxy Addresses to CSV by Using PowerShell

  • Comments 5
  • Likes

Summary: Microsoft Scripting Guy, Ed Wilson, talks about writing all proxy addresses and user names to a CSV file by using Windows PowerShell.

Hey, Scripting Guy! Question Hey, Scripting Guy! Yesterday’s blog post, Export User Names and Proxy Addresses to CSV File, showed me an easy way to get a couple of proxy addresses from Active Directory, but all users do not consistently have only one or two addresses. Some users have as many as four or five. I want to know exactly how many I have, and I need to know all of the addresses. Can you help?

—PQ

Hey, Scripting Guy! Answer Hello PQ,

Microsoft Scripting Guy, Ed Wilson, is here. Well, it has not gotten any cooler today down here in Charlotte, North Carolina. But the evenings are not too bad after the sun goes down. The mornings are actually quite nice right before the sun comes up. I am trying to figure out a way to do the siesta thing down here, but for some strange reason I cannot catch on. Bummer—it is a great idea.

I decided to break down and write a script to solve this issue. I probably could have done it in a one-liner, but it begins to get too complicated, convoluted, and cumbersome. In the end, many times a simple script is much better than a very hard to read one-liner…at least in my mind.

Gather the users information

The first thing I do is gather together all of the user names with which I want to work. I make sure to pick up the ProxyAddresses attribute because it is not selected by default. I store all of the returned user objects in a variable named $users. This line of script is showne here:

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

Walk through the users

I need to walk through my collection of users. I do this by using the Foreach language statement. Inside the loop, I will use $u to refer to a specific user. This script is shown here:

Foreach ($u in $users)

 {

Now I create an ordered dictionary object. This is a new feature in Windows PowerShell 3.0. It acts like a hash table, except that it is ordered. The key to success is that [ordered] MUST appear right in front of the @ symbol. In addition, I need to completely delete the variable after I am done using it. If I cast the ordered dictionary to a hash table, I lose the ordered attribute. There is no way to specify how I am going to order the dictionary object—it does it by maintaining the order in which the items become added to the dictionary. Here, I create the ordered dictionary object, and then I add the user name to it:

$proxyAddress = [ordered]@{}

 $proxyAddress.add("User",$u.name)

I want to add all of the proxy addresses. To do this, I use the Count property from the array of ProxyAddresses, and I use the For statement to count actions for each of the addresses. I then use the Add method to add the ProxyAddresses values to a new field named after the order of ProxyAddress. This section of script is shown here:

For ($i = 0; $i -le $u.proxyaddresses.count; $i++)

   {

    $proxyAddress.add("ProxyAddress_$i",$u.proxyaddresses[$i])

    } #end for

I cast the ordered dictionary to a PSCustomObject, and then I pipe it to the Export-CSV cmdlet. The key here is to remember to use the –NoTypeInformation switch or the newly created CSV file will not display properly in Microsoft Excel (due to the entrenched type information). Now I remove the variable. This is shown here:

[pscustomobject]$proxyAddress |

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

  Remove-Variable -Name proxyAddress } #end foreach 

The last thing I do in my script is use Invoke-Item to open the CSV file. By default, this opens in Excel— that for my purposes is fine. The following image illustrates the newly created Excel spreadsheet:

Image of spreadsheet

PQ, that is all there is to using Windows PowerShell to display all proxy address values. 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 isn't working very well. It seems that the first one added to the CSV defines the limit to the number of columns it will accept.

    The first account pulled out of our AD had 5 proxyaddresses. That seems to have become the standard as any user with greater than 5 (some have up to 13, which I'm trying to weed out) just have the additional ones truncated in the output file. They make it to that stage of the script intact, but when it is appended to the file, it removes the addresses over 5.

    Sorry for the verbose explanation. Please let me know if I was not clear.

  • So I did this...

    $users = Get-ADUser -Filter * -Properties proxyaddresses

    $maxProxy = $users | %{$_.proxyaddresses.count} | Sort-Object | Select-Object -Last 1

    foreach ($u in $users)

    {

    $proxyaddress = [ordered]@{}

    $proxyaddress.Add("User",$u.name)

    for ($i=0; $i -le $maxProxy; $i++)

    {

    $proxyaddress.add("proxyaddress_$i",$u.proxyaddresses[$I])

    } #end for

    [pscustomobject]$proxyAddress | Export-Csv -Path h:\fso\proxyaddresses.csv -NoTypeInformation –Append -Force

    #$proxyaddress #just to see if it made it this far.

    Remove-Variable -Name proxyAddress

    }

  • @All - suggestion:

    Sort results descending on the count of addresses in the array.

    This is why I prefer to use a pipe delimited field for array contents.  It is pretty common in CSV to do that.

  • One more thing I noticed... since it's 0 indexed, it should be less than the max, not less than or equal.

  • This is so complex... Isn't this method easier?

    exchangeshare.wordpress.com/.../powershell-export-multivalued-properties