Batch script to export info from Active Directory (name, phone numbers, location, etc) into a csv file

Question

The secretary at my work uses a spreadsheet that lists names, phone numbers, and locations of employees to look up info if someone asks. All this info is located in AD, but the problem is that we are a rapidly growing company, and people change locations, job titles, and sometimes phone numbers quite often, so her spreadsheet is frequently out of date. I'm a low level tech, but my boss suggested to me that I should try and put together a batch script of some sort that she can run from her computer that goes into AD, pulls this info into a csv, and then throws that into an excel document.

I'm not too familiar with scripting (did a bit of python in school, but not a huge amount), so I guess I'm just looking for some pointers in the right direction. I pretty much get exactly what the script needs to do, I'm just not sure what commands are out there to interact with AD and pull info from it. Is it even possible to do this with a batch script? And would she need certain access into AD to run it from her computer?

Solution 1

Unless you're using Windows NT 4, I'd recommend using Windows PowerShell - it's powerfull and pretty easy to use (at least compared to VBScript, KiXtart etc.)

To interact with and retrieve data from Active Directory with PowerShell, you life will be a lot easier using the Active Directory Module for PowerShell.

With the AD Module in place, you can easily query AD for user data:

Import-Module ActiveDirectory

# define the attributes we are interested in, this is the ldap-display-name of the attributes
$InterestingAttributes = "displayName","physicalDeliveryOfficeName","telephoneNumber"
# Use Get-ADUser to retrieve all enabled users, Select-Object to filter out stuff like the object DN
$ADUsers = Get-ADUser -LDAPFilter "(!(userAccountControl:1.2.840.113556.1.4.803:=2))" -Properties $InterestingAttributes |Select-Object -Property $InterestingAttributes

$ADUsers | Export-Csv -Path "C:\Users\HerAccountName\Desktop\Users.csv" -NoTypeInformation

These attributes are all public, meaning that she can execute the script on her own, she won't need an admin to run it for her

Solution 2

If the secretary has Microsoft Office 2013 Professional Plus, Office 365 ProPlus or Excel 2013 Standalone she can use Excel Power Query to pull that information directly into Excel from Active Directory. From the Excel Power Query menu option you choose "From Other Sources" and then choose "From Active Directory". The data that is required can be selected from the Power Query editor to create a report that would be refreshable at any time. No additional rights should be required.

The Power Query add-in download is available here: https://www.microsoft.com/en-ca/download/details.aspx?id=39379excel-FX104018616.aspx

Solution 3

I can tell you how I solved a similar issue at a former employer. We were using Windows only to host AD and Exchange, and all the other servers in the company ran FreeBSD, so I developed our export automation using an open source tool called adtool which acts as an Active Directory client, and spits out lists or individual attributes. With adtool, after you've configured its access to your AD server per its documentation, you'd pull the basic list with a shell command like:

$ adtool list ou=user,dc=example,dc=com

For the additional info you need on each record, you can query the record and parse the results:

$ adtool attributeget <object> <attribute>

In a script this might look something like:

#!/bin/bash

fmt='"%s","%s","%s"\n'    # Grow as required

adtool list ou=user,dc=example,dc=com | sed -e 's/^\S*=/"/;s/,\S*/"/' | while read name; do
  sAMAccountName=$(adtool attributeget "$name" sAMAccountName)
  email=$(adtool attributeget "$name" mail)
  somethingelse=$(adtool attributeget "$name" somethingelse)
  # etc
  printf "$fmt" "$sAMAccountName" "$email" "$somethingelse" ...
done

This bash script is intended to send data to stdout, so it can be redirected to a file that's on a server or gets copied to your staff's workstation for import into excel. Note that it's not perfect. As it stands, it doesn't escape double quotes that might exist within field names. You can account for that yourself if it's a risk for you.

I'm afraid I don't have an AD server to test this with, so I can't hand you a complete solution, but if you decide to go this route and build your automation in Linux or Unix, I'd be happy to help you with the necessary text processing once I see the output of the basic adtool commands whose data you're parsing.

N.B.: You can probably also pull any data you want out of Active Directory using any LDAP client (like OpenLDAP which is available for almost every distro of linux and BSD). Adtool is just a convenience.

Comments

[Mark Allen]

Please see support.microsoft.com/kb/327620 Also technet.microsoft.com/en-us/library/cc778414(v=WS.10).aspx

[matt wilkie]

Good tutorial of how to build a query here: matt40k.uk/2016/06/… with code snippet for advanced editing at gist.github.com/matt40k/7ded896bf9fc14bc21602ceebf6be7dd