Using CUCM AXL and SQL Queries for 3rd-Party Provisioning

Cisco Unified Communication Manager (CUCM) gives you a set of essential tools that allow you to do most of the things you need to do with your system. However, there are times when these tools may not be the most effective or efficient when integrating 3rd-party provisioning applications. I’ll walk you through three phases that will help you successfully execute CUCM AXL and SQL Queries. Each phase needs to be executed in order.

Know what you’re looking for? Skip ahead:

• Phase 1: Integrate Any 3rd-Party Provisioning Application
• Phase 2: Sending AXL Requests to CUCM with Postman
• Phase 3: Executing SQL Queries against CUCM through Cisco AXL

Phase 1: Integrate Any 3rd-Party Provisioning Application

Let’s begin by enabling Cisco’s Administrative XML Web Service (AXL) API to allow remote provisioning of CUCM.

For applications that require AXL access to your CUCM, you should always:

• Enable the AXL Web Service on your CUCM
• Create an Application User Account with AXL Access
• Perform a Smoke Test

Enabling the AXL Web Service on your CUCM

STEP 1 –  Navigate to your CUCM in your web browser and log in to the Cisco Unified Serviceability.

STEP 2 – Go to Tools > Control Center – Feature Services.

STEP 3 – Select the CUCM and click Go.

STEP 4 – Within the Database and Admin Services section, ensure Cisco AXL Web Service is checked, then press Save.

Creating an Application User Account with AXL Access

STEP 1 – Log in to the Cisco Unified CM Administration page.

STEP 2 – Navigate to User Management > Application User.

STEP 3 — Click Add New.

STEP 4 — Provide a new user with a UserID and Password.

STEP 5 —Assign the Standard AXL Access group to the user.

STEP 6 — Press Save.

Performing a Smoke Test

You are now ready to carry out a quick Smoke Test to ensure that the AXL Service is up and running. The Smoke Test also ensures that the Application User with Standard AXL Access is properly set up.

STEP 1 — Log out of the CUCM if you are still logged in.

STEP 2 — Navigate to the web address.

STEP 3 — When prompted by the browser, enter the credentials of your Application User with Axl access.

STEP 4 — You should see a page with the header Cisco CallManager: AXL Web Service along with the text: The AXL Web Service is working and accepting requests. Use HTTP POST to send a request.

Congratulations!

You have successfully enabled your CUCM AXL service and smoke-tested it using an Application User Account.

If you are interested in learning more about AXL or how we use it here at ZIRO in a variety of our software solutions, including ZIRO for Cisco, give us a shout.

Phase 2: Sending AXL Requests to CUCM with Postman

Sending AXL requests to CUCM with Postman assumes your CUCM has the AXL Service Enabled. It also assumes you have credentials for an Application User account with Standard AXL Access. If not, please review Method 1. Integrate Any 3rd-Party Provisioning Application above before proceeding.

The Administrative XML Web Service (AXL) is an XML/SOAP-based API that enables remote provisioning of CUCM using any modern (and even not so modern!) programming language. We will be looking at how to quickly test your AXL API calls using Postman.

This Google Chrome App is a great tool for developers or anyone who wants an easy way for testing any web API (including Cisco’s AXL API). Now let’s review the steps required to set up and install Postman and then send an AXL request to CUCM with Postman.

Set up and install Postman

STEP 1 – Install & open Postman (Available at https://www.getpostman.com/)

STEP 2 – Provide the URL to your CUCM (typically your Publisher) in the address bar:

https://<hostname or IP of your CUCM>:8443/axl/

STEP 3 – Go to the Authorization tab and select Basic Auth.

STEP 4 – Provide the credentials for the Application User with Standard AXL Access and click Update request.

Step 5 – Clicking Send should now return an HTML response with the text:
"The AXL Web Service is working and accepting requests."

If your smoke test failed, validate the following:

• Is the URL correct?
  • Hostname
  • Port
  • Starts with https://
  • Ends with /axl/
• Is the Cisco AXL Service enabled on CUCM? If not, see Phase 1 above.

Sending an AXL request to CUCM

Now that Postman has been set up and smoke-tested against the Cisco AXL Service let’s send a real request.

The below steps demonstrate how to send a getPhone AXL API request. This request will require the device’s name as part of the request. The CUCM will in turn respond with all the details of the phone.

For further details on API and the list of all possible transactions, please see the Cisco AXL Documentation.

STEP 1 – Set the request type to POST.

STEP 2 – Go to the Headers tab and add an entry SOAPAction with the value CUCM:DB ver=14.0 getPhone

NOTE: If you are on an older version of CUCM (ex. 12.0 or 12.5), swap out 14.0 for the version of your CUCM.

STEP 3 – In the Body tab, provide the getPhone request with the name of the phone to lookup (e.g.SEPDD2338878585)

<soapenv:Envelope xmlns:soapenv="https://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="https://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:getPhone>
         <name>SEPDD2338878585</name>
      </ns:getPhone>
   </soapenv:Body>
</soapenv:Envelope>

NOTE: Notice once again the version number in the XML body. Swap out 14.0 for the version of your CUCM if necessary.

STEP 4 – Press Send and notice the XML response containing the details of the phone.

Congratulations!

You have successfully sent an AXL API request to your CUCM using Postman. If you are interested in building solutions to help you with your UC environment using AXL, feel free to reach out. We use AXL extensively for a variety of software products, including ZIRO for Cisco.

Phase 3: Executing SQL Queries against CUCM through Cisco AXL

This method takes CUCM AXL and SQL Queries one step further with direct access to the CUCM database with Cisco AXL.

Executing SQL queries against CUCM with Cisco AXL walks you through the steps to get the most from your CUCM using the Cisco AXL API.

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 of an inactive Directory Number from the CUCM administration interface.

Consulting the CUCM Database Dictionary, we 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.

STEP 1 – Change the SOAPAction from the previous step to executeSQLQuery

STEP 2 – Change the Body to be an executeSQLQuery and provide the query defined above.

<soapenv:Envelope xmlns:soapenv="https://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="https://www.cisco.com/AXL/API/14.0">
   <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>

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

Getting Help with CUCM AXL and SQL Queries

Following these three phases will help you get the most out of your CUCM using the Cisco AXL API to integrate 3rd-party provisioning tools.

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

If you have any questions about CUCM AXL and SQL Queries, schedule a call with a ZIRO UC expert or call +1-844-940-1600. We love a good conversation.

—

Editor’s Note: This post was originally published as a three-part series between March 10-24, 2016.