Using Microsoft Powershell to perform queries against informix via SOAP\AXL

bpolitis1 · ‎05-29-2014

I've been working to try and run some simple reports against the informix database today on our call manager. I've found alot of information and believe I'm close to my goal, but can't quite connect all the dots yet. I had never heard of AXL and never worked with SOAP before today, so I apologize if this request for help is a little disjointed.

I'm a fairly skilled administrator and scripter, but come from a more Microsoft centric world. And am trying to tie the cisco documentation which is more unix\java related into a Microsoft-centric environment. I'm a relative novice at CCUM, and a complete novice at SOAP and AXL but I think I've found all the pieces I need, and now I just need to push them together into a solution. .

My goal is to be able to automatically query CUCM and generate some reports on a weekly or monthly basis. Most of my management tools are written in Powershell so I'm trying to stick with that if at all possible.

With the help of this site, and the UCGuerilla site, I was able to construct an AXL request using CURL to return a simple DB query. That command looks like this:

curl -k -u admin -H "Content-type: text/xml;" -H "SOAPAction: CUCM:DB ver=8.5" -d @request.xml https://10.228.54.10:8443/axl/ > output.xml

With a properly formatted "request.xml" file this command will return any DB query sent to it. I'll need to do some work on the queries I want, but I know I can access the DB this way without any issue.

Now, I'm trying to achieve the same goal but using Powershell native commands to achieve them so I don't have to use Curl unless necessary.

Power-shell provides a command-let object for doing SOAP queries. And I can create a SOAP web service proxy with the following command:

$ControlCenterProxy=New-WebServiceProxy -Uri "https://10.228.54.10:8443/controlcenterservice/services/ControlCenterServicesPort?wsdl" -Credential $myCredentials

This returns an object called $controlcenterproxy and the methods exposed in the WSDL file can then be invoked by that object.

I have found a list of supported WSDL files\objects at the following site:

http://www.cisco.com/c/en/us/td/docs/voice_ip_comm/cucm/devguide/8_5_1/XML_DevGuide/serviceability.html

Table 4-3 URLs for SOAP Requests Service Definitions
SOAP Request Type	URL for SOAP Requests Service Definition
RisPort RisPort70	https://servername:8443/realtimeservice/services/RisPort?wsdl https://servername:8443/realtimeservice/services/RisPort70?wsdl
PerfmonPort	https://servername:8443/perfmonservice/services/PerfmonPort?wsd
ControlCenterServices	https://servername:8443/controlcenterservice/services/ControlCenterServicesPort?wsdl
LogCollectionService	https://servername:8443/logcollectionservice/services/LogCollectionPort?wsdl
CDRonDemand	https://servername:8443/CDRonDemandService/services/CDRonDemand?wsdl
DimeGetFile	https://servername:8443/logcollectionservice/services/DimeGetFileService?wsdl
SOAPMonitorService¹	https://servername:8443/realtimeservice/services/SOAPMonitorService?wsdl

I can expose the methods of one of these proxy objects as follows below:

PS C:\Users\Admin_politisb> $ControlCenterProxy | gm -mem met*

TypeName: Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebServiceProxy15ControlCenterServices

Port_wsdl.ControlCenterServices

Name MemberType Definition

---- ---------- ----------

Abort Method void Abort()

BeginGetProductInformationList Method System.IAsyncResult BeginGetProductInformationList(string ServiceInformati...

BeginsoapDoControlServices Method System.IAsyncResult BeginsoapDoControlServices(Microsoft.PowerShell.Comman...

BeginsoapDoServiceDeployment Method System.IAsyncResult BeginsoapDoServiceDeployment(Microsoft.PowerShell.Comm...

BeginsoapGetServiceStatus Method System.IAsyncResult BeginsoapGetServiceStatus(string[] GetServiceStatusLis...

BeginsoapGetStaticServiceList Method System.IAsyncResult BeginsoapGetStaticServiceList(string ServiceInformatio...

CancelAsync Method void CancelAsync(System.Object userState)

CreateObjRef Method System.Runtime.Remoting.ObjRef CreateObjRef(type requestedType)

Discover Method void Discover()

Dispose Method void Dispose(), void IDisposable.Dispose()

EndGetProductInformationList Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

EndsoapDoControlServices Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

EndsoapDoServiceDeployment Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

EndsoapGetServiceStatus Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

EndsoapGetStaticServiceList Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

Equals Method bool Equals(System.Object obj)

GetHashCode Method int GetHashCode()

GetLifetimeService Method System.Object GetLifetimeService()

GetProductInformationList Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

GetProductInformationListAsync Method void GetProductInformationListAsync(string ServiceInformationResponse), vo...

GetType Method type GetType()

InitializeLifetimeService Method System.Object InitializeLifetimeService()

soapDoControlServices Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

soapDoControlServicesAsync Method void soapDoControlServicesAsync(Microsoft.PowerShell.Commands.NewWebservic...

soapDoServiceDeployment Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

soapDoServiceDeploymentAsync Method void soapDoServiceDeploymentAsync(Microsoft.PowerShell.Commands.NewWebserv...

soapGetServiceStatus Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

soapGetServiceStatusAsync Method void soapGetServiceStatusAsync(string[] GetServiceStatusList), void soapGe...

soapGetStaticServiceList Method Microsoft.PowerShell.Commands.NewWebserviceProxy.AutogeneratedTypes.WebSer...

soapGetStaticServiceListAsync Method void soapGetStaticServiceListAsync(string ServiceInformationResponse), voi...

ToString Method string ToString()

What I'm struggling with now though is which one of the objects in the table listed above will expose native SQL against informix? And how do I take a SQL query formatted in my Request.XML file used in Curl and then reformat it to pass it through to these objects?

I know all the information should be in the WSDL files somewhere, but without combing through alot of data for days, is there a way I can find this more easily? Can someone bump me in the right direction? This is where my SOAP/AXL knowledge is starting to breakdown.

Thank You in advance for your help - Brian

antyeung · ‎06-04-2014

I'm not sure if this is what you're looking for but have you tried looking at the WSDL that is included in the AXL Toolkit that can be downloaded from the "Application > Plugins" menu in CUCM?

There should be a executeSQLQuery soap request method where you can execute sql queries against the DB.

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/8.5">

<soapenv:Header/>

<soapenv:Body>

<ns:executeSQLQuery sequence="?">

</ns:executeSQLQuery>

</soapenv:Body>

</soapenv:Envelope>

Thanks,

Anthony

dstaudt · ‎06-05-2014

Unfortunately the list of SOAP services you show above is missing the one which provides the functionality you are looking for (executing SQL against the UCM database)! The Administrative XML Layer (AXL) is the interface you want.

Note, this service - and corresponding - WSDL+XSD is multiple orders of magnitude larger than the very small 'serviceability SOAP' interfaces above (like Control Center Service), and if you are only looking to do SQL queries, trying to use a full WSDL consumer/stub-compiler tool-chain (in something as off the beaten track as Powerscript) is probably serious overkill. As shown above, the actual XML that needs to be POSTed to AXL to effect an executeSqlQuery request is simple, and unchanged from request to request except for the actual SQL string - it is likely a lot easier to deal with the request as a simple XML object (or even simpler, as strings to concatenate/manipulate).

A few more notes:

- Re your proxy object for Control Center Service: the core list of operations is basically what is here: Cisco Unified Communications Manager XML Developers Guide, Release 9.1(1) - Serviceability XML API [Cisco Unified Commun…. one example being the main request 'soapDoControlServices'. I assume the method of the same name in your proxy is what you would want to use

- There seem to be a set of XXXAsync methods created as adjuncts of the basic (synchronous) requests, e.g. 'soapDoControlServicesAsync'. I believe this provides an alternate method of making the same request, but wrapping it in an automatically generated asynchronous framework. I assume the BeginXXX and EndXXX methods are intended to be used as events back into the application code to signal the start/end of using one of these XXXAsync calls.

- As noted by gcheria above, the WSDL for AXL is not available for direct download from the AXL service (e.g. 'AXLurl?WSDL'), but is provided in a .zip file (with some other tools) as part of the 'AXL SQL Toolkit'. This may throw a curve if you are pursuing the WSDL consumer path, as the WSDL is not available 'live'.

- The proxy object appears to create some default/accessory/miscellaneous methods which are probably generic for any generated SOAP stub, e.g. 'Abort', 'CancelAsync', 'GetLifetimeService'. Not sure what these do (some are probably related to the asynchronous call pattern), but you can probably ignore anything not on the basic list of requests for the service.

- Just quickly searching the web, there seems to be good support for XML manipulation and making HTTP requests in powershell, e.g. Execute a SOAP request from Powershell - IISLogs.com