The fall semester arrives and with it the need to create CSV files

Imagine that you have an LDAP directory service provided by Microsoft’s Active Directory. You need to extract the phone numbers for two groups of users (faculty and staff) from this directory in some format that can be easily consumed by other tools. CSV is such a format. Here’s how to do it in very basic Python. If you look at the LDAP query, it’s got a couple of wrinkles – we’re doing an “or” query (GROUP A or GROUP B) and we’re only returning values where the telephoneNumber attribute has a value.


#! /usr/bin/python
######################
# Return the samaccountname and telephone number for members of G LAW STAFF
# or G LAW FACULTY
# with a non-empty telephonenumber attribute
# FIXME: It's not sorted by sAMAccountName.
######################

# The ldap query returns a list of tuples, where tuple[0] is the DN of the object
# and tuple[1] is a dictionary of attribute -> value entries
# like this:
# ('CN=XXXXXXX,OU=People,DC=Eu,DC=Emory,DC=Edu', {'telephoneNumber':# ['404-XXX-XXXX'], 'sAMAccountName': ['XXXXXXX']})

import ldap
import csv

server='ldaps://yourservernamehere.edu:636'
dn='ldapserviceaccount@yourservernamehere.edu'
pw='ldappass'
base_dn='ou=People,dc=Eu,dc=Emory,dc=Edu'
lfilter=r"(&(|(memberof=CN=G LAW STAFF,OU=Global Groups,OU=LAW,DC=Eu,DC=Emory,DC=Edu)(memberof=CN=G LAW FACULTY,OU=Global Groups,OU=LAW,DC=Eu,DC=Emory,DC=Edu))(telephonenumber=*))"
attrs=['sAMAccountName','telephoneNumber']

OUTFILE="ldapdata.csv"

l = ldap.initialize(server)
ret = l.simple_bind_s(dn,pw)
people = l.search_s(base_dn,ldap.SCOPE_SUBTREE, lfilter,attrs)
w = csv.writer(open(OUTFILE,'wb'))

for person in people:
        w.writerow([person[1]['sAMAccountName'][0].lower(),person[1]['telephoneNumber'][0]])
print "Wrote %s" % (OUTFILE)

There are a couple of other things worth pointing out. First, the csv module works on lists, so you need to create a list from the items that you want to write back to the file. In Python, that can be done by bracketing the returned strings. That accounts for the strange looking ([…]) in the writerow line above. Also, each value returned from the LDAP query is a list, so you have to explicitly tell it that you want the first element of the list. That’s why you see the [0] at the end of the field values. I’m sure there are more elegant ways to do this – share in the comments if you know of one.

As a side note, most of the time I work out my LDAP queries using ldapsearch at the command line – because the Python ldap library is a pretty thin wrapper, there’s almost a one-to-one correspondence between the ldapsearch approach and the version used in the Python program.

Advertisements

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