View source for Script to update zimbra ldap with csv/xls

* convert xsl to csv
* if "values" have commas, do some vi trickery to use something other than "," as delmiter ( the perl is easy to change, search "split").

<pre>

#!/usr/bin/perl

use strict;
use Net::LDAP;
use Data::Dumper;

my $VERBOSE = 0;

my $host = "host";
my $base = "ou=people,dc=domain,dc=ca";
my $mydn = "uid=zimbra,cn=admins,cn=zimbra";
my $pass = "XXX";
my $ldap ;
my $mesg;
my $update = 1; # set to one to enable updates

my $csvfile = "contactlist.csv";
my @ldapattr = qw/company l st postalCode street telephoneNumber mobile/;

$ldap = Net::LDAP->new($host) or die "Can't bind to ldap: $!\n";

$VERBOSE && print "Binding...\n";
$mesg = $ldap->bind( $mydn, password => $pass );
$mesg->code && die $mesg->error;
$VERBOSE && print "Bound ok\n";

$VERBOSE && print "opening csv file ..";
open( FILE, "< $csvfile" ) or die "Can't open $csvfile : $!";
while( <FILE> ) {
    next if /^#/;

    # You will need to edit this to match the way you csv is, some examples:
    # First Name,Last Name,Job Title,Office Phone,Mobile Phone,E-mail Address,Department
    # Display - Common Name,Email,Last Name,First Name,Company,City,Province,Postal Code,Address,Office,Mobile,Fax
    my %csv ;
    chomp;

    ($csv{'displayname'},$csv{'zimbraMailDeliveryAddress'},$csv{'sn'},$csv{'givenName'},$csv{'company'},$csv{'l'},$csv{'st'},$csv{'postalCode'},$csv{'street'},$csv{'telephoneNumber'}, $csv{'mobile'},$csv{'facsimiletelephonenumber'}) = split ( /#/ , $_ );
    $csv{'company'} = "Alpha Group";
    print "Looking for $csv{'givenName'}, $csv{'sn'} ($csv{'zimbraMailDeliveryAddress'})\n";
    my($mesg) = $ldap->search( base => $base,
        # filter => "(&(givenName=*$fname*)(sn=*$lname*))"
        filter => "(&(zimbraMailDeliveryAddress=$csv{'zimbraMailDeliveryAddress'}))"
        # filter => "(&(givenName=$csv{'givenName'})(sn=$csv{'sn'}))"
    );
    $mesg->code && warn $mesg->error;
    my $max = $mesg->count;
    # print "Number of entries found for $csv{'zimbraMailDeliveryAddress'}: $max\n";
    if ( $max == 0 ) {
        print "No $csv{'zimbraMailDeliveryAddress'} found!\n";
    }elsif ( $max > 1 ) {
        print "More than one $csv{'zimbraMailDeliveryAddress'} found!\n";
    } else {
        print "Only one $csv{'givenName'}, $csv{'sn'} found!\n";
        my $entry = $mesg->shift_entry ;
        my $dn  = $entry->dn();
        if ( $entry ) {
            foreach my $required (@ldapattr) {
                # print "Looking for $required attr in entry\n";
                if ( grep $_ eq $required , $entry->attributes) {
                    # print "found attr $required\n";
                    my ($ldapvalue)  = $entry->get( $required );
                    if ( $csv{$required} eq $ldapvalue) {
                        # print "They match, s\'all good\n";

                    } else {
                        print "$csv{'givenName'}, $csv{'sn'} $required don\'t match (xls:$csv{$required} ldap:$ldapvalue : UPDATE\n";

                        ################
                        if ( $update ) {
                        ################

                        if ( $required eq 'company' ) {
                            $entry->replace ( company => $csv{'company'} );
                            $mesg = $entry->update ( $ldap );
                            if ( $mesg->code ) {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update no good\n";
                                print "Error:".$mesg->error,."\n";
                            } else {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update ok\n";
                            }

                        }
                        if ( ( $required eq 'telephoneNumber' ) || ( $required eq 'mobile' ) ) {
                            $entry->replace ( $required => $csv{$required} ) ;
                            $mesg = $entry->update ( $ldap );
                            if ( $mesg->code ) {
                                print $csv{'zimbraMailDeliveryAddress'}." ".$required." (".$csv{$required}.") Update no good\n";
                                print $mesg->error;
                            } else {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update ok\n";
                            }
                        }
                        if ( $required eq 'title' ) {
                            $entry->replace ( title => $csv{'title'} ) ;
                            $mesg = $entry->update ( $ldap );
                            if ( $mesg->code ) {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update no good\n";
                                print $mesg->error;
                            } else {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update ok\n";
                            }

                        }

                        if ( $required eq 'street' ) {
                            print "required is street\n";
                            $entry->replace ( street => $csv{'street'} ) ;
                            $mesg = $entry->update ( $ldap );
                            if ( $mesg->code ) {
                                print $csv{'zimbraMailDeliveryAddress'} ." ". $required ." (".$csv{'street'}.") Update no good\n";
                                print $mesg->error;
                            } else {
                                print "$csv{'givenName'}, $csv{'sn'} $required Update ok\n";
                            }
                            $entry->replace ( title => '' ) ;
                        }

                        ################
                        }
                        ################

                    }
                } else {
                    if ( $csv{$required} ) {
                        print "this entry doesn't have a $required, adding \n";
                        ################
                        if ( $update) {
                        ################

                        $entry->add ( $required => $csv{$required} );
                        # print "Add done, updating ... \n";
                        $mesg = $entry->update ( $ldap );
                        # print "Success?\n";
                        if ( $mesg->code ) {
                            print $csv{'zimbraMailDeliveryAddress'}." ". $required. " (".$csv{$required}.") Update no good\n";
                            print $mesg->error;
                        } else {
                            print "$csv{'givenName'}, $csv{'sn'} $required Update ok\n";
                        }

                        ################
                        }
                        ################

                    } else {
                        print "No $required in csv file\n";
                    }
                }
            }
        }

        #$entry->replace ( title => '' ) ;
        $entry->delete ( 'title' );
        $mesg = $entry->update ( $ldap );
        if ( $mesg->code ) {
            print "failed to blank out title\n";
            print $mesg->error."\n";
        }
        #my ($ldapoffice) = $entry->get( 'telephoneNumber' );
        #my ($ldapmobile) = $entry->get( 'mobile' );
        #my ($ldapdept)   = $entry->get( 'ou' );
        #my ($ldaptitle)  = $entry->get( 'jobTitle' );

        #printf 'Varaible %-32s %-32s'."\n" , "Csv" , "Ldap" ;
        #printf 'office:  %-32s %-32s'."\n" , $office , $ldapoffice;
        #printf 'mobile:  %-32s %-32s'."\n" , $mobile , $ldapmobile;
        # array of one
        #my @uid = $entry->get( 'uid' );
        #printf 'email:   %-32s %-32s'."\n" , $email , "@uid\@scalar.ca";
    }
}
close FILE;

$mesg = $ldap->unbind;

</pre>