Take the 2-minute tour ×

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
add comment

2 Answers

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

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
add comment

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.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] }
} | Export-Csv names3.csv -NoTypeInformation
share|improve this answer
add comment

Your Answer


