Exporting from Active Directory

I recently needed to get a dump of users from Active Directory to analyse the department field values because we changed some recently and now some users have lost access to the call logging system we use (Vivantio).

This was surprisingly fiddly to achieve with the tools available to me, so I thought I’d write it up for future reference.

You can get at the users via the built-in Active Directory Users & Computers tool (dsa.msc). This will already be on Windows servers running Active Directory (domain controllers for example), but can also be installed onto a workstation via Remote Server Admin Tools (RSAT).

Using Active Directory Users & Computers, select a container in the AD tree. If your users are in multiple containers (multiple organisational units (OUs) for example) then you’ll need to repeat this for each as I didn’t find a way to list several at once.

To get the department field showing (there are lots of others available too, but not everything) use View > Add/Remove Columns.

To remove groups and disabled users (for example) use View > Filter Options. The tool will help you to build simple queries, but to screen out the disabled users we need to use an LDAP query, so choose Create Custom Filter > Customized > Advanced and enter the following:

(&(objectCategory=User)(!userAccountControl:1.2.840.113556.1.4.803:=2))

You can then export the users to file by right-clicking on the selected container and choosing Export List. I changed the file format to Unicode Text (Tab Delimited). I tried Comma Delimited first, but some of our departments contain commas which messed up the import into Excel.

I opened the file with Excel (via File > Open) which walked me through the import process.

I inserted a Pivot Table and dragged Department into the Row Labels box and Name into the Values box (this was automatically converted to “Count of Name” by Excel). Excel lists all the departments and the count of users in that department. Double-clicking a department name will ask what to display next (or just drag another row name into the Row Labels box). I chose Display Name, so expanding a department lists the names of the people in it.

From this it was easy to spot users that had incorrect or unusual department values.