Link to home
Start Free TrialLog in
Avatar of xounox
xounox

asked on

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

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.
Avatar of KenMcF
KenMcF
Flag of United States of America image

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
Avatar of xounox
xounox

ASKER

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
sorry, typo should be -nolabel

and
objectcategory-person

should be
objectcategory=person
and another thing I just saw, no spaces between the commas for the attributes it is getting
Avatar of xounox

ASKER

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?

""
""
""


Avatar of xounox

ASKER

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
Add this to the end

>C:\users.csv
Avatar of Pber
There is also the csvde tool from M$ that does a similar job:
http://www.computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm 
Avatar of xounox

ASKER

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.
Surnamae shoud ne sn
First name should be firstname
And for ou you could get dn and parse it out
Avatar of xounox

ASKER

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.
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
Avatar of xounox

ASKER

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  
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
Avatar of xounox

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of KenMcF
KenMcF
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of xounox

ASKER

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!
Glad I could help