International Man of Awesome's Blog – When Too Much Awesome Isn't Enough

September 9, 2010

Extracting a list of all Users from your Active Directory

Filed under: Active Directory, Microsoft, Scripting, Windows, Windows 2003, Windows 2008 R2 — internationalmanofawesome @ 11:17 am

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

Advertisements

1 Comment »

  1. what if I wanted a column to list if the account is currently disabled or not? how would I list that? I am using a 2003 DC.

    Comment by Buddy Farr — November 26, 2013 @ 5:22 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: