Running SQL Queries on CUCM 6x/7x Using AXL SOAP Toolkit – Part 2

Updated Information!

In 2012 I launched another blog site hosted at http://ucguerrilla.com where I maintain a series that is focused on providing regular installments of SQL query examples for use with Cisco UC applications. If you want to get at a lot of data, really fast then you may want to check it out.

Where is the Toolkit?

If you are like me you like to get right down to business and start trying things out.  Learn by doing as it were.  The AXL/SOAP toolkit can be downloaded from the CUCM plug-in list.

When you download the toolkit a zip archive is saved to your desktop.  Extract the contents to a directory (e.g. cucm6axltool).

When you unzip the archive you will have the following in the directory you specified as the extraction destination:

• classes directory: java class files for the application
• lib directory: various .jar files that support the application
• src directory: contains the actual java applet AxlSqlToolKit.java
• README.txt: A quick and dirty file that gives you the basic command line syntax
• sample.xml: A sample file that shows how to run a query, do an update, etc.

How to use the tool?

First and foremost you should think of this tool as a very basic way to send SQL transactions to the CUCM system.  It is not a very robust tool by any stretch of the imagination but it may be handy for testing an interface, or feeding data into another script, etc.  It does a good job at what it is intended to do but the syntax, input, and output is pretty raw.

The command line is one of those beasty java contraptions where you specify a long class path switch.  The classpath is important to be sure but you don’t need to always pass the classpath each time you run the tool.  You can add the beasty command line to a shell script or command (.cmd) file (for Windows).  You can also update the “CLASSPATH” environment variable.  See this Sun resource: Setting the classpath.  An example for Windows folks:

set CLASSPATH=%CLASSPATH%;.classes;.libsaaj-api.jar;.libsaaj-impl.jar;.libmail.jar;.libactivation.jar;.libjaxm-api.jar;.libjaxm-runtime.jar;.libxercesImpl.jar;.libxml-apis.jar

The above will append to the existing classpath enviornment variable.  This only applies to the active cmd shell window.  You need to use the System Utility in the control panel to make things more permanent.  Oh, and you may want to specify the full path.

By default, when you run the AxlSqlToolkit applet it will read in the sample.xml file to determine the commands you want it to run.  It runs the command in a serial fashion and (by default) dumps them to sample.response.

You can demo these by creating a sample.xml file like so:

    "

Then run the following command (assuming you have statically set the classpath):

java AxlSqlToolkit -username=ccmadministrator -password=C1$coC1$co -host=10.3.3.20

If everything is on the up and up, you will get some spaghetti like the following:

Basically what you are seeing with the sample.xml and the sample.response is the “raw” XML syntax for the “executeSQLQuery” method and it’s appropriate response.  This is actually quite handy when you are learning to write your own tools.  In the native format it may be quite maddening to try and parse.

The response includes the SOAP envelope, which will contain any fault codes or messages if a fault was encountered.  Assuming none, you will want to start dissecting the return data by looking for the “” string.  This signals the beginning of the recordset values returned by your query.

Each record is a “row” and as such will be contained in the “” child nodes.  Such as:

  SEPDEADBEEFDEAD
  Sample Record

Recall that our original query was: select name,description from device.  This is why there are child nodes named “name” and “description” in each “row”.  If our query was: select name as devicename,description from device, our response may look like this:

  SEPDEADBEEFDEAD
  Sample Record

So, that is the basic record structure.  Understanding this is important but it doesn’t help one parse a large XML stream.  This tool takes raw input and dumps the raw XML response from the CUCM server.  I actually wrote a tool to do my query and response management but I use the AXL/SOAP toolkit to test and learn.  So, don’t discount that value.  Also, I use the AXL/SOAP toolkit to do SQL updates.  Meaning, to update records in the CUCM database.  It is handy to use this toolkit to update fields in bulk that BAT can’t assist you with or to update database fields that you can’t manage via the standard admin web portal.

We’ll go into an update example on another installment.  For now, you will want to look for tools that help parse the XML output.  If that is too much of a bother you could open the file in an application like notepad++ and do some fancy “search and replace” to quickly clean things up.  For example:

1. Search/Replace:  with “rn”  (insert a carriage return/line feed)

2. Search/Replace: with “” (basically, delete)

3. Search/Replace: with “,” (starting to build a CSV)

4. Search/Replace: with “” (delete the straggler)

Yeah, not a whole lot better but better than nothing. With large record sets (multiple fields per row) you could replace the “<” and “>” with commas and then clean it up quickly by loading into Excel and deleting columns.  None of these are graceful, but not everyone wants to be bothered with learning a programming language.  If you are willing to do some creative text editing you can actually get things accomplished quickly.

Keep in mind that using AXL/SOAP will allow you to run queries to get data “views” that aren’t easily compiled by strolling around the CCMAdmin web interface.

Where to find more on AXL/SOAP and the toolkit(s)?

The best place is the Cisco developer community, which can be accessed here: http://developer.cisco.com/web/axl