Best Method to Query Directory Information

[sharpgate]

Hello,

What is the best method to get a list of all phone extensions and extension descriptions from CUCM via AXL? It is important to query extensions for users who even extension mobility configured regardless of their login status. I know the executeSQLQuery API is the an option. What is the best query to use?

Also from CUCM user configuration standpoint, what type of user/user group should be created to allow such privileges when consuming the API?

Thanks

[npetrele]

Can you be a little more specific about what you're looking for?

Perhaps listUser would be the AXL API you want?

[sharpgate]

Sure, to be more specific what I need is the following:

Extension No, Extension Description, CSS Description. This can help build some sort of corporate directory if you will.

I hope it is clearer now.

Thanks

[npetrele]

If you want a corporate directory, the best approach is to sync call manager with an LDAP directory service or Active Directory. 

[sharpgate]

Yes you are right, but this particular CUCM is not configured for that since LDAP and AD is not present. I simply want to use AXL API to pull all configured extensions and user descriptions. I presume I will need to query Device and dnorPattern tables. I only need help the correct query.

[TDoan]

Hi,

You use getPhone (AXL API) to get the information as Extension (directory number), CSS, description of device/line....

Regards

Thuy Doan

[sharpgate]

Hi

Thanks. So what is the syntax to be used to query all phone extensions?

Thanks

[TDoan]

Hi

If you have already got a list of device then you can use getPhone to get information for every device

This is a sample getPhone (AXL API). I do not know what CUCM version you are using, my example is CUCM version 10.5

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/10.5">

  <soapenv:Header/>

  <soapenv:Body>

     <ns:getPhone sequence="1">

        <name>Your Device Name</name>

     </ns:getPhone>

  </soapenv:Body>

</soapenv:Envelope>

It will return name, product, description, callingSearchSpaveName (CSS), directory number (extension).....

You can use listPhone (AXL API) to get all devices in CUCM then you use getPhone (AXL API)  to get information for every

device.

I hope this will help you.

Regards

Thuy Doan

[Mamdouh Elgamal · Accepted solution]

If you don't want to use the API and just need a SQL query to obtain the information you need.

here is a sample query, tested on CUCM 10.5. Should work fine on prior recent versions. but can't confirm.

run sql select device.name AS DeivceName, numplan.dnorpattern AS Extension, numplan.description AS
LineDescription, callingsearchspace.name AS LineCSS 
from device, numplan, devicenumplanmap, callingsearchspace
where device.pkid = devicenumplanmap.fkdevice and numplan.pkid = devicenumplanmap.fknumplan
and device.name like '%SEP%' and numplanindex = 1
and numplan.fkcallingsearchspace_sharedlineappear = callingsearchspace.pkid

This query only grabs the primary line on each device, remove the numplanindex condition and it will also grab secondary lines, also it's only restricted to device names containing SEP.

Hope that helps

[sharpgate]

Thanks Mando

This is exactly what I was looking for. I do prefer using SQLQuery.

So, does any one happen to have helpful information about data throttling issues. In other words, if the query result is like 3000 devices what the implications will be?

Thanks

[npetrele]

AXL isn't meant to be used as a real-time API for applications (or as a way to emulate directory services), so you may run into problems unless you break up queries to return multiple data subsets.  From the developer docs:

AXL acts as a provisioning and configuration API, not as a real-time API.

The AXL interface provides Developers with two ways of accessing database obejcts:  Standard methods which provide backward compatibility between releases (e.g. Get User) and direct access through the ExecuteSQLQuery and ExecuteSQLUpdate methods, which do not provide backward compatibility.

The AXL service dynamically throttles 'write' requests (add/update/remove requests, and executeSqlUpdate) based on the current size of the UCM database transaction queue.  Note that the UCM database transaction queue can contain pending updates arising from recent administrative actions, bulk administration tasks, other AXL applications, etc.  If the database transaction queue is too large, AXL may reject new write requests with a "503: Service Unavailable" error message.  In such cases applications may re-try rejected requests, however re-tries should be attempted after a reasonable delay (e.g. several seconds)

Read requests are also dynamically throttled based on the size of the data set to be returned.  Large queries issued using the List <object> and ExecuteSQLQuery methods that result in a data set greater than 8 MB  will return "Query request too large. Total rows matched: <Matched Rows>. Suggested row fetch: less than <Number of Rows>."

Cisco recommends developers who use the ExecuteSQLQuery method follow these guidelines:

• Applications should break up all SQL queries so that the data returned is always less than 8 MB
• Use the Unified CM Data Dictionary to help determine the maximum allowable size of any field in the database
• ASCII characters are stored as 1-byte
• i18n characters (UTF-8) are stored as 3-bytes
• DB has a mix of ASCII and UTF-8 characters
• While UCM is processing a large query, concurrent queries should not result in data sets larger than 2 MB
• Applications should wait to receive a response before issuing subsequent queries
• Applications should not submit duplicate queries.

[sharpgate]

Yes I usually do that but this is for manual method to achieve that. However, the target from what I am looking for is to have an updated list of the extensions and their descriptions from within my application on a regular basis.

SSH won't fit as a good interface with CUCM database to automate such a task.

[Mamdouh Elgamal]

I would not use the API (i.e ExecuteSQLQueryReq), for this type of query

Just SSH into CUCM and run this query then copy the result or you can log the session through your ssh client.

[AndrewGeorgiou]

Hello I am new to this CUCM.  How to I extract the phone extensions from the system on a daily basis?  Is there any code like Powershell or SQL?  I am a TSQL guy but also do powershell.

thank you!!!

[TDoan]

Hello AndrewGeorgiou,

You can use AXL API to get a list of extensions from CUCM and to get a response from CUCM you need to use https or SOAP to post it to CUCM.

To get a list of extensions you need to do the following steps

1) Get listPhone (AXL API)

    Ex:

<ns:listPhone sequence="1">
<searchCriteria>
<name>%</name>
</searchCriteria>
<returnedTags><uuid/><name/><description/><product/><model/><class/><protocol/><callingSearchSpa ceName/><devicePoolName/><securityProfileName/></returnedTags>
</ns:listPhone>

2) getPhone (AXL API)

Ex:

<ns:getPhone sequence="1">
<name>CIPC_101</name>   ---- (Or you can use <name>%</name> or <description>%</description>. Name and Description you get it from listPhone)
<returnedTags><name/><description/><product/><model/><class/><protocol/><callingSearchSpaceName/><devicePoolName/><securityProfileName/></returnedTags>
</ns:getPhone>

You will get a response from CUCM and from there you will find "pattern" (Ex: <pattern>1002</pattern>) 1002 is an extension.

Hope this will help you.

Regards,

TDoan