Linux LDAP Tutorial: Support scripts and software tools for OpenLDAP

LDAP database dump to CSV:

Script to dump the LDAP database into CSV (Comma Separated Variables) format:

If you wish to allow others access to the entire database in a form that other programs such as spread sheets and databases can easily read, dump the LDAP database to a CSV format.

1#!/bin/sh
2#
3# Dump LDAP database
4/usr/sbin/ldbmcat -n /var/lib/ldap/stooges/id2entry.gdbm > /home/dbdumps/stooges-`date +%m%d%y`.ldif
5sleep 10
6#
7# Convert ldif format to  csv
8/bin/awk -F ': ' -f /opt/bin/ldif2csv-StoogesDumpAll.awk < /home/dbdumps/stooges-`date +%m%d%y`.ldif > /home/dbdumps/StoogesDatabaseAll-`date +%m%d%y`.csv

File: ldif2csv-StoogesDumpAll.awk

# File: ldif2csv-StoogesDumpAll.awk
# Create csv dump for whole database
#
BEGIN {
        last       = ""
        first      = ""
        name       = ""
        address    = ""
        loc        = ""
        state      = ""
        postalcode = ""
        homephone  = ""
        telephonenumber = ""
        mail       = ""
        mobile     = ""
        printf(" last,first,full name,address1,address2,city,state,postalcode,home#,work#,e-mail,phone3\n");
}
/^sn: /              {last=$2}
/^givenName: /       {first=$2}
/^cn: /              {name=$2}
/^street: /          {address=$2}
/^l: /               {loc=$2}
/^st: /              {state=$2}
/^postalCode: /      {postalcode=$2}
/^homePhone: /       {homephone=$2}
/^telephoneNumber: / {telephonenumber=$2}
/^mail: /            {mail=$2}
/^mobile: /          {mobile=$2}
/^dn/ {
        if(last != "" && first != "" && last != "StoogeAdmin") printf("%s,%s,%s,%s,,%s,%s,%s,%s,%s,%s,%s\n",last,first,name,address,loc,state,postalcode,homephone,telephonenumber,mail,mobile)
        last       = ""
        first      = ""
        name       = ""
        address    = ""
        loc        = ""
        state      = ""
        postalcode = ""
        homephone  = ""
        telephonenumber = ""
        mail       = ""
        mobile     = ""
}
# Capture last dn
END {
        if(last != "" && first != "" && last != "StoogeAdmin") printf("%s,%s,%s,%s,,%s,%s,%s,%s,%s,%s,%s\n",last,first,name,address,loc,state,postalcode,homephone,telephonenumber,mail,mobile)
}

CSV to LDAP LDIF format:

File: csvDump2ldif.c

001/* File:  csvDump2ldif.c
002   Author: Greg Ippolito
003   Version: 1.0
004 
005   Usage: csvDump2ldf < inputfile.csv > outfile.ldif
006 
007   Load into LDAP:
008          ldapadd -f stooges.ldif -cxv -D "cn=StoogeAdmin,o=stooges" -W
009 
010*/
011 
012#include <stdio.h>
013#include <string.h>
014 
015#define MAX_NUMBER_OF_FIELDS 17
016#define MAX_FIELD_LENGTH 132
017#define TOTAL_SIZE (MAX_FIELD_LENGTH * MAX_NUMBER_OF_FIELDS)
018#define SERVER_ROOT "o=stooges"
019 
020int main(int argv, char *argc[])
021{
022   int i, c;
023   int ifield;                      /* Field count */
024   int iline=0;                     /* Line count */
025   int ignoreCommaFlag = 0;
026   char field[MAX_NUMBER_OF_FIELDS][MAX_FIELD_LENGTH];  /* 17 fields */
027   char *server_root = SERVER_ROOT;
028   int ii, kk, fComma;
029 
030   for( ii=1910; ii<2100; ii++ )
031   {
032      printf("dn: ou=%d,o=stooges\n",ii);
033      printf("ou: %d\n",ii);
034      printf("objectclass: top\n");
035      printf("objectclass: organizationalUnit\n");
036      printf("\n");
037   }
038 
039   bzero((char *)field, (size_t) TOTAL_SIZE);
040 
041   i = 0;
042   ifield = 0;
043   fComma = 0;
044 
045   while ((c = getchar()) != EOF)
046   {
047      if( c == '"' && ignoreCommaFlag ) ignoreCommaFlag = 0;
048      else if( c == '"' && !ignoreCommaFlag ) ignoreCommaFlag = 1;
049      else if( c == ',' && ignoreCommaFlag )
050      {
051         field[ifield][i] = ' ';
052         i++;
053      }
054      else if( c == ',' && !ignoreCommaFlag )
055      {
056         fComma = 1;                            /* Set comma flag */
057         field[ifield][i] = '\0';               /* NULL terminate */
058         i = 0;
059         ifield++;
060         /* Found line with bogus number of fields. Keep repeating last field.*/
061         if( ifield == MAX_NUMBER_OF_FIELDS ) ifield--;
062      }
063      else if( c == '\n' )
064      {                               /* First field number begins count at 0 */
065        iline++;
066        field[ifield][i] = '\0';               /* NULL terminate */
067        fComma = 0;                              /* Set comma flag */
068 
069        if( field[2][0] == '\0' )
070                fprintf(stderr,"Error line %d: Blank field 3 - no cn\n", iline);
071        else
072        {
073         printf("dn: cn=%s,ou=%s,%s\n",field[2],field[11],server_root);
074         printf("cn: %s\n", field[2]);
075         printf("objectClass: top\n");
076         printf("objectClass: person\n");
077         printf("objectClass: organizationalPerson\n");
078         printf("objectClass: inetOrgPerson\n");
079         printf("givenname: %s\n", field[1]);
080         printf("sn: %s\n", field[0]);
081         if( field[11][0] != '\0')
082            printf("ou: %s\n", field[11]);  /* Department=organizational unit */
083         if( field[12][0] != '\0' ) printf("mail: %s\n", field[12]) ;
084         printf("employeetype: A\n");
085         if( field[3][0] != 0 && field[4][0] != 0)
086                    printf("streetAddress: %s %s\n", field[3], field[4]);
087         else if( field[3][0] != 0 && field[4][0] == 0)
088                    printf("streetAddress: %s\n", field[3]);
089         else if( field[3][0] == 0 && field[4][0] != 0)
090                    printf("streetAddress: %s\n", field[4]);
091         if( field[5][0] != '\0') printf("l: %s\n",field[5]);
092         if( field[6][0] != '\0') printf("st: %s\n", field[6]);
093         if( field[7][0] != '\0') printf("postalCode: %s\n", field[7]);
094         if( field[10][0] != '\0') printf("telephoneNumber: %s\n", field[10]);
095         if( field[9][0]  != '\0') printf("homePhone: %s\n", field[9]);
096         if( field[13][0] != '\0') printf("mobile: %s\n", field[13]);
097         if( field[11][0] != '\0')
098            printf("departmentNumber: %s\n", field[11]);
099 
100         printf("\n");
101 
102         /* Clear variables */
103         bzero((char *)field, (size_t) TOTAL_SIZE);
104        }
105 
106        i = 0;
107        ifield = 0;
108      }
109      else
110      {
111         //if( i==0 && c==' ' )
112         field[ifield][i] = c;
113         i++;
114         fComma = 0;                              /* Set comma flag */
115      }
116 
117   }
118 
119}

LDAP LDIF file dump to CSV:

File: ldif2csv.cpp

001// ldif2csv.cpp
002// Greg Ippolito
003 
004// ldif2csv < file.ldif > file.csv
005 
006#include <string>
007#include <algorithm>
008#include <vector>
009#include <cctype>
010#include <iostream.h>
011#include <fstream.h>
012 
013main()
014{
015   const string      sDelim(":");
016   string            sLine, sValue;
017   string::size_type posBeginIdx, posEndIdx;
018   string::size_type ipos;
019   string::size_type ilength;
020   string            sKeyWord;
021   string            cn,givenname,sn,streetaddress,l,st,postalcode;
022   string            mail,homephone,telephonenumber,mobile;
023 
024   cout << " last,first,full name,address1,address2,city,state,postalcode,country,home#,work#,year,e-mail,phone3" << endl;
025 
026   while( getline(std::cin, sLine) )
027   {
028      if( sLine.empty() );                     // Ignore empty lines
029      else
030      {
031         ipos = 0;
032         posEndIdx = sLine.find_first_of( sDelim );
033         sKeyWord  = sLine.substr( ipos, posEndIdx ); // Extract word
034         posBeginIdx = posEndIdx + 2;  // Beginning of next word (after ': ')
035 
036         if( !sKeyWord.compare( "cn" ) ) cn = sLine.substr(posBeginIdx);
037         if( !sKeyWord.compare( "givenname" ) ) givenname = sLine.substr(posBeginIdx);
038         if( !sKeyWord.compare( "sn" ) ) sn = sLine.substr(posBeginIdx);
039         if( !sKeyWord.compare( "streetaddress" ) ) streetaddress = sLine.substr(posBeginIdx);
040         if( !sKeyWord.compare( "l" ) ) l = sLine.substr(posBeginIdx);
041         if( !sKeyWord.compare( "st" ) ) st = sLine.substr(posBeginIdx);
042         if( !sKeyWord.compare( "postalcode" ) ) postalcode = sLine.substr(posBeginIdx);
043         if( !sKeyWord.compare( "mail" ) ) mail = sLine.substr(posBeginIdx);
044         if( !sKeyWord.compare( "homephone" ) ) homephone = sLine.substr(posBeginIdx);
045         if( !sKeyWord.compare( "telephonenumber" ) ) telephonenumber = sLine.substr(posBeginIdx);
046         if( !sKeyWord.compare( "mobile" ) ) mobile = sLine.substr(posBeginIdx);
047 
048         if( !sKeyWord.compare( "dn" ) && cn.compare("StoogeAdmin")
049            && !givenname.empty() && !sn.empty() )
050         {
051            if(index(cn.c_str(),',' ) )
052            {
053               cn="\""+cn;
054               cn.append("\"");
055            }
056            if(index(sn.c_str(),',' ) )
057            {
058               sn="\""+sn;
059               sn.append("\"");
060            }
061            if(index(givenname.c_str(),',' ) )
062            {
063               givenname="\""+givenname;
064               givenname.append("\"");
065            }
066            if(index(mail.c_str(),',' ) )
067            {
068               mail="\""+mail;
069               mail.append("\"");
070            }
071            }
072            if(index(streetaddress.c_str(),',' ) )
073            {
074               streetaddress="\""+streetaddress;
075               streetaddress.append("\"");
076            }
077            if(index(telephonenumber.c_str(),',' ) )
078            {
079               telephonenumber="\""+telephonenumber;
080               telephonenumber.append("\"");
081            }
082            if(index(homephone.c_str(),',' ) )
083            {
084               homephone="\""+homephone;
085               homephone.append("\"");
086            }
087            if(index(mobile.c_str(),',' ) )
088            {
089               mobile="\""+mobile;
090               mobile.append("\"");
091            }
092            cout << sn << "," << givenname << "," << cn << "," ;
093            cout << streetaddress << ",," << l << "," << st << "," ;
094            cout << postalcode << "," << homephone << "," ;
095            cout << telephonenumber << ",";
096            cout << endl;
097            cn.erase();
098            givenname.erase();
099            sn.erase();
100            streetaddress.erase();
101            l.erase();
102            st.erase();
103            postalcode.erase();
104            mail.erase();
105            homephone.erase();
106            telephonenumber.erase();
107            mobile.erase();
108         }
109      }
110 
111   }
112 
113}

Return to YoLinux LDAP Tutorial

YoLinux LDAP Tutorial: Support scripts and software tools for OpenLDAP directories