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:
We use Windows 2003 server for our domain controllers. Thank you for your help.
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.
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,","initia l,","samac countname, mail,descr iption,"," office"
ldap_get_next_page_s: [servername..xxx.xxx] Error 0x57 (87) - Filter Error
ERROR: Bad Command Line Arg(s)
ERROR: nolable
So I tried it without the -nolable command switch and received the following output:
"givenname,","sn,","initia
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
objectcategory-person
should be
objectcategory=person
and another thing I just saw, no spaces between the commas for the attributes it is getting
ASKER
Mmm, using the command:
adfind -default -f "&(objectcategory=person)( objectclas s=user)" givenname,sn,initial,samac countname, mail,descr iption,off ice -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?
""
""
""
adfind -default -f "&(objectcategory=person)(
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?
""
""
""
ASKER
Ah! I used the following command without any spaces:
adfind -default -f "&(objectcategory=person)( objectclas s=user)" givenname,sn,initial,samac countname, mail,descr iption,off ice-csv-no label-nodn
This does return a list of all the accounts in the command prompt but again without the outputted csv file
adfind -default -f "&(objectcategory=person)(
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
>C:\users.csv
There is also the csvde tool from M$ that does a similar job:
http://www.computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm
http://www.computerperformance.co.uk/Logon/Logon_CSVDE_Export.htm
ASKER
OK almost there!
The command: adfind -default -f "&(objectcategory=person)( objectclas s=user)" givenname,sn,initial,samac countname, mail,descr iption,off ice-csv -nolabel -nodn >C:\users.csv
Created a CSV with the total number of Objects returned
The command:
adfind -default -f "&(objectcategory=person)( objectclas s=user)" givenname,sn,initial,samac countname, mail,descr iption,off ice-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.
The command: adfind -default -f "&(objectcategory=person)(
Created a CSV with the total number of Objects returned
The command:
adfind -default -f "&(objectcategory=person)(
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
First name should be firstname
And for ou you could get dn and parse it out
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)( objectclas s=user)" givenname,sn,initial,samac countname, mail,descr iption,off ice-csv >C:\users.csv
This is a good start but I still require the other fields of information.
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)(
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
•First Name - GivenName
•Surname - sn
•Middle Initial - initial
•User ID - samaccountname
•E-Mail Address - mail
•Description - description
•Office - physicalDeliveryOfficeName
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
Thank you
The attributes I gave are the defaults. Can you try this on your account
adfind -default -f samaccountname=YOURLOGONNA ME
this will give you all the attributes on your account
adfind -default -f samaccountname=YOURLOGONNA
this will give you all the attributes on your account
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)( objectclas s=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?
Based on your assistance, I got a little creative and tried: adfind -default -f "&(objectcategory=person)(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)( objectclas s=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!
This is the command that gave me the information required:
adfind -default -f "&(objectcategory=person)(
KenMcF, thanks for your help today you have saved me a lot of time - points are on their way!
Glad I could help
joeware.net - adfind
adfind -default -f "&(objectcategory-person)(
Quest AD cmdlets with powershell
get-qaduser | select-object firstname, lastname, initial, email, description, office | export-csv c:\users.csv