In 2012 I launched another blog site hosted at http://ucguerrilla.com where I maintain a series that is focused on providing regular installments of SQL query examples for use with Cisco UC applications. If you want to get at a lot of data, really fast then you may want to check it out.
...Have you ever wanted to change the LDAP filter used by CUCM DirSync?
I assume that the answer to the question is “yes” since you clicked “Read More”. In the current CUCM appliance releases you cannot modify the LDAP filter used by CUCM natively through the CCMAdmin web interface. However, the filter is stored in a table and can be manipulated using the AXL/SOAP API. The rumor is that in CUCM 8x, a method is exposed in the CCMAdmin interface that will allow administrators to edit the LDAP filter used and actually create other customizations to LDAP integrations. That is great for CUCM 8.x deployments, but what if you want to tweak the settings today?
What is this table of which you speak?
The table(s) we are interested in are the ldapfilter table and the typeldapserver. Actually, we only need to deal with the ldapfilter table but there is a type relationship that could be helpful. You can take a look at the data from the command shell by executing the following command:
admin: run sql select ldap.name, ldf.tkldapserver as type, ldf.filter from ldapfilter as ldf inner join typeldapserver as ldap on ldf.tkldapserver = ldap.enum
name type filter =============================== ==== =============================================================================================== Microsoft Active Directory 1 (&(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl:1.2.840.1135184.108.40.2063:=2))) Netscape or Sun ONE LDAP Server 2 (objectclass=inetOrgPerson)
Using the AXL/SOAP Query Tool
Running the above query is actually easier from the command line but since this blog is part of a series on the AXL/SOAP query tool then we should so the syntax you could put in the query file used by the tool:
<?xml version="1.0" encoding="UTF-8"?> <data> <sql query="select ldap.name, ldf.tkldapserver as type, ldf.filter from ldapfilter as ldf inner join typeldapserver as ldap on ldf.tkldapserver = ldap.enum"/> </data>
Save this content to test.xml and then run the command:
java AxlSqlToolkit -input=test.xml -username=ccmadministrator -password=C1$coC1$co -host=10.3.3.20
So, we took a bunch of extra steps to run a query that took us two seconds from the command line. Not very interesting? I concur, so let’s look at doing an update using the AXL/SOAP Query tool. Let’s assume we are using DirSync with a Microsoft Active Directory (AD) server and that we wish to modify the LDAP filter to only include user objects in the telecommunications department (Telecomm). In this scenario, our target LDAP filter is:
Now, we can’t edit this directly using the CCMAdmin interface nor can we perform an update to this value from the command shell. So, we have to leverage our AXL API and one method is the AXL/SOAP Query toolkit. We first need to create a query file:
<?xml version="1.0" encoding="UTF-8"?> <data> <sql update="update ldapfilter set filter='(&(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl:1.2.840.1135220.127.116.113:=2))(department=Telecomm))' where tkldapserver=1"/> <sql query="select ldap.name, ldf.tkldapserver as type, ldf.filter from ldapfilter as ldf inner join typeldapserver as ldap on ldf.tkldapserver = ldap.enum"/> </data>
There are a few things to note about the update query. First, the LDAP filter uses the ampersand (“&”) to denote a logical “AND”. Since the XML parser in the AXL/SOAP API will bulk if you send the “&”, you must escape the character using the appropriate escape syntax (check here if you need more information on XML escape characters). Second, we are specifying the Microsoft AD LDAP filter (type == 1) using the tkldapserver value in the ldapfilter table. This is the reason we ran the first query so we knew which value we could use as a unique key when doing an update.
Save this query file as updateldapfilter.xml and then run the following command line:
java AxlSqlToolkit -input=updateldapfilter.xml -username=ccmadministrator -password=C1$coC1$co -host=10.3.3.20
What the script will do is update the ldapfilter table and then run a select query that you can use to determine if the LDAP filter was updated as requested.
Once you confirm that the database is updated with your new filter then you will need to restart the following services on the publisher node:
- Cisco DirSync
- Cisco TomCat
You can then perform a manual synchronization with your LDAP server and validate that the user records are activated (or deactivated) as expected.
Notes on Commands and Versions Used
The process described in this blog has been tested with CUCM version 6.x and should be applicable to CUCM version 7x. The Microsoft AD version tested was 2003. The syntax used for the AxlSqlToolkit assumes that you have added the appropriate directories to your class path as described in part 2 of this series. You should substitute the appropriate values in for the username, password, and host command line arguments when running the AxlSqlToolkit java app.