When working with directory technologies, such as NetIQ’s eDirectory, or Microsoft’s Active Directory, sometimes the quick and simple thing to do is to use an LDAP browser to make or verify changes. A popular LDAP browser is the Apache Directory Studio.
Oftentimes, a directory administrator is given a list of accounts that need an attribute changed or added such as email address, or supervisor, etc. On other occasions, a directory administrator may simply want to filter the accounts being displayed in the LDAP browser to only a certain few in order to verify some of the attribute information of those specific accounts.
The objective of this post is to provide a simple Excel Spreadsheet that can create a basic LDIF file or generate an LDAP filter to help directory administrators accomplish these simple directory administration tasks.
**Please note, the LDAP LDIF and Filter Creator (link to zip file) contains a Microsoft Excel Spreadsheet that has two macros and is a macro-enabled file. Code from those macros is copied below for additional review.
The attached spreadsheet has two worksheets, one to create an LDIF file, the other to create an LDAP filter. The LDIF file created with this tool will remove all of the values of the specified attribute and will add the newly provided attribute value. The LDAP Filter will be created with the attribute provided and the values for that filter.
The attached spreadsheet is also an example of how you can modify an Excel spreadsheet to execute code. Code in this spreadsheet is simple VBA code, but spreadsheets can be setup to execute powershell scripts that directory administrators have created to assist in the management of their environments.
The attached spreadsheet was initially created to assist in migrating accounts from Microsoft Active Directory to NetIQ’s eDirectory. NetIQ Identity Manager was configured to synchronize accounts from the AD domain to eDirectory and to a Microsoft SQL database. The directory administrator wanted to migrate a specific list of the accounts and the LDIF Creator created an LDIF file which modified an attribute that triggered the migration of those accounts.
The LDAP filter tab makes creating a simple “OR’d” LDAP filter from a list of attribute values really easy. Copy the list to the spreadsheet and it will build the LDAP filter. The filter can then be copied to an LDAP browser to return only the desired accounts. LDAP filters can be quite complex and powerful. LDAP filters can simplify the administration and the process to validate the data contained in a directory. The attached file creates a simple LDAP filter similar to the filter below:
Being able to master LDAP filters and LDIF files are important for any directory administrator. Simplifying tasks and automating processes are just as important for directory administrators. As tasks get more and more sophisticated, the tools being used to accomplish those tasks will also be more and more sophisticated. Simple LDAP filters and LDIF files will not suffice when a company needs realtime account synchronization. An organization looking to automate processes that can transform data and synchronize data from one source to another should consider a much more robust tool like NetIQ’s Identity Manager. But if a simple LDAP filter, or a simple LDIF file can do the trick, then enjoy!!!
Create_LDIF & Create_LDAP_Filter Code:
Sub Create_LDIF() Sheets("Attrib Mod - Create LDIF").Select eidFile = Range("B7").Value attrib = Range("B5").Value numRecords = Range("B8").Value Set fs = CreateObject("Scripting.FileSystemObject") Set a = fs.CreateTextFile(eidFile, True) Range("B8").Select For i = 1 To numRecords dnGet = ActiveCell.Offset(i + 2, 0).Range("A1").Value a.WriteLine "dn: " & dnGet a.WriteLine "changetype: modify" a.WriteLine "delete: " & attrib a.WriteLine "-" a.WriteLine "" a.WriteLine "dn: " & dnGet a.WriteLine "changetype: modify" a.WriteLine "add: " & attrib a.WriteLine attrib & ": " & ActiveCell.Offset(i + 2, 1).Range("A1").Value a.WriteLine "-" a.WriteLine "" Next ' Close the file. a.Close End Sub Sub Create_LDAP_Filter() Sheets("Create LDAP Filter").Select attrib = Range("B7").Value Range("B5").Select numRecords = Range("B5").Value If numRecords > 1 Then ldapfilter = "(|" For i = 1 To numRecords Dim attribval As String attribval = ActiveCell.Offset(i + 2, 0).Range("A1").Value ldapfilter = ldapfilter + "(" & attrib & "=" + attribval + ")" Next ldapfilter = ldapfilter + ")" Range("D5").Value = ldapfilter End Sub