Updated Information!
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.
Background
...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='(&(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.
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.
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
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!
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…???)
George,
Good question. Try the following:
(&(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl:1.2.840.113556.1.4.803:=2))(telephoneNumber=*))
If that doesn’t work for you, you could try:
(&(objectclass=user)(!(objectclass=Computer))(!(UserAccountControl: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
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
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
Bill,
I’m sure you’ve been there done this… I look into your method.
Thanks
Mark
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 ;D
HTH.
Bill
I just started using the axl sql toolkit. Are there any other tools out there to access the informix databae and view the query results in a much nicer format (or easy to read).
it does take some time to format the response file before it is a readable format.
Please advice.
Thanks,
Haider
Hey Haider,
You don’t necessarily need to use the SQL Toolkit to get at the database information, but you do have to use the AXL/SOAP API. I have several tools that I put together which leverage the API without launching the Toolkit. The key is to have a tool in your toolkit that can handle XML data objects. Typically, you will have an element labeled "return" with each record wrapped in an element labeled "row" (row will be a child of return) and within each row, you will have child elements that are labeled based on the field name (or identified) that you specified in the query. Once you have a way to handle the XML. Then you can parse, manipulate, push the data.
HTH.
Regards,
Bill
I downloaded the AXL toolkit but my question is where do I run the program? Should it be run on the Call manager or should it be run on my windows desktop PC?
thanks Phil
Phil,
You will run the toolkit from your desktop.
HTH.
Regards,
Bill
Great article
I’m trying to use ldap filter to only sync user’s that dont have the "telephoneNumber" field empty in ldap. Is that possible by using filters or do I have to do this in MS side.
Thanks.
Zak.
Zak,
You can use telephonenumber=* in your LDAP query. This means that the attribute is present and you will accept any value. You will want to include it as part of a logical AND.
-Bill