Script to update zimbra ldap with csv/xls
From Federal Burro of Information
Jump to navigationJump to search
- 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").
#!/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;