What seems like a very long time ago, I became interested in the AXL (Administrative XML Layer) API available on Cisco Unified Communications Manager (formerly CallManager) systems. This API allows you to make configuration changes in a programmatic manner rather than using the standard CUCM Administration web interface.
This is extremely useful to anyone administering a CUCM system as bulk changes are often required that would be very slow to implement manually and the built in Bulk Administration routines do not always give you the functions that you need.
My first exposure to AXL was using it via some purchased tools from a vendor on the web. This tool basically merged some text-based XML templates with an input CSV file and then fired the resulting requests at the CUCM AXL web service. For those who are interested in something like this, the tool is here : http://www.purejs.com/index.jsp
As the tool requires some understanding of the AXL API Schema I quickly came to feel I knew the interface well enough and that getting some working code of my own together would allow me much more flexibility than a simple import.
I then came across a need to make some changes that weren't exposed in the AXL API, which meant directly editing the SQL database. This is not something that should be attempted unless you are very confident with your SQL skills, knowledge of the CUCM database, and the time and resources to test changes properly and preferably in a lab environment.
Cisco kindly provide a tool to allow you access to the CUCM database, and conveniently this is sat on your CUCM server waiting for you in the Application/Plugins page of the CCMAdmin web interface.
Using the AXLSqlToolkit
as Cisco Intended, in an 'Unsupported' Fashion
So – download the AXLSqlToolkit.zip file from the plugins page (where it is listed as 'Cisco CallManager AXL SQL Toolkit'), and extract it to a directory of your choice e.g. c:\axltoolkit
In the root of the unzipped directory there is a README.TXT that describes the tool and how to run it.
Essentially the tool reads in a list of SQL statements from an input XML file, and then exports the returned data to an output XML file. There is a sample input file named sample.xml:
<?xml version="1.0" encoding="UTF-8"?>
<!--DTD generated by XMLSPY v5 rel. 4 U (http://www.xmlspy.com)-->
<!DOCTYPE data [
<!ELEMENT data (sql+)>
<!ELEMENT sql EMPTY>
<!ATTLIST sql
query CDATA #IMPLIED
update CDATA #IMPLIED
>
]>
<data>
<sql query="select * from callmanager"/>
<sql query="select * from device where name='SEP00000'"/>
<sql update="update callmanager set name='test' where name='something'"/>
<sql query="select * from device where name='SEP00000'"/>
</data>
If you were to run this, it would run the two 'Select' queries, and then run an 'Update' query that will fail (unless you are very unlucky and have a CallManager service named 'something', in which case it will probably give you a headache). It will then run the second query again...
Assuming that you are using a Windows client PC, you would enter the command as illustrated in the README.TXT file, replacing the elements in bold with appropriate details for your CUCM cluster:
java -cp .\classes;.\lib\saaj-api.jar;.\lib\saaj-impl.jar;.\lib\mail.jar;.\lib\activation.jar;.\lib\jaxm-api.jar;.\lib\jaxm-runtime.jar;.\lib\xercesImpl.jar;.\lib\xml-apis.jar AxlSqlToolkit -username=CCMAdministrator -password=ciscocisco -host=64.101.156.207
Note that if no input or output parameter is specified, it will import sample.xml and output sample.response.
You can construct lots of SQL queries in your chosen way (for example, you may choose to generate the commands using a text editor, or if you are like me you would likely use a spreadsheet so that formulas can do the repetitive work) and use the AxlSqlToolkit to inject those queries into the CUCM cluster to make any change that is required.
Those queries can be complicated, and more importantly making changes direct to the database means you are bypassing some of the checks and balances that going through a proper API would involve, so you are generally at higher risk of doing something that may terminally affect your CUCM cluster's configuration. It's mainly for this reason that directly updating the DB is frowned upon.
Another great example of the use of the AxlSqlToolkit is AXL SQL Toolkit - Part 3 - Updating CUCM DirSync LDAP Filter by Example, where Bill Bell describes how to update the CUCM LDAP filter
Using the AxlSqlToolkit with General AXL Calls
A quick look over the source code that Cisco have been good enough to provide in the download revealed that it is full of useful tricks that would get me started. Being relatively new to Java when I first came accross this, and a non-programmer by trade, there were lots of minor hurdles around security, networking, authentication and so forth that would have taken me some time to figure out myself, but are nicely worked around here.
In this example, I'll use the AXL API to create a translation pattern. As some of you may have noted, this is something you can't bulk insert (unless, of course, you use the relatively new Import/Export BAT feature, modify/duplicate the exported lines in the CSVs and reimport them, but that's another tutorial).
If you don't have a text editor that supports Java, I recommend jEdit as a good free editor with the ability to colour code to make it far easier to read and spot mistakes. If you are new to Java, also note that everything is very case sensitive, so edit carefully or prepare to spend lots of time fighting the compiler!
Step 1) Copy the AxlSqlToolkit.java file, name it appropriately and update the file contents.
I copied the file to a new file named AxlCreateTrans.java. The class name inside the file must match the filename, so open the AxlCreateTrans.java file, and do a find/replace for AxlSqlToolkit and replace it with AxlCreateTrans
Step 2) Replace the 'execute()' method with something more useful
The execute() method parses the input XML file, extracts the SQL queries from it, and sends them to CUCM. This is all well and good, but I'm not a computer so writing XML is not as simple for me as writing a simple text file. Being lazy, I can also easily get any spreadsheet program to write that CSV for me... so we replace this execute() method with a modified version that simply reads a line, splits it where it finds a comma, and uses that data to create our AXL calls.
private void execute() {
try {
// first, initialize the output file
new FileWriter(outputFile).close();
FileReader fRead = new FileReader(inputFile);
BufferedReader buffRead = new BufferedReader(fRead);
String currentLine = buffRead.readLine();
while (currentLine != null) {
System.out.println("Processing : " + currentLine);
StringTokenizer st = new StringTokenizer(currentLine, ",");
String sPattern = st.nextToken();
String sPartition = st.nextToken();
if (sPartition.equals("#")) {
sPartition = "";
}
String sCSS = st.nextToken();
if (sCSS.equals("#")) {
sCSS = "";
}
String sMask = st.nextToken();
if (sMask.equals("#")) {
sMask = "";
} else {
sMask = sMask.trim();
}
System.out.println("Inserting : Pattern(" + sPattern + ") Partition(" + sPartition + ") CSS(" + sCSS + ") Called Party Mask(" + sMask + ")");
sendMessage(createSOAPMessage(sPattern, sPartition, sCSS, sMask));
currentLine = buffRead.readLine();
}
} catch (Exception e) {
e.printStackTrace();
}
}
Simply put, that code expects 4 values on each line – a Pattern, Partition, CSS, and a Calling Party Mask. It reads the line, splits it at each comma, and feeds the resulting parameters to the 'createSOAPMessage' function.
Step 3) Add a new 'createSOAPMessage' function
This function is based on the createSqlMessage function that is already in the AxlSqlToolkit.java file. However, instead of sending a SQL query, we'll have it generate an 'AddTransPattern' XML object and send this to CUCM.
public SOAPMessage createSOAPMessage(String sPattern, String sPartition, String sCSS, String sMask) throws Exception {
MessageFactory mf = MessageFactory.newInstance();
SOAPMessage soapMessage = mf.createMessage();
SOAPPart soapPart = soapMessage.getSOAPPart();
SOAPEnvelope envelope = soapPart.getEnvelope();
SOAPBody bdy = envelope.getBody();
SOAPBodyElement bodyElement = bdy.addBodyElement(envelope.createName("addTransPattern", "axl", "http://www.cisco.com/AXL/API/1.0"));
Name attrUuid = envelope.createName("uuid");
SOAPElement newPattern = bodyElement.addChildElement("newPattern");
newPattern.addAttribute(envelope.createName("sequence"), String.valueOf(System.currentTimeMillis()));
newPattern.addChildElement("pattern").addTextNode(sPattern);
newPattern.addChildElement("callingSearchSpaceName").addTextNode(sCSS);
newPattern.addChildElement("usage");
newPattern.addChildElement("routePartitionName").addTextNode(sPartition);
newPattern.addChildElement("blockEnable");
newPattern.addChildElement("calledPartyTransformationMask").addTextNode(sMask);
newPattern.addChildElement("callingPartyTransformationMask");
newPattern.addChildElement("useCallingPartyPhoneMask").addTextNode("Off");
SOAPElement dialPlan = newPattern.addChildElement("dialPlan");
dialPlan.addChildElement("name");
newPattern.addChildElement("dialPlanWizardGenId").addTextNode("0");
newPattern.addChildElement("networkLocation");
newPattern.addChildElement("patternUrgency").addTextNode("True");
newPattern.addChildElement("prefixDigitsOut");
newPattern.addChildElement("routeFilterName");
return soapMessage;
}
Note here that there is a lot of other stuff inserted into the XML object aside from the 4 attributes in the XML file. The published schema lists certain things as mandatory, but in reality each version of CUCM may randomly complain about mandatory things missing (which are really missing, and you must add with empty values or sensible defaults) or things that are missing (that are actually there, but not in the order that the particular CUCM version expects). The default values here suited what I was doing at the time.
Step 4) Add missing import statements and remove one that stops it compiling
Near the top of the file, there is a statement like so:
import org.apache.xerces.parsers.DOMParser;
This is something we aren't using (it was used to parse the input XML file originally) and it's easier to compile without it there. Remove that line, and then add the following lines at the end of the list of import lines:
import java.util.StringTokenizer;
import java.io.FileReader;
import java.io.BufferedReader;
import javax.xml.soap.Name;
import javax.xml.soap.SOAPElement;
These lines enable some of the other classes I've used in my code.
Step 5) Finally, compile the code
For this final bit, you will need to have a Java Development Kit installed. If you just have the normal JRE (Java Runtime Engine) then you won't have the required compiler. You can tell if this is the case as your java.exe will be in a folder with JRE in the title, and you won't have the javac.exe compiler in the same directory.
When you have installed the JDK (if necessary), compile the file like so:
Step 6) Test the tool!
To use the tool, we need to create a CSV file. Simply open notepad, and enter appropriate values like this example:
7878,Internal,#,1212
Here I want to add 7878 in the Internal partition as a translation to 1212, and the # simply means apply no CSS (Calling Search Space) to this translation pattern. On most systems you would want a CSS assigned, and would enter the name of the CSS. Save the file as input.txt
You can simply type the command line as follows:
java AxlCreateTrans -host=yourccmip -username=ccmadministrator -password=yourpassword -input=input.txt
If you see the message 'Positive Response Received' in the command window, then you should be able to visit the CCMAdmin web interface and see that the new translation pattern has been added.
If you see an error, it may be a live issues such as 'the number already exists', or it may be one of many other failures such as a fault with the API itself or (heaven forbid) the code I have shown you.
Next Steps:
To get further use from this, it's important to understand the way the SOAP message is built in Step 3. This is all standard SOAP stuff and is documented in lots of places all over the web, but start by taking a look at the Cisco AXL Schema document for your version of CUCM, and see how what is described there for the 'AddTransPattern' object translates into code here.
Try modifying it to add other simple objects such as a Pickup group, or doing an Update to a user account.
If you are new to Java, I strongly recommend this book - I read a lot on Java when I was learning, and found this book after wading through some extremely dull material. It's less dry than your average computer manual, and I found that I learnt a huge amount of the basic and advanced concepts from this very quickly:
Aaron Harrison
Development Engineer at IPCommute UK
