Click here to request your free 14-day trial of Cisco Umbrella through NetCraftsmen today!

11/25
2009
William Bell

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

William Bell

William Bell

Architect, Infrastructure Practice Lead

William’s background spans an array of technical disciplines including application development, network infrastructure, protocol analysis, virtualization, and Unified Communications. Bill is certified as a CCIE Voice (CCIE #38914) and possesses a deep understanding of Cisco’s UC and Collaboration portfolio. He leads the Infrastructure Engineering team and also works with customers on architecting solutions that align with core business drivers. Bill is a regular contributor on the Cisco Support Community, a 3 time Cisco Designated VIP, and blogs on the NetCraftsmen and UC Guerrilla sites.

View more Posts

 

Nick Kelly

Cybersecurity Engineer, Cisco

Nick has over 20 years of experience in Security Operations and Security Sales. He is an avid student of cybersecurity and regularly engages with the Infosec community at events like BSides, RVASec, Derbycon and more. The son of an FBI forensics director, Nick holds a B.S. in Criminal Justice and is one of Cisco’s Fire Jumper Elite members. When he’s not working, he writes cyberpunk and punches aliens on his Playstation.

 

Virgilio “BONG” dela Cruz Jr.

CCDP, CCNA V, CCNP, Cisco IPS Express Security for AM/EE
Field Solutions Architect, Tech Data

Virgilio “Bong” has sixteen years of professional experience in IT industry from academe, technical and customer support, pre-sales, post sales, project management, training and enablement. He has worked in Cisco Technical Assistance Center (TAC) as a member of the WAN and LAN Switching team. Bong now works for Tech Data as the Field Solutions Architect with a focus on Cisco Security and holds a few Cisco certifications including Fire Jumper Elite.

 

John Cavanaugh

CCIE #1066, CCDE #20070002, CCAr
Chief Technology Officer, Practice Lead Security Services, NetCraftsmen

John is our CTO and the practice lead for a talented team of consultants focused on designing and delivering scalable and secure infrastructure solutions to customers across multiple industry verticals and technologies. Previously he has held several positions including Executive Director/Chief Architect for Global Network Services at JPMorgan Chase. In that capacity, he led a team managing network architecture and services.  Prior to his role at JPMorgan Chase, John was a Distinguished Engineer at Cisco working across a number of verticals including Higher Education, Finance, Retail, Government, and Health Care.

He is an expert in working with groups to identify business needs, and align technology strategies to enable business strategies, building in agility and scalability to allow for future changes. John is experienced in the architecture and design of highly available, secure, network infrastructure and data centers, and has worked on projects worldwide. He has worked in both the business and regulatory environments for the design and deployment of complex IT infrastructures.