Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am really struggling to create the query that will export the following: Account FirstName LastName the OU that the user resides in

Ideally I would like it as a CSV. I do not have access to the domain controller, but can run DSQUERY/DSGET/etc. from my pc.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Depending on your tools, you can do

dsquery user dc=contoso,dc=com | dsget user -samid -fn -ln -dn > names.csv 

That'll create a space-separated list of account name, firstname, lastname, and location.

Converting it into a true csv takes a bit more work.

$userList=dsquery user dc=contoso,dc=com | dsget user -samid -fn -ln -dn
foreach ($user in $userList) {
    $outstring=$user.trim(" ") -replace('\s+',',')
    write-host  `"$outstring`"
}

Which will get you output like

"samid","fn","ln","dn"
"jarey.boe","jarey","boe","cn=jarey.boe,ou=users,dc=contoso,dc=com"
share|improve this answer
    
What does the dc=contoso mean? –  Neil Oct 5 '12 at 23:03
    
@Neil It's part of the full Distinguished Name for the object. The dc bits are the DNS names for your AD domain-name. –  sysadmin1138 Oct 5 '12 at 23:49
    
If i can high 5 who provided the powerscript, i would. Your amazing and a life saver! –  user154774 Jan 16 '13 at 21:12

dsquery works but the conversion to CSV by splitting on one or more spaces (\s+ ) is tenuous. Any attribute value with embedded spaces is going to shift the remaining values to the right. Also, by default dsquery will stop after outputting 100 objects; "-limit 0" will output all results.

The correct way to do this via command line is using csvde:

csvde -f names.csv -r "(&(objectClass=user)(objectCategory=user))" -l samAccountName,givenName,sn

The obligatory PowerShell method is:

$a = [adsisearcher]'(&(objectClass=user)(objectCategory=user))'
$a.PageSize = 1000
$a.PropertiesToLoad.AddRange(@('samAccountName','givenName','sn','distinguishedName'))
$a.FindAll() | ForEach-Object {
    $b = $_.properties
    $op = '' | select DN,sAMAccountName,sn,givenName
    $op.DN = $b.distinguishedname[0]
    if ($b.samaccountname) { $op.samAccountName = $b.samaccountname[0] }
    if ($b.givenname) { $op.givenName = $b.givenname[0] }
    if ($b.sn) { $op.sn = $b.sn[0] }
    $op
} | Export-Csv names3.csv -NoTypeInformation
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.