0
Answered

Ability to specify SQL query for CISCO UCM IdB Connector

Mark Southwell 8 years ago in UNIFYBroker/Cisco Unified Communications Manager • updated by anonymous 4 years ago 5

Hi guys!

Is there any way to override the default queries the Cisco UCM IdB connector presents to the Cisco AXL service? There appears to be two modes of operation:
1. sqlTrust=True communicator option set results in the executeSQLQuery AXL request being sent with "select * from <object>".
2. sqlTrust=False communicator option set results in an AXL "get<object>" request being sent for the specified object. SQL queries are performed internally based on the object requested.

Option 1 returns too few attributes, option 2 is too verbose and when executed on End User objects takes as much time to process 1 user as operation mode 1 does in processing all users in my test environment.

If we could pass any SQL query to the AXL service via the executeSQLQuery AXL request the connector could be customised to return exactly what we need.

The attached files demonstrate the functionality and what is actually being processed by the AXL service when these requests come in. sqlTrust=False causes a heap of unnecessary queries being made to the SQL database by the AXL service, slowing down response.


sqlTrustFalse.txt
sqlTrustTrue.txt
Affected Versions:
Fixed by Version:

Hi Mark,

Been doing some other things the last two days, but your help on PRODUCT-29 allowed me to get it all up and running - just need to test and debug an export now. Once that's done I can start looking in to this.

As I've mentioned before, everything Rod left us is in that documentation, so you know as much as I do - if it doesn't mention being able to specify an SQL query then it's probably not possible. However, if we're just providing our query as a parameter on the SOAP request then I don't see why we can't add the option to supply one.

Thanks Patrick. The crux of the problem I'm having with the connector is that the select * from <object> SQL queries do not return the reference attributes for things like device profile associations, etc. Whilst the Get<object> native AXL queries return way too much and slow down performance. Happy medium would be to query for select attributes including the reference attributes and then import end users, devices and phone number maps as separate connectors and apply broker transforms to present what I want to FIM.

That's going to be incredibly difficult given the only available work days between now and then are Dec 19th-22nd and Jan 9th-13th and we haven't done feasability on this yet (there's still issues with the existing code). I'll have a chat to the team to see if there's any way we can make it work.

Hi Peter,

Please close when you've confirmed you've read out all the fields you need to.