LDAP2CSV



By: jimsc

March 15, 2005 5:35 pm

Reads: 187

License:
Free

Download ldap2csv.zip

What makes this tool unique is that it can handle attributes that have multiple values. The administrator specifies the attributes and the maximum count for each one. The program discards values that exceed the count and provides null values for attributes that don’t match the maximum count. You run the utility like this:

ldap2csv.pl -h < host> -D < user> -w < password> -b < base> -f < attributes file> < filter>

Example:
ldap2csv.pl -h 192.168.1.2 -D cn=admin,o=novell -w novell -b ou=people,o=novell -f attrs.txt objectclass=user

  1. The user and password are optional. If not specified, an anonymous bind will be done.
  2. The attributes file must be in this format:
  3. attribute_name, count
    attribute_name, count

    Here’s an example attributes file:

    description,1
    cn,2
    mail,3
    sn,1
    fullname,1
    telephonenumber,1

  4. You’ll need to download and install the perl-ldap-0.3202 module from www.cpan.org/. (This is already present on NetWare 6.5.)

Platforms: I’ve tested it on SLES9 and NetWare 6.5.

VN:F [1.9.22_1171]
Rating: 0.0/5 (0 votes cast)

Tags: ,
Categories: Cool Tools, eDirectory

Disclaimer: As with everything else at NetIQ Cool Solutions, this content is definitely not supported by NetIQ, so Customer Support will not be able to help you if it has any adverse effect on your environment.  It just worked for at least one person, and perhaps it will be useful for you too.  Be sure to test in a non-production environment.

1 Comment

  1. By:nathanshaw

    I stumbled across this the other day and fixed it up to suit my needs. Just thought I’d post back the updated code in case anyone else wants it. I know there are zillions of ways to get data out of LDAP but I liked the way Jim (original author) was handling multi-valued attributes.

    I added the following features/fixes:

    • Uses Net::LDAPS instead of Net::LDAP. Use -s to specify using SSL.
    • Allows user to specify port (uses the default port for the method used if not specified).
    • Printing the header is now off by default. Use -H to turn it on.
    • Changed output of column names. No more number after each attribute name for the first value of any attribute. If there are multiple values for an attribute, those above 1 will have an enumeration within square brackets. Example: description,description[2],description[3]
    • Added a debug switch (-d). All debug output is sent to STDERR so that you can redirect STDOUT and still just get the CSV data.
    • If you specify a username, but no password (-w) the script will prompt you for the password.
    • Fixed problem where attributes were reordered in the output instead of matching the order from the attribute file.
    • Added more information when bind fails.

    #!/usr/bin/perl
    
    # Originally written by Jim Schnitter
    # http://www.novell.com/coolsolutions/author/527.html
    # Retrieved from: http://www.novell.com/coolsolutions/tools/14487.html
    #
    # Version: 1.5 by Nathan Shaw
    #
    # Usage: ldap2csv.pl -h <host> -p <port> -D <user> -w <password> -b <base> -f <attributes file> <filter>
    #
    # Purpose: Performs an LDAP/SSL Search and formats the output into a csv file.
    # The user can specify which attributes and how many values to return.
    # Create a file and put an attribute name on each line followed by a comma
    # and how many values to return (for multiple attributes).
    #
    # Changes from 1.0 - 1.5
    #   Uses Net::LDAPS instead of Net::LDAP.  Use -s to specify using SSL.
    #   Allows user to specify port (uses the default port for the method used if not specified).
    #   Printing the header is now off by default.  Use -H to turn it on.
    #   Changed output of column names.  No more number after each attribute name for the first value of any attribute.  If there are multiple values for an attribute, those above 1 will have an enumeration within square brackets.  Example: description,description[2],description[3]
    #   Added a debug switch (-d).  All debug output is sent to STDERR so that you can redirect STDOUT and still just get the CSV data.
    #   If you specify a username, but no password (-w) the script will prompt you for the password.
    #   Fixed problem where attributes were reordered in the output instead of matching the order from the attribute file.
    #   Added more information when bind fails.
    
    use strict;
    use Getopt::Std;
    use Net::LDAPS;
    use Net::LDAP::Util qw(ldap_error_text);
    
    my %options = ();
    getopts('h:p:sD:w:b:f:dH', \%options);
    
    my $opt_server;
    my $opt_port;
    my $opt_user;
    my $opt_pw;
    my $opt_base;
    my $opt_filter;
    my $opt_ssl;
    my $opt_header;
    my $DEBUG;
    
    if (defined $options{d}) {
            print STDERR "\nDEBUG MODE ON\n";
            $DEBUG = 1;
    }
    
    if (defined $options{h}) {
            $opt_server = $options{h};
    } else {
            print STDERR "$options{h}\n" if $DEBUG;
            print STDERR "\nNo server defined!\n" if $DEBUG;
            usage();
    }
    
    if (defined $options{D} && defined $options{w}) {
            $opt_user = $options{D};
            $opt_pw = $options{w};
    }
    
    if (defined $options{H}) {
            $opt_header = 1;
    }
    
    if (defined $options{s}) {
            $opt_ssl = 1;
    }
    
    if (defined $options{p}) {
            $opt_port = $options{p};
    } else {
            $opt_port = 389 if !$opt_ssl;
            $opt_port = 636 if $opt_ssl;
    }
    
    if (defined $options{D}) {
            $opt_user = $options{D};
    }
    
    if (defined $options{b}) {
            $opt_base = $options{b};
    } else {
            print STDERR "\nNo base defined!\n" if $DEBUG;
            usage();
    }
    
    
    # Initialize the attribute hash from the file given on the command line.
    
    my %attrcount = ();
    my @attrorder;
    #print STDERR "\n# Original Attributes:\n" if $DEBUG;
    if (defined $options{f}) {
            open (ATTRFILE, $options{f}) or die "Can't open $options{f}: $!\n";
    
            while (<ATTRFILE>) {
                    chomp;
                    my ($attr, $num) = split(/,/);
                    push(@attrorder, $attr);
    #               print STDERR "$attr\n" if $DEBUG;
                    $attrcount{$attr} = $num;
            }
            close (ATTRFILE);
    
    } else {
            print STDERR "\nNo file defined!\n" if $DEBUG;
            usage();
    }
    
    # ARGV should only contain filter, after calling getopt()
    if (@ARGV == 1) {
            $opt_filter = $ARGV[0];
    } else {
            print STDERR "\nNo bueno defined!\n" if $DEBUG;
            usage();
    }
    
    if ($DEBUG) {
            print STDERR "\n# Connection Details:\n";
            print STDERR "mode:\tLDAPS\n" if $opt_ssl;
            print STDERR "mode:\tLDAP\n" if !$opt_ssl;
            print STDERR "server:\t$opt_server\n";
            print STDERR "port:\t$opt_port\n";
            print STDERR "user:\t$opt_user\n";
            print STDERR "base:\t$opt_base\n";
            print STDERR "filter:\t$opt_filter\n";
            print STDERR "\n";
    }
    
    if ($opt_user && defined $options{w}) {
            $opt_pw = $options{w};
    } else {
            $opt_pw = getPrivate("Password: ");
    }
    
    my $ldap;
    $ldap = Net::LDAP->new($opt_server, port=> $opt_port) or die "$@" if !$opt_ssl;
    $ldap = Net::LDAPS->new($opt_server, port=> $opt_port) or die "$@" if $opt_ssl;
    
    # User and password are optional.  If not set, an anonymous bind is done.
    
    if (defined $opt_user && defined $opt_pw) {
            my $mesg = $ldap->bind  ($opt_user, password => $opt_pw, version => 3);
            if ($mesg->code) {
                    my $message = ldap_error_text($mesg->code);
                    die ("Can't bind as \"$opt_user\" (LDAP Error: ", $mesg->code, ")\n$message");
            } else {
                    print STDERR "# Bind successful!\n" if $DEBUG;
            }
    }
    
    LDAPsearch($ldap, $opt_filter, \%attrcount, $opt_base);
    
    sub usage {
            die ("\n\nUsage: ldap2csv.pl -h <host> [-p <port>] [-s] [-D <BindDN> [[-w <password>]] -b <base> [-d] -f <file> <filter>\n  h: LDAP Server (req'd)\n  p: Port\n  s: Use SSL\n  D: Bind DN\n  w: Bind Password\n  b: BaseDN for search (req'd)\n  f: Attributes file (req'd)\n");
    }
    
    sub LDAPsearch {
            my ($ldap,$searchString,$attrdata,$opt_base) = @_;
    
            my $result = $ldap->search (base => "$opt_base", scope   => "sub", filter  => "$searchString");
    
            if ($result->code) {
                    die ("Can't search $opt_base (LDAP Error: ", $result->code, ")\n");
            } else {
                    print STDERR "# Search successful!\n" if $DEBUG;
            }
    
    
            if ($opt_header) {
                    # Print Header
                    my $header;
                    foreach my $column (@attrorder) {
                            my $valmax = $$attrdata{$column};
    
                            for (my $i = 1; $i <= $valmax; ++$i) {
                                    $header = $header . "\"$column\[$i\]\"," if $i != 1;
                                    $header = $header . "\"$column\"," if $i == 1;
                            }
                    }
                    $header =~ s/,$//;
                    print "$header\n";
            }
    
    
            my @entries = $result->entries;
            my $entr;
    
            # Cylce through search results...
            foreach $entr ( @entries ) {
                    my $attr;
    
                    # Cycle through each attribute...
                    my $record;
                    foreach $attr ( @attrorder ) {
    
                            # If the entry has this attribute on it...
                            if ($entr->exists($attr)) {
    
                                    my @values = $entr->get_value($attr);
                                    my $valmax = $$attrdata{$attr}; # Maximum number of values to return from this attribute
    
                                    for (my $i = 0; $i < $valmax; ++$i) {
    
                                            if ( $values[$i] ) {
                                                    $record = $record . "\"$values[$i]\",";
                                            } else {
                                                    $record = $record . ",";
                                            }
                                    }
                            # If the attribute isn't available...
                            } else {
                                    # Add columns for each missing attribute
                                    my $valmax = $$attrdata{$attr};
                                    for (my $i = 0; $i < $valmax; ++$i) {
                                            $record = $record . "\"\",";
                                    }
                            }
                    }
                    $record =~ s/,$//;
                    print "$record\n";
            }
    }
    
    sub getPrivate() {
            print STDERR "$_[0]" if $_[0];
            print STDERR "Input: " if !$_[0];
            system("stty -echo");
            my $private=<STDIN>;
            system("stty echo");
            chomp($private);
            if (!$private) {
                    usage();
            } else {
                    print STDERR "*******\n";
            }
            return $private;
    }
    

Comment