How can I eport a list of all user accounts from Active Directory to a CSV file?

xounox
xounox used Ask the Experts™
on
I've been asked if it is possible to export to a spreadsheet in .CSV format a list of all of our user accounts from Active Directory.  

The resulting list must contain:

First Name
Surname
Middle Initial
User ID
E-Mail Address
Description
Office

We use Windows 2003 server for our domain controllers.  Thank you for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Many way to do this.
joeware.net - adfind

adfind -default -f "&(objectcategory-person)(objectclass=user)" givenname, sn, initial,  samaccountname,mail,description, office -csv -nolable -nodn

Quest AD cmdlets with powershell

get-qaduser | select-object firstname, lastname, initial, email, description, office | export-csv c:\users.csv

Author

Commented:
Thank you KenMcF, i'm trying adfind but I receive the following output:

ERROR: Bad Command Line Arg(s)
ERROR:  nolable

So I tried it without the -nolable command switch and received the following output:

"givenname,","sn,","initial,","samaccountname,mail,description,","office"
ldap_get_next_page_s: [servername..xxx.xxx] Error 0x57 (87) - Filter Error

Commented:
sorry, typo should be -nolabel

and
objectcategory-person

should be
objectcategory=person
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

Commented:
and another thing I just saw, no spaces between the commas for the attributes it is getting

Author

Commented:
Mmm, using the command:
adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname,sn,initial,samaccountname,mail,description,office -csv -nolabel -nodn

I see a lot of inverted brackets like below and I'm then returned to the command prompt, but there doesn't appear to be an outputted file?

""
""
""


Author

Commented:
Ah! I used the following command without any spaces:
 
adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname,sn,initial,samaccountname,mail,description,office-csv-nolabel-nodn

This does return a list of all the accounts in the command prompt but again without the outputted csv file

Commented:
Add this to the end

>C:\users.csv
PberSolutions Architect

Commented:
There is also the csvde tool from M$ that does a similar job:
http://www.computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm 

Author

Commented:
OK almost there!

The command: adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname,sn,initial,samaccountname,mail,description,office-csv -nolabel -nodn >C:\users.csv

Created a CSV with the total number of Objects returned

The command:
adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname,sn,initial,samaccountname,mail,description,office-csv >C:\users.csv

Successfully created a populated CSV File but was missing some of the fields. The fields included were Surname,First Name, OU container.

Commented:
Surnamae shoud ne sn
First name should be firstname
And for ou you could get dn and parse it out

Author

Commented:
Just to provide a flavor of the outputted CSV file It looks a bit like this:
 
Using server: servername.xxxx.xx.xx:xx                        
Directory: Windows Server 2003 R2                        

dn:CN=account name, CN=Users, DC=xxxx, DC=xxxx, DC=xx
etc....

This is using the command: adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname,sn,initial,samaccountname,mail,description,office-csv >C:\users.csv

This is a good start but I still require the other fields of information.

Commented:
You can add any attribute in the list the ones you want would be

•First Name - GivenName


•Surname - sn


•Middle Initial - initial


•User ID - samaccountname


•E-Mail Address - mail


•Description - description


•Office - physicalDeliveryOfficeName

Author

Commented:
Sorry KenMcF, i should have been more clearer, the only information thats returned in the CSV file is the account name and the OU Location in the tree.

Thank you  

Commented:
The attributes I gave are the defaults. Can you try this on your account

adfind -default -f samaccountname=YOURLOGONNAME

this will give you all the attributes on your account

Author

Commented:
Thanks, I can confirm this works as expected - it returns a huge amount of useful information.  

Based on your assistance,  I got a little creative and tried: adfind -default -f "&(objectcategory=person)(objectclass=user)" >C:\allusers.csv

This gave all the information possible for each AD account.  This is great as it answers my question but its a trifle difficult to traverse.  Is it possible to narrow it down to show only the information required?
Commented:
Yeah, you can use the -csv like my first example and put in whatever attribute you want after the filter

like this

givenname,sn,initial,samaccountname,mail,description,office-csv >C:\users.csv

change any of these or add any you want and they will all be in the csv file

Author

Commented:
Right I tested the command without using any commas in-between attributes and...... hey presto!

This is the command that gave me the information required:
adfind -default -f "&(objectcategory=person)(objectclass=user)" givenname sn initial samaccountname mail description office -csv >C:\userstest2.csv

KenMcF, thanks for your help today you have saved me a lot of time - points are on their way!

Commented:
Glad I could help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial