topic Re: Best Method to Query Directory Information in Management

Best Method to Query Directory Information

sharpgate — Wed, 20 May 2015 15:17:05 GMT

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

Re: Best Method to Query Directory Information

npetrele — Wed, 20 May 2015 16:03:13 GMT

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

Perhaps listUser would be the AXL API you want?

Re: Best Method to Query Directory Information

sharpgate — Thu, 21 May 2015 00:30:33 GMT

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

Re: Best Method to Query Directory Information

npetrele — Thu, 21 May 2015 00:53:44 GMT

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

Re: Best Method to Query Directory Information

sharpgate — Thu, 21 May 2015 01:01:45 GMT

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.

Re: Best Method to Query Directory Information

TDoan — Mon, 25 May 2015 00:56:51 GMT

Hi,

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

Regards

Thuy Doan

Re: Best Method to Query Directory Information

sharpgate — Mon, 25 May 2015 07:26:52 GMT

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

Thanks

Re: Best Method to Query Directory Information

TDoan — Mon, 25 May 2015 07:47:34 GMT

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

Re: Best Method to Query Directory Information

Mamdouh Elgamal — Wed, 27 May 2015 15:03:37 GMT

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

Re: Best Method to Query Directory Information

sharpgate — Thu, 28 May 2015 10:08:21 GMT

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

Re: Best Method to Query Directory Information

npetrele — Thu, 28 May 2015 14:26:04 GMT

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.

Re: Best Method to Query Directory Information

Mamdouh Elgamal — Thu, 28 May 2015 15:04:36 GMT

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.

Re: Best Method to Query Directory Information

sharpgate — Thu, 28 May 2015 17:36:17 GMT

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.

Re: Best Method to Query Directory Information

AndrewGeorgiou — Thu, 23 Mar 2023 18:14:49 GMT

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

Re: Best Method to Query Directory Information

TDoan — Fri, 24 Mar 2023 01:10:00 GMT

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

Re: Best Method to Query Directory Information

AndrewGeorgiou — Fri, 24 Mar 2023 14:25:49 GMT

Thank you TDoan. I will give it a try.