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".

Network Automation with TCL

My company has a large (and not very well managed) network.  Different VLANs have different "ip helper-address" (DHCP) settings.  Some of them are pointing to development DHCP, some of them pointing to production DHCP, some of them are pointing to VDI environment.

For the production environment, there are two DHCP servers (A and B).  They are supposed to back up each other.  However some of the VLANs might be configured to use one of them but not both.

The DHCP administrator wanted to perform a DR test, which he would shut down one of the DHCP servers and see if the clients still be able to get IP address from the other server.

Before he can perform the DR test, he needs to make sure that DHCP server A and B are referenced in pairs, which means a interface either references both servers, or reference neither server.  If the interface references only one of the two, it'd be a problem.  We need to fix this problem before the DR test.

So the workflow is pretty straight forward:
1) SSH into a switch (where the  IP interfaces are configured).
2) Use "show ip interface brief | exclude unassigned" to display the interfaces with IP configured.
3) Use "show run interface xxx" command to review the configuration.  If one of the DHCP servers (A or B) was referenced but the other one of missing, we need to add the other one to the configuration.

* If both are present, it's fine.  If none of them present, that's fine.

This is a simple but tedious work.  Because we have a bunch of switches.  Some of the switches have more than 50 SVIs.  Visual inspection would be  time consuming and prone to human errors.

This is where automation should kick in.  You may use other program languages.  I chose TCL because it was built in on IOS.

In privilege (enable) mode, type "tclsh" to get into TCL shell.  Copy and paste the script into the command line.  It will create a procedure called "check".  Then type "check" (without quotation marks).  Below are some sample outputs:

Example 1: No problem found.

Example 2: Found some interfaces missing one of the helpers.

Script is as below.
proc check {} {

# Define the two commands we want to check
set str_helper1 "ip helper-address 10.50.23.27"
set str_helper2 "ip helper-address 10.202.32.30"

# Define missing_commands string
set str_missing_cmds ""

# List all the interfaces with IP address.
set str_sh_ip_int [exec sh ip int br | ex una]

# Break the output into individual lines and put them in a list.  First two lines are headers.
set list_int [split $str_sh_ip_int \n]

# Get the number of lines
set num_num_of_lines [llength $list_int]

# Skip headers, start from 1st interface
set num_current_line 2

# Process each each line (each IP interface).
while { $num_current_line < $num_num_of_lines } {

    # Get the interface name from each line.
    set str_int_name [lindex [split [lindex $list_int $num_current_line]] 0]

    # Do a "show run interface" againt the interface name.
    set str_sh_run_int [exec sh run int $str_int_name]

    # See if helper1 exists but not helper2
    if { [string match "*$str_helper1*" $str_sh_run_int] && ![string match "*$str_helper2*" $str_sh_run_int] } {
        append str_missing_cmds "interface $str_int_name\n $str_helper2\n"

    # See if helper2 exists but not helper1
    } elseif { [string match "*$str_helper2*" $str_sh_run_int] && ![string match "*$str_helper1*" $str_sh_run_int] } {
        append str_missing_cmds "interface $str_int_name\n $str_helper1\n"
    }

    # Move to next line (next IP interface)
    incr num_current_line
}

# Missing helper on some of the interfaces
if {[string length $str_missing_cmds]} {
    puts "\n\nIP Interface(s):"
    puts "----------------"
    puts $str_sh_ip_int
    puts "\n[expr $num_num_of_lines-2] interface(s) checked.  The following commands are missing:\n"
    puts $str_missing_cmds

# All interfaces are good
} else {
    puts "\n\nIP Interface(s):"
    puts "----------------"
    puts $str_sh_ip_int
    puts "\n[expr $num_num_of_lines-2] interface(s) checked.  No Problem Found."
}

}