Using Directory Services information to generate a Telephone List
Using Directory Services information to generate a Telephone ListMy organization creates a list of four digit extension numbers of all the employees in the organization, every year or so. The purpose of course is to provide a quick reference list, next to every telephone in the building. Until recently, the list has been maintained manually, and over time it has become an unruly mess of fonts, tabs and various styles. I have found it to be quite useless for some time.
Last year I decided to tackle this issue, and automate the list generation. My first attempt, was significantly better than the original manually generated list, but was less than ideal. It boiled down to running csvde with a long list of arguments on one of my domain controllers in order to generate a .csv file that contained more information than I needed. Then I moved that file to my mac, and through the use of some shell scripting I was able to trim the un-needed information out of the file and create a new, filtered, .csv file Following that, I was able to take the filtered .csv and run it through a mail merge document that I created in Word.
It worked, it generated the document exactly the way I wanted it. However, there were a few things I didn't like. My biggest gripe was that I had to use both the Win 2K3 Domain Controller (to generate the first file) and then had to use my Mac to process that file and ultimately generate the word document.
Over Christmas break this year, I set out to resolve that issue, and do some learning on a couple of topics that have interested me for a while now.
Apple has provided a command line utility called dscl (Directory Services Command Line) for at least the last two major releases of OS X. I have only just learned what it can do for me. I found it to be quite useful in pairing down the number of steps required to generate the telephone list.
Here is a copy of the script, fairly well commented, for ease of understanding.
#!/bin/bash
# Phone List Generator v. 2 by bill_wellington at aw.org
# This script will pull live data from Active Directory and output a .csv
# file (named as an argument) including first name, last name, department,
# phone ext. and mobile phone ext. It goes through a strips out all
# constituents who are students, members of the "Class of...", or who do
# not have a 4 digit extension listed in either their phone number or
# their mobile number. The list can then be used to generate a phone list
# using MS Word's mail merge functions.
# create file
touch /tmp/$1.csv
echo > /tmp/$1.csv
# pull a plist for every user in AD
for i in `dscl /Active\ Directory/All\ Domains/ -list /Users` ; do
dscl -plist /Active\ Directory/All\ Domains/ -read /Users/${i} FirstName LastName Department PhoneNumber MobileNumber | \
# remove lines matching following regex - these extra xml delimiters that we will not use
grep -v '^<?xml.*$' | \
grep -v '^<!DOC.*$' | \
grep -v '^<plist.*$' | \
grep -v '^</plist>' | \
grep -v '<key>.*$' | \
# remove xml tags around the data we want to keep, insert commas between fields
sed -e '/<dict>/d' -e 's@</dict>@@' \
-e '/<array>/d' -e 's@</array>@@' \
-e 's/<string>//' -e 's@</string>@,@' | \
# remove tab characters (0x09) from original xml output
sed -e 's/ //g' | \
# clean up ampersands
sed -e 's/&/\&/g' | \
# remove line breaks
sed -n -e ":a" -e "$ s/\n//gp;N;b a" | \
# remove students and all other without 4 digit extension
grep -v "Student" | \
grep -v "Class of " | \
grep '^.*[0-9][0-9][0-9][0-9].*$' | \
# ok, now we have the data we want, let's put the fields in the right order
sed -e 's/\([0-9][0-9][0-9][0-9],\)\([0-9][0-9][0-9][0-9],\)/\2\1/' | \
sed -e "s|\([a-zA-Z \&'/-]*,\)\([a-zA-Z ]*,\)\([a-zA-Z '-]*,\)\(.*\)|\3\2\1\4|" | \
# remove trailing commas from lines with a cell number
sed -e 's|\(.*,.*,.*,.*,.*\),|\1|' >> /tmp/$1.csv
done
# prepare final .csv
echo "Last Name, First Name, Department, Ext., Cell" > $1.csv
sort -f /tmp/$1.csv | \
#remove blank lines
sed -e '/^$/d' >> $1.csv
# add in static numbers
echo "Activity Bus 1,,,,5448" >> $1.csv
echo "Activity Bus 2,,,,5449" >> $1.csv
echo "Auction Office,,,5454," >> $1.csv
echo "AWSPA Office,,,5454," >> $1.csv
echo "Bishop's Suite,,,5409," >> $1.csv
echo "College Counseling,,,4339," >> $1.csv
echo "Courtesy Phone,,,8604," >> $1.csv
echo "Dorm Parent Office,,,5423," >> $1.csv
echo "Dorm Parent Cell 1,,,,5446" >> $1.csv
echo "Dorm Parent Cell 2,,,,5447" >> $1.csv
echo "Extended Day,,,8627,5445" >> $1.csv
echo "Front Office,,,8642," >> $1.csv
echo "HelpDesk - Tech,,,8999," >> $1.csv
echo "Housekeeping (Daytime),,,,5438" >> $1.csv
echo "Kitchen,,,4153," >> $1.csv
echo "Meeting Rooms,,,," >> $1.csv
echo "Conference Room,,,8893," >> $1.csv
echo "Cottage 1st Floor,,,8894," >> $1.csv
echo "Cottage 2nd Floor,,,8895," >> $1.csv
echo "Huston Room,,,8891," >> $1.csv
echo "Sutton Room,,,8892," >> $1.csv
echo "Security,,,5426,5444" >> $1.csv
# Future Plans This scrpt perhaps through some fancy "osascript" commands
# should be able to launch MS Word, and perform the mail merge. I need to
# check and see if Word's Mail Merge functions are scriptable using
# AppleScript.
Posted on Sun, 03 Jan 2010 21:25:27 -0800 in
AWS
•
Network Administration
•
Phones
•
Technology
•
Apple Computer
•
Macintosh
•
OS X
•
Open Directory
•
Microsoft
•
Windows
•
Server 2003
•
Active Directory
Permalink