Tuesday, January 27, 2009

Converting LDIF/LDAP data into a CSV file

LDIF to CSV

I work with LDAP on a regularly basis. I frequently have to pull data using ldapsearch. While the data that ldapsearch spits out is a decent representation sometimes I want something a bit easier to work with.

The format I use most tends to be CSV. While the acronym stands for comma separated value the format may be used to describe data that is printed out line by line and separated by anything you can imagine.

The problem for me is finding a decent LDIF to CSV converter. The ones that I have tried tend to choke on any number of issues. Some of these include binary data or failing to normalize the multivalued attributes.

I finally got sick enough of these issues that I decided to write my own. You can download it here:

Python/Linux/Source

Windows

Update: Pushed the code to GitHub: https://github.com/tachang/ldiftocsv

Hopefully the Windows binary works for you. If it doesn't then just download Python 2.6 and run the source manually. I am providing the binary just for convenience.

Using LDIFtoCSV
Running "python LDIFtoCSV.py" should give you the usage text.

usage: LDIFtoCSV.py [options]

-o <filename> : File to write output. By default this is set to sys.stdout
-l <filename> : File to write logging output. By default there is no logging.
-F <char> : Character to separate the fields by. By default this is a comma. i.e. -F","
-D <char> : Character to delimit the text value by. By default this is a double quote. i.e. -D"""
-M <num> : The maximum number of columns a multivalued attribute should take up (default: 5). This is common with the objectClass attribute where it can have over 20 values. Do you want to have 20 columns each with the same heading objectClass or do you want to limit it.

Here are some common command lines that I use (assuming you have a test.ldif):

Outputs the CSV straight to standard output:
python LDIFtoCSV.py test.ldif

Outputs CSV to standard output with semicolons as the delimiter:
python LDIFtoCSV.py -F";" test.ldif
Outputs CSV to standard output with pipes as the delimiter and text surrounded by carrots:
python LDIFtoCSV.py -F"|" -D"^" test.ldif

Wednesday, January 21, 2009

Downloading the Global Address List from Outlook/Exchange

Getting the Data

Recently I had a need to dump the entire Global Address List from within Outlook. Usually the address list will contain the contact information for your entire organization.

The most effective way I have found to do this is a straight LDAP search on the Active Directory and pull everything into an LDIF file. Conceptually it is straightforward but I have found the most difficult part is usually trying to get all the pieces together. So subscribing to the French idea of "mise en place" here is what you'll need:

Username
Your username is your fully qualified distinguished name. It is not your Windows login name. It commonly looks something like "CN=Tchang\, Jeff,OU=Users,OU=USA,DC=example,DC=com".

The easiest way I have found to obtain your username is to login to a Windows machine and then put the following two lines in a text file with the extension .vbs:

Set objADSysInfo = WScript.CreateObject("ADSystemInfo")
result = InputBox("Active Directory Username (copy and paste as necessary)", "Active Directory Username", objADSysInfo.UserName, 100, 100)

Password
The password will be your domain password.

Domain controller hostname
Couple of ways to get this. This will be the Active Directory/LDAP server that you will extract the entries. An echo %logonserver% at the command prompt usually works.

Ldapsearch tool
Easiest way to get the tools is from Sun's iPlanet Directory SDK. Go to http://www.sun.com/download/products.xml?id=3ec28dbd and select the iPlanet Directory SDK downloads. After download the zipfile find the executable named ldapsearch.exe.

Performing the search
Here is an example syntax to extract out all the users:

Windows:
ldapsearch.exe -b OU=Users,OU=USA,DC=example,DC=com -h host.example.com -p 389 -D "CN=Tchang\, Jeff,OU=Users,OU=USA,DC=example,DC=com" -w - (objectClass=*)

Unix:
ldapsearch -b OU=Users,OU=USA,DC=example,DC=com -h host.example.com -p 389 -D "CN=Tchang\, Jeff,OU=Users,OU=USA,DC=example,DC=com" -W -x "(objectClass=*)"

The -b is for the base. In this example I knew the users were all stored in that branch. -D is for username. -w - (that is a dash w followed by a dash) means to read the password from the console. On Unix the big -W is to prompt and the -x indicates you want simple authentication (cleartext username/password).

You might want to add a " > output.txt" to send the output to a file. If you do that you will have to supply the password on the command line.

Sunday, January 4, 2009

Eye Fi Standalone Server

Eye Fi Linux Hacking

So I spent some time over my vacation learning a bit more about Python. What better way to learn a language than to implement something you want or need, right?

I am releasing a standalone Eye-Fi server written in Python. Basically I saw Dave Hansen's post (http://dave-hansen.blogspot.com/2008/12/freestanding-server.html) and went ahead and did it. This software works on Windows with Python 2.6. I have not tested it on Linux/Unix yet but I assume it will work seeing as how it is written in Python. Please let me know if you try this software out and it works or doesn't. I personally would love any comments!

Python script is here: http://www.darkeneddesire.com/EyeFiServer/EyeFiServer.py

General Architecture Notes

This is a standalone Eye-Fi Server that is designed to take the place of the Eye-Fi Manager.

Starting this server creates a listener on port 59278. I use the BaseHTTPServer class included with Python. I look for specific POST/GET request URLs and execute functions based on those
URLs.

Currently all files are downloaded to the directory in which this script is run.

To use this script you need to have your Eye-Fi upload key.
It is in C:\Documents and Settings\[User]\Application Data\Eye-Fi\Settings.xml

Simple search for "eyeFiUploadKey" and replace it with your key.

--

Update (4/4/09) - http://returnbooleantrue.blogspot.com/2009/04/eye-fi-standalone-server-version-20.html