Excel, AXL, and Cisco CUCM

Introduction

 Cisco CUCM (CallManager)'s clumsy web GUI has earned its reputation.  :)  It might be OK for a small-to-medium business to perform daily operation on the infamous CCMAdmin GUI.  But it's a nightmare for large organization, especially service providers that need to perform MACD(Move, Add, Change, Delete) on hundreds or thousands of entries.

DevOps is the trend.  No exception on CUCM.  All CUCM configuration is stored in database.  You could view or change the database if you know a little bit about SQL query language.  You either do it from CUCM CLI (command line), or via web calls (SOAP/AXL).

Though database is the most powerful and flexible way to view and change CUCM configuration, it requires you understand the database structure.  You'll have to read the "Database Dictionary" on cisco.com to figure out which table is for what function.  Some of the functions require more than one table, which makes things more complicated.

AXL (Administrative XML Web Service) is another option to manipulate CUCM configuration in a relatively easy way.  Instead of having to know database tables, fields, keys, etc., you may just tell AXL that "I want to list all users with first name Michael".  Then AXL will do the leg work to query database and return desired results.

Obviously, we need to have AXL service running on CUCM.  We also need a user account that has AXL privilege.  Using admin account is the lazy way, but you should create a dedicate AXL account.  To verify AXL service is running and the account has proper privilege, you may put the following URL into a web browser, where "cucm.domain-name.com" is the FQDN of your CallManager server.

https://cucm.domain-name.com:8443/axl/

You should see the following displayed on the web page:

I'm not going to turn this article into an AXL tutorial.  For details, please refer to https://developer.cisco.com/docs/axl/

Excel and VBA

"Why VBA?  It's so old-school!  You should be using Python!"

I'm not a big fan of VBA.  However, Excel's dominance makes it the most common tool across industries.  Office clerks, field engineers, end users, almost everyone has Excel installed on their computers.  Almost everyone knows how to use Excel with no (or very little) training.  In network integration or migration projects, we're still seeing a lot of data being stored in Excel (or CSV) format.

Thus we need to use the data already there to program network gears including CUCM.  VBA is the only built-in script language for MS office suite.  Which means, end users don't have to install any additional software to run the script.

Unfortunately, VBA is not as popular as Python or PHP from developer perspective, especially when it comes to network programming.  There is very little information online to show you how to get things done (concerning network programming).  That doesn't mean it can't be done.  You'll just have to spend more time on research and test.

I did quite a lot of research online.  Couldn't find a complete example of how to make AXL calls to Cisco CUCM from Excel spreadsheet.  I'm sharing my script here so you don't have to reinvent the wheel.

AXL, SOAP, and SoapUI

I assume you know how to code with VBA scripting.  I assume you spent your time reading AXL, SOAP (basically XML).  And of course, I assume you know how to do administrative work on CUCM Admin GUI.  We're not going to cover those topics here.

You may think AXL as programming API.  Before using the API, you need to know what functions are available in the API.  And you also want an easy way to test the API (without involving language-specific coding).  I recommend you download a free version of SoapUI (https://www.soapui.org/downloads/latest-release.html).  Use SoapUI to open the AXLAPI.wsdl downloaded from Cisco (https://developer.cisco.com/docs/axl/#!download-the-axl-wsdl/download-the-axl-wsdl).  Then you may test the AXL calls and examine the SOAP/XML being sent and received.

In the screen above, the XML code on left hand side is the request, the XML code on right is the response from CUCM.  It is a example of querying a Calling Search Space (CSS) and get all partitions in that CSS.

Excel and VBA Script

Now we build an Excel spreadsheet like below:

There are a few cells in the spreadsheet need to be filled out:
1) The FQDN of CUCM publisher (AXL server)
2) Username
3) Password
4) Calling Search Space (CSS) name

When click on the 'Execute' button, the script will reach out to CUCM and retrieve all partitions in that CSS.  Then it'll fill the partition names in cell B8, B9, B10, ... so on so forth.

You see another benefit of using Excel is that:
1) You have a place to store the data input
2) You have a place to store the data output

This is what it looks like on CCMAdmin:

This is what it looks like after clicking the 'Execute' button in spreadsheet:

This is the script:

By the way, in order to use the XML objects, you need to enable the reference to "Microsoft XML v6.0".