Extracting a list of all Users from your Active Directory

Last week, a colleague required a list of all active users from our Active Directory. This is relatively easy to do using the CSVDE tool available from Microsoft. The only caveat is that the AD needs to be kept up to date with the correct information. Garbage In, Garbage Out!

If you do a straight dump, using CSVDE –f c:\ADExport.csv you get everything, a list of all objects in your AD. Groups,  Computers, Containers, foreignSecurityPrincipal, publicFolder, etc.  EVERYTHING.

Note that the switch –f is to name the file and where you and it exported to.

So, you need to filter out the dump to show only users. Plus, we only want users, who are actually Users, so no Service Accounts, Builtin accounts, or Contacts. We need to filter on the following two AD attributes

objectClass=user  ; if you only filter on this it dumps both user and

objectCategory=person ; if you only filter on this, it dumps users and

You can filter rows using a –r switch, and to fine tune filtering on both object type, it would be

CSVDE -f c:\ADExport_Users.csv -r “(&(objectClass=user)(objectCategory=person))”

Another example would be to filter all users with a surname starting with C, and using wilcards like *

CSVDE -f c:\ADExport_Users_Surname_C.csv -r “(&(objectClass=user)(sn=C*))”

Additionally, when you do the dump, you get all of the AD attributes, DN, cn, objectGUID, objectClass etc. all across the top of the csv file. Good for referring to if you don`t know what exact attributes you want, but makes the csv file very unwieldy with A LOT of unnecessary information for the task at hand.

If you have your Active Directory laid out in a sensible manner, you can also target the export to specific OUs and their subOUs. To do this, you use the –d filter, specifying the DN of the OU you need to target, as follows.

CSVDE -f c:\ADExport_Users.csv -d “OU=Company_Users,DC=company,dc=local”

Now my colleague needed to know the users Distinguished Name, Display Name, account name, their internal phone extension number, their external telephone number, and their email address.  Matching each of these up to the AD attributes can be done fairly simply by finding the relevant field in the straight dump you did previously.

In our case, we needed the following fields:

DN           cn            sAMAccountName               mail         telephoneNumber               ipPhone

To do this, with use the –l (lowercase L) switch. CSVDE is supposed to be case insensitive, but this is what is listed in the help.

-l “sAMAccountName,cn,telephoneNumber,name,ipPhone,mail”

The order of filters is not important, as the dump will list the order that it comes out from AD. Additionally, DN will always be the first column.

So, putting all of these items together, we get the following.

CSVDE -f C:\ADExport_Users.csv -d “OU=Company_Users,DC=company,dc=local” -r “(&(objectClass=user)(objectCategory=person))” -l “sAMAccountName,cn,telephoneNumber,name,ipPhone,mail”

This gives us a nice dump of relevant information that I passed onto my colleague, who totally agreed that I`m AWESOME.

Twitter: @intmanofawesome