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

Posted by Carl Karawani on Mar 24, 2016 7:30:00 AM

executing-sql-queries.pngOnce 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. 

 

Was this Article Helpful? 

Subscribe to the Stack8 Blog

 

 

Topics: Cisco AXL, Cisco Unified Communications Manager

Don’t miss out. Expert advice straight to your inbox!

Insightful tips, troubleshooting and solutions for your everyday Unified Communications challenges from our team of experts. You can look forward to:

  • Weekly UC tips;
  • Cisco Unified Communications insights;
  • UCCX - Contact Center insights;
  • Network and Security insights;
  • Cisco Release notes and Product reviews.

Posts by Topic

see all
Migrating Lync to Jabber

Recent Posts