Script for User Name, Email and Office to CSV

I have the following script.  It works just fine for the name and email, but I cannot get the office field to work.  What am I missing here?
# Basic information to run the script
$File = "\\server\files\test\test.csv"
$SearchRoot = "LDAP://OU=Staff,DC=mydomain,DC=com"
 
# Filter to find the things we want
$Filter = "(objectClass=User)"
 
# Connect to the search root
$SearchRootDE = New-Object System.DirectoryServices.DirectoryEntry($SearchRoot)
 
# Set up the search
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($SearchRootDE, $Filter)
$intProp = $Searcher.PropertiesToLoad.Add("name")
$intProp = $Searcher.PropertiesToLoad.Add("mail")
$intProp = $Searcher.PropertiesToLoad.Add("office")
 
# Get the results
$Results = $Searcher.FindAll()
 
# Sort through the results and save them in an array
$Users = @()
ForEach ($Result in $Results) {
  $Users += $Result.Properties | Select-Object `
        @{n="name";e={$_.name}}, `
        @{n="mail";e={$_.mail}}
        @{n="office";e={$_.office}}
}
 
# Write the array to a file
$Contacts | Export-CSV -Path $File

Open in new window

LVL 10
wdurrettAsked:
Who is Participating?
 
Chris DentPowerShell DeveloperCommented:

Try this version.

Chris
# Basic information to run the script
$File = "\\server\files\test\test.csv"
$SearchRoot = "LDAP://OU=Staff,DC=mydomain,DC=com"
 
 
# Filter to find the things we want
$Filter = "(objectClass=User)"
 
# Connect to the search root
$SearchRootDE = New-Object System.DirectoryServices.DirectoryEntry($SearchRoot)
 
# Set up the search
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($SearchRootDE, $Filter)
$intProp = $Searcher.PropertiesToLoad.Add("name")
$intProp = $Searcher.PropertiesToLoad.Add("mail")
$intProp = $Searcher.PropertiesToLoad.Add("physicalDeliveryOfficeName")
 
 
# Get the results
$Results = $Searcher.FindAll()
 
# Sort through the results and save them in an array
$Users = @()
ForEach ($Result in $Results) {
  $Users += $Result.Properties | Select-Object `
        @{n="name";e={$_.name}}, `
        @{n="mail";e={$_.mail}}, `
        @{n="physicalDeliveryOfficeName";e={$_.physicaldeliveryofficename}}
}
 
# Write the array to a file
$Users |  Export-CSV -Path $File

Open in new window

0
 
wdurrettAuthor Commented:
here is my updated file.  Still no dice.  The "Office" column turns up blank.
# Basic information to run the script
$File = "\\server\files\test\test.csv"
$SearchRoot = "LDAP://OU=Staff,DC=mydomain,DC=com"
 
# Filter to find the things we want
$Filter = "(objectClass=User)"
 
# Connect to the search root
$SearchRootDE = New-Object System.DirectoryServices.DirectoryEntry($SearchRoot)
 
# Set up the search
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($SearchRootDE, $Filter)
$intProp = $Searcher.PropertiesToLoad.Add("name")
$intProp = $Searcher.PropertiesToLoad.Add("mail")
$intProp = $Searcher.PropertiesToLoad.Add("office")
 
 
# Get the results
$Results = $Searcher.FindAll()
 
# Sort through the results and save them in an array
$Users = @()
ForEach ($Result in $Results) {
  $Users += $Result.Properties | Select-Object `
        @{n="name";e={$_.name}}, `
        @{n="mail";e={$_.mail}}, `
        @{n="office";e={$_.office}}
}
 
# Write the array to a file
$Users |  Export-CSV -Path $File

Open in new window

0
 
Chris DentPowerShell DeveloperCommented:

In AD, office is a field called "physicalDeliveryOfficeName". If you add that to your loaded properties, and modify the Select-Object statement you should find it springs to life.

You might be interested in the AD CmdLets Quest made if you end up doing a lot of this:

http://www.quest.com/activeroles-server/arms.aspx

That would allow the script above to be simplified to:

Get-QADUser | Select-Object Name, Mail, Office

Which can then be exported to a CSV if desired.

Chris
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Chris DentPowerShell DeveloperCommented:

Oh and just in case... The PowerShell TA is here:

http://www.experts-exchange.com/Programming/Languages/Scripting/MSH-Monad/

Might get you a quicker response in future, despite it's extremely out of date area name :)

Chris
0
 
wdurrettAuthor Commented:
Thank you for your response.

This is what i changed and it still comes up blank.
# Basic information to run the script
$File = "\\server\files\test\test.csv"
$SearchRoot = "LDAP://OU=Staff,DC=mydomain,DC=com"
 
 
# Filter to find the things we want
$Filter = "(objectClass=User)"
 
# Connect to the search root
$SearchRootDE = New-Object System.DirectoryServices.DirectoryEntry($SearchRoot)
 
# Set up the search
$Searcher = New-Object System.DirectoryServices.DirectorySearcher($SearchRootDE, $Filter)
$intProp = $Searcher.PropertiesToLoad.Add("name")
$intProp = $Searcher.PropertiesToLoad.Add("mail")
$intProp = $Searcher.PropertiesToLoad.Add("physicalDeliveryOfficeName")
 
 
# Get the results
$Results = $Searcher.FindAll()
 
# Sort through the results and save them in an array
$Users = @()
ForEach ($Result in $Results) {
  $Users += $Result.Properties | Select-Object `
        @{n="name";e={$_.name}}, `
        @{n="mail";e={$_.mail}}, `
        @{n="physicalDeliveryOfficeName";e={$_.physicalDeliveryOfficeName}}
}
 
# Write the array to a file
$Users |  Export-CSV -Path $File

Open in new window

0
 
wdurrettAuthor Commented:
Bingo!  Thank you so much!

What did you change?

0
 
Chris DentPowerShell DeveloperCommented:

The properties in the result set can only be accessed if you use lower case for the property names. That is "physicaldeliveryofficename" instead of "physicalDeliveryOfficeName". Doesn't matter how they appear in AD, it just seems to dislike mixing case there.

Can't say I know why :) I've never been bothered by it enough to find out, just made me use lower case there ever since I found it :)

Chris
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.