AXL SQL Toolkit - Part 3 - Updating CUCM DirSync LDAP Filter by Example

Home

Starting in October I began writing a series of articles about the AXL SQL Toolkit that Cisco provides with the Cisco Unified Communications Manager (CUCM). I started the series because a friend of mine wanted to know more about how to get more granular "views" on the CUCM database. In the first article, we discussed the table structure, tables, and relationships (well, some examples at least). In the second article we discuss the AXL SQL Toolkit itself. Now, I wanted to round out the series with a real world example of using the toolkit. Let's stat with a question: 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.113556.1.4.803:=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:

(&(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl:1.2.840.113556.1.4.803:=2))(department=Telecomm))

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='(&amp;(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl:1.2.840.113556.1.4.803:=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.

Comments (9) Add Comment

Subscribe to this comment's feed

Good information.
written by damntech, June 10, 2010

This was pretty good information. I goofed around with the entire tutorial, I douldn't get the AXL Tool running. So I wondered why couldn't I get the LDAP query in via the CLI. It was interesting to find that the CM 7.1.3 CLI doesn't let one input ampersands.

Re: Good information
written by William Bell, June 11, 2010

Yes, it is annoying that ampersands aren't accepted by the CLI. Were you able to the AXL tool running? If not, what specific problems did you run into?

-Bill

Ah Ha.
written by damntech, June 15, 2010

The text document included with the axlsqltoolkit (with 7.1.3) improperly stated that one should use J2RE 1.4.2-08. As it turns out the latest version of Java works, JRE 6u20. Have a great day and thanks for the information!

question from a newbie - exclude users with a blank phone number field
written by George, June 21, 2010

ignoring for the moment that the cust could reorg their AD tree (laughing)
I'm super SQL illiterate. I'm going to go look, but you may be able to help faster...
what if I want to only import users if their phone number field is not blank?
(now I'll go see if I can find the field... and the syntax for a blank field... and...???)

Re: question from a newbie - exclude users with a blank phone number field
written by William Bell, June 22, 2010

George,

Good question. Try the following:

(& (objectclass=user)(!(objectclass=Computer))(!(UserAccou
ntControl:1.2.840.113556.1.4.803:=2))(telephoneNumber=*))

If that doesn't work for you, you could try:

(& (objectclass=user)(!(objectclass=Computer))(!(UserAccou
ntControl:1.2.840.113556.1.4.803:=2))(!(telephoneNumber='')))

I have used the former for another attribute filter. I have not tested the latter. The only thing that is really in question is if the LDAP filter should use single quotes (') or double (").

HTH.

Regards,
Bill

Sample.response
written by Mark, June 27, 2010

Hi Bill,

I was originally trying to get data or files created from the bat phone report tool. Conclusion was that data isn't accessible from the shell. I think the toolkit could fill that requirement. I went through the sample exercise. I couldn't get the output of the sample.response file imported into an Access database. So that's what I'm researching. Just wondering if you had any suggestions?

Mark

Re: sample.response
written by William Bell, June 28, 2010

Mark,

The response is going to be in XML format. I am not sure if access has a way to parse XML and import data into tables, but it does have a macro language that you could likely use to accomplish this task. Personally, I use Microsoft's XML DOM object via MS javascript to parse XML responses from the CUCM AXL/SOAP API. I would first learn the structure of the XML response. Typically it will be:

value
value

Then search google for methods to either (a) importing directly from access or (b) use a script that can process the XML and push into access.

HTH.

Regards,
Bill

Thanks Bill
written by Mark, June 29, 2010

Bill,

I'm sure you've been there done this... I look into your method.

Thanks
Mark

Re: been there done this
written by William Bell, July 02, 2010

Mark,

I just noticed that my simple example didn't show up. Our comment tool apparently doesn't like it when I post XML syntax. Even when properly escaped. I will check with our admin on that. Imagine, that the following parens are actually lt and gt symbols.

The response would look like:
(return)
(row)
(header1) value1 (/header1)
(header2) value2 (/header2)
(/row)
(row)
(header1) value1 (/header1)
(header2) value2 (/header2)
(/row)
(/return)

I have used vbscript, java, and javascript. All of them have an XML DOM parser of some sort. Most have more than one option. My scripts are MS centric as of now. With a proper XML parser you can pretty easily parse through the XML return (which is daunting at first) and turn it into database records, csv, tab delimited, flowers, whatever smilies/grin.gif

HTH.

Bill

Write comment

Chesapeake NetCraftsmen