How to extract data from AD?

[cbass]

I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

[Richard Mueller [MVP]]

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

You have a few options. The best command line utility would be Joe Richards'
free adfind. See this link:

http://www.joeware.net/freetools/tools/adfind/index.htm

You can also use Microsoft's "dsquery user". There is command line help the
command prompt of a DC (w2k3 or above), and also at the Micrsoft site.
Another alternative is a VBScript program using ADO. For details see this
link:

http://www.rlmueller.net/ADOSearchTips.htm

A VBScript example that uses ADO to retrieve information for all users
similar to what you want and outputs in csv format is linked here:

http://www.rlmueller.net/DocumentUsers.htm

To document when the account is created you would retrieve the value of the
whenCreated attribute.

[Arun Kumar]

Have you explored dsget.exe,dsquery.exe and csvde.exe? With CSVDE you can get
almost full AD export and with dsget.exe you can get most of "generally
required" attributes. dsquery "using *" is a generic ldap search tool using
which you can retrieve absolutely any information within AD including schema,
configuration, attributes etc.

e.g. to export all attributes of all users under OU "allusers" in domain
"example.com use -

csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv

exporting only user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))"

exporting just few attributes of user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))" -l samaccountname

using dsget -
dsget user "cn=test user,ou=allusers,dc=example,dc=com" -empid

using dsquery *-
dsquery * -filter "cn=test user,ou=allusers,dc=example,dc=com" -attr
samaccountname employeeid

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

[cbass]

Thank you, I did try the CSVDE and it is exporting information. But I do not
see a field based on the creation date of the user account or password
expiration setting. Do these fields exist?

Post by Arun Kumar
Have you explored dsget.exe,dsquery.exe and csvde.exe? With CSVDE you can get
almost full AD export and with dsget.exe you can get most of "generally
required" attributes. dsquery "using *" is a generic ldap search tool using
which you can retrieve absolutely any information within AD including schema,
configuration, attributes etc.
e.g. to export all attributes of all users under OU "allusers" in domain
"example.com use -
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv
exporting only user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))"
exporting just few attributes of user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))" -l samaccountname
using dsget -
dsget user "cn=test user,ou=allusers,dc=example,dc=com" -empid
using dsquery *-
dsquery * -filter "cn=test user,ou=allusers,dc=example,dc=com" -attr
samaccountname employeeid

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

[Richard Mueller [MVP]]

The following adfind command retrieves whenChanged, pwdLastSet, and
userAccountControl for all users in domain MyDomain.com (one line, watch
line wrapping):

adfind -b dc=MyDomain,dc=com -f
"(&(objectCategory=person)(objectClass))" -tdc -samdc pwdLastSet whenChanged
userAccountControl

The -tdc option converts the Integer8 attribute (pwdLastSet) into a
date/time value. The -samdc option decodes userAccountControl. See the link
I gave earlier. Just replace with the DNS name of your domain in the command
above. You can redirect to a text file. Use the -csv option for csv format
output.
--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

Post by cbass
Thank you, I did try the CSVDE and it is exporting information. But I do not
see a field based on the creation date of the user account or password
expiration setting. Do these fields exist?

Post by Arun Kumar
Have you explored dsget.exe,dsquery.exe and csvde.exe? With CSVDE you can get
almost full AD export and with dsget.exe you can get most of "generally
required" attributes. dsquery "using *" is a generic ldap search tool using
which you can retrieve absolutely any information within AD including schema,
configuration, attributes etc.
e.g. to export all attributes of all users under OU "allusers" in domain
"example.com use -
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv
exporting only user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))"
exporting just few attributes of user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))" -l samaccountname
using dsget -
dsget user "cn=test user,ou=allusers,dc=example,dc=com" -empid
using dsquery *-
dsquery * -filter "cn=test user,ou=allusers,dc=example,dc=com" -attr
samaccountname employeeid

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information.
How
would I go about retrieving this information? My AD environment is 2003.

[cbass]

I figured out my own question I wasn't logged in with admin rights so it
wasn't pulling all fields, i am now showing the created date and modified
date. thanks!

Post by Arun Kumar
Have you explored dsget.exe,dsquery.exe and csvde.exe? With CSVDE you can get
almost full AD export and with dsget.exe you can get most of "generally
required" attributes. dsquery "using *" is a generic ldap search tool using
which you can retrieve absolutely any information within AD including schema,
configuration, attributes etc.
e.g. to export all attributes of all users under OU "allusers" in domain
"example.com use -
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv
exporting only user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))"
exporting just few attributes of user accounts
csvde -d "ou=allusers,dc=example,dc=com" -f c:\some_file.csv -r
"(&(objectclass=user)(objectcategory=person))" -l samaccountname
using dsget -
dsget user "cn=test user,ou=allusers,dc=example,dc=com" -empid
using dsquery *-
dsquery * -filter "cn=test user,ou=allusers,dc=example,dc=com" -attr
samaccountname employeeid

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

[Sean Donaghey]

Try Paul Bergs site. He has a nice script that should do the job for you.

http://www.pbbergs.com/windows/downloads.htm#AcctAttrib

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.

[Jorge de Almeida Pinto [MVP - DS]]

ADFIND is a way to do that. Download it from joeware.net

for example

adfind -default -f
"(&(objectCategory=person)(objectClass=user)(!(userAccountControl:AND:=2)))"
sAMAccountName ATTRIBUTE ATTRIBUTE ETC
--
Cheers,
(HOPEFULLY THIS INFORMATION HELPS YOU!)

# Jorge de Almeida Pinto # MVP Identity & Access - Directory Services #

BLOG (WEB-BASED)--> http://blogs.dirteam.com/blogs/jorge/default.aspx
BLOG (RSS-FEEDS)--> http://blogs.dirteam.com/blogs/jorge/rss.aspx
------------------------------------------------------------------------------------------
* How to ask a question --> http://support.microsoft.com/?id=555375
------------------------------------------------------------------------------------------
* This posting is provided "AS IS" with no warranties and confers no rights!
* Always test ANY suggestion in a test environment before implementing!
------------------------------------------------------------------------------------------
#################################################
#################################################
------------------------------------------------------------------------------------------

Post by cbass
I need to create a report on users in Active Directory that contains
information such as the date their user account was created, whether their
account is disabled, password settings, and other user information. How
would I go about retrieving this information? My AD environment is 2003.