Generate a telephone list using Active Directory, Word, and a Mac. Ick! #mac #active_directory #ad #phone_list

generatePhoneList.sh

#!/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