This is a revisit of previous blog https://htluo.blogspot.com/2018/08/excel-axl-and-cisco-cucm.html
The reasons to use Excel/VBA are:
- Excel is available on most (if not all) computers.
- No additional software installation is needed. This is important in a "lockdown" environment where software installation is prohibited.
- Smoother learning curve. Most of people know how to use Excel.
- Easy data entry and processing. Excel formulas can be used to validate and normalize data.
While Python dominates the DevOps/Automation field, I believe Excel still has its value. I'll use CUCM and Unity as example to demonstrate the DevOps idea. You may modify the script to work with other network devices, such as routers, switches, etc.
Most of the network devices/appliances have API (Application Program Interface). Some of them use AXL (such as CUCM). Some of them use REST (such as Unity). In general, you send your request to the network devices, telling them what you want to read or write to their database(configuration). The devices act accordingly, given your request was in the right format (AXL, REST, etc.) and authorized (authenticated).
In the case of CUCM and Unity, the core of AXL and REST is XML, which is the data format to send and receive data. It'd be helpful if you have some basic understanding of XML such as node, element, attribute, etc.
In our example, two pieces of software will be used:
- Microsoft Excel
- SOAPUI (optional but strongly recommended) - https://www.soapui.org/tools/soapui/
Referencing documents:
- Cisco Administrative XML (AXL) - https://developer.cisco.com/site/axl/
- Cisco Unity Connection Provisioning Interface (CUPI) API - https://www.cisco.com/c/en/us/td/docs/voice_ip_comm/connection/REST-API/CUPI_API/b_CUPI-API.html
As mentioned before, the core of the API is XML. Microsoft has an object library (a.k.a. library, module, add-in, etc.) to make XML programming easier. Our first job is to enable developer menu in Excel and enable the XML library.
Go to Excel > File > Options > Customize Ribbon (or right-click on the ribbon area and choose "Customize the ribbon"). If "Developer" is not already in the right column, find it in the left column, choose it, click "Add" button to move it to the right column. If it's already in the right column, make sure it is enabled (checked).
Now you are ready to code in Excel VBA. We will use two of the object types in the MSXML library:
- MSXML2.DOMDocument60 - this is for XML operations
- MSXML2.SERVERXMLHTTP - this is for HTTP processing
CUCM AXL example:
Dim sXML, sURL, sUsername, sPassword As String
objHTTP.setOption 2, objHTTP.getOption(2)
sUsername = "admin"
sPassword = "p@ssw0rd"
" <soapenv:Body>" & _
" <ns:updatePhone>" & _
" <name>SEPAABBCCDDEEFF</name>" & _
" <description>John Doe</description>" & _
" </ns:updatePhone>" & _
" </soapenv:Body>" & _
"</soapenv:Envelope>"
The example above updates phone SEPAABBCCDDEEFF with description "John Doe". It uses a MSXML2.SERVERXMLHTTP object (named "objHTTP") to do that.
Note that in this example, we didn't use the MSXML2.DOMDocument60 object (named "objXML"). It was because we didn't have too much XML work to do. Instead of using a DOD object, we just used a regular string variable sXML to construct the XML block we want to send.
If we use AXL to query CUCM, for instance, to get the phone details, the HTTP response might contain a large amount of data. Using a string variable to hold this data is not as convenient as using a DOM object. With a DOM object, you may easily locate, extract, update, add, delete the data you want. DOM reference document: https://learn.microsoft.com/en-us/previous-versions/windows/desktop/ms766487(v=vs.85)
No comments:
Post a Comment