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

Author
William Bell
Vice President, Solutions and Products

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='(&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.

15 responses to “AXL SQL Toolkit – Part 3 – Updating CUCM DirSync LDAP Filter by Example

  1. 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.

  2. 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

  3. 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!

  4. 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…???)

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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.

  13. 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

Leave a Reply

 

Nick Kelly

Cybersecurity Engineer, Cisco

Nick has over 20 years of experience in Security Operations and Security Sales. He is an avid student of cybersecurity and regularly engages with the Infosec community at events like BSides, RVASec, Derbycon and more. The son of an FBI forensics director, Nick holds a B.S. in Criminal Justice and is one of Cisco’s Fire Jumper Elite members. When he’s not working, he writes cyberpunk and punches aliens on his Playstation.

 

Virgilio “BONG” dela Cruz Jr.

CCDP, CCNA V, CCNP, Cisco IPS Express Security for AM/EE
Field Solutions Architect, Tech Data

Virgilio “Bong” has sixteen years of professional experience in IT industry from academe, technical and customer support, pre-sales, post sales, project management, training and enablement. He has worked in Cisco Technical Assistance Center (TAC) as a member of the WAN and LAN Switching team. Bong now works for Tech Data as the Field Solutions Architect with a focus on Cisco Security and holds a few Cisco certifications including Fire Jumper Elite.

 

John Cavanaugh

CCIE #1066, CCDE #20070002, CCAr
Chief Technology Officer, Practice Lead Security Services, NetCraftsmen

John is our CTO and the practice lead for a talented team of consultants focused on designing and delivering scalable and secure infrastructure solutions to customers across multiple industry verticals and technologies. Previously he has held several positions including Executive Director/Chief Architect for Global Network Services at JPMorgan Chase. In that capacity, he led a team managing network architecture and services.  Prior to his role at JPMorgan Chase, John was a Distinguished Engineer at Cisco working across a number of verticals including Higher Education, Finance, Retail, Government, and Health Care.

He is an expert in working with groups to identify business needs, and align technology strategies to enable business strategies, building in agility and scalability to allow for future changes. John is experienced in the architecture and design of highly available, secure, network infrastructure and data centers, and has worked on projects worldwide. He has worked in both the business and regulatory environments for the design and deployment of complex IT infrastructures.