Update Active Directory using Perl

Over the weekend, I received a phone call from my manager because there was an emergency to help out in a site migration that was happening somewhere in Texas. One of the things that was needed to be done is to import data from an Excel spreadsheet to a new active directory setup. Well, this is a pretty easy thing to do with perl.

Here's my solution:

#set_user_account.pl
#Program to import data to Active Directory
#by: Sam Dela Cruz
#date: 05/04/2007

use Win32;
use Win32::OLE qw(in);
use Win32::OLE::Enum;
use Spreadsheet::ParseExcel::Simple;

#list should be in the form of
#userid,lastname,firstname,middlename,organization,location,street,city,state,zip,phone,mobile,fax

my $data_source;
unless (@ARGV) {
    print "Enter input file: ";
    chomp ($data_source = );
}
else {
    $data_source = shift @ARGV;
}

my $xls = Spreadsheet::ParseExcel::Simple->read($data_source)
    or die "Cannot read $data_source: $!\n";

foreach my $sheet ($xls->sheets()){
    while ($sheet->has_data){
        my ($usb,
            $ln,
            $fn,
            $mn,
            $org,
            $location,
            $street,
            $city,
            $state,
            $zip,
            $phone,
            $mobile,
            $fax ) = $sheet->next_row;

        my $user;
        if($user = get_user($usb)){
            $User->GetInfo();

            #Set Info
            set_object_property($User,
                                "sn",
                                remove_before_after_space($ln)); #lastname
            set_object_property($User,
                                "givenName",
                                remove_before_after_space($fn)); #firstname
            set_object_property($User,
                                "initials",
                                remove_before_after_space($mn)); #middlename
            set_object_property($User,
                                "department",
                                remove_before_after_space($org)); #organization
            set_object_property($User,
                                "physicalDeliveryOfficeName",
                                remove_before_after_space($location)); #location
            set_object_property($User,
                                "streetAddress",
                                remove_before_after_space($street)); #street
            set_object_property($User,
                                "l",
                                remove_before_after_space($city)); #city
            set_object_property($User,
                                "st",
                                remove_before_after_space($state)); #state
            set_object_property($User,
                                "postalcode",
                                remove_before_after_space($zip)); #zip
            set_object_property($User,
                                "telephoneNumber",
                                remove_before_after_space($phone)); #phone
            set_object_property($User,
                                "mobile",
                                remove_before_after_space($mobile)); #mobile
            set_object_property($User,
                                "facsimiletelephonenumber",
                                remove_before_after_space($fax)); #fax

            unless ($User->SetInfo()){
                print "Uploaded,$usb,$ln,$fn,$mn,$org,$location,$street,$city,$state,$zip,$phone,$mobile,$fax\n";
            }
            else{
                print "Cannot upload $usb info: ".Win32::OLE->LastError."\n";
            }
        }
        else{
            print "Unable to open $usb\n";
        }
    }
}

sub get_user {
    my $usb = shift;
    my $user;
    my $ldap = "LDAP://cn=$usb,ou=corporate,ou=users,ou=corporate,dc=acme,dc=acme,dc=com";

    if($user = Win32::OLE->GetObject($ldap)){
        return $user;
    }
    return 0;
}

sub set_object_property {
    my ($object,$property,$value)=@_;
    if ($value) {
        print "Error: ".Win32::OLE->LastError."," if ($object->Put($property,$value));
    }
    else {
        print "Error: ".Win32::OLE->LastError."," if ($object->PutEx(1,$property,$value));
    }
}

sub remove_before_after_space {
    my $data = shift;
    $data =~ s/^\s+|\s+$//g;
    return $data;
}

There is just one gotcha. When setting up object property with the value of NULL or if you want to blank out that property, you will have to use a different method called PutEx with the ADS_PROPERTY_CLEAR = 1, thus:

Instead of

$object->Put($property,$value)

do

$object->PutEx(1,$property,$value)