Select Page

Executing SQL Queries against CUCM through Cisco AXL (part 3 of 3)

Carl Karawani

Once you have completed part 1 (Integrate any 3rd party provisioning application with your CUCM using Cisco AXL) and part 2 (Sending AXL Requests to Cisco CUCM with Postman), part 3 takes it one step further with direct access to the CUCM database with Cisco AXL.

Executing SQL queries against CUCM with Cisco AXL is the third and final part of our 3-part series on taking you through the steps to get the most from your CUCM using the Cisco AXL API.  Once you have completed part 1 and part 2, part 3 takes it one step further with direct access to the CUCM database with Cisco AXL. Perhaps two of the most powerful things about the Cisco AXL API are the executeSQLQueryReq and the executeSQLUpdateReq functions. These features allow you to create, update and delete directly in the CUCM database.

For a complete view of the database schema, please consult the CUCM Database Dictionary

To demonstrate this, we will show you the steps to query CUCM to find all the Directory Numbers that are inactive.

Below is a screenshot from an inactive Directory Number from the CUCM administration interface.

the steps to query CUCM to find all the Directory Numbers

Consulting the CUCM Database Dictionarywe can build up the following query to find Directory Numbers (NumPlan.tkPatternUsage = 2) that are inactive (np.iscallable = 'f')

SELECT description,
       dnorpattern
FROM numplan np
WHERE np.tkpatternusage = 2
  AND np.iscallable = 'f'

Let’s now go over how we would send this query down to CUCM using AXL.

1. Change the SOAP action from the previous step to executeSQLQuery
Change the SOAP action

2. Change the body to be an executeSQLQuery and provide the query defined above.

   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery>
         <sql>
             SELECT description, dnorpattern
             FROM numplan np
             WHERE np.tkpatternusage = 2
             AND np.iscallable = 'f'
        </sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

Cisco Axl code

3. Press Send and notice the XML Result containing all Directory Numbers that are inactive.

Send and notice the XML Result containing all Directory Numbers that are non-active

That is all there is to it. This completes the 3-part series on how to get the most from your CUCM using the Cisco AXL API.

If you would like to learn more about some tools we have created using this API and others, check out our software solutions.

If you have any questions about this or other services we offer including our new UC managed services offerings, reach out, and we will be more than happy to work with you.

 

March 24, 2016

Carl Karawani