How to improve response to multiple queries

Version 1
    This document was generated from CDN thread

    Created by: Gordon Ross on 28-10-2009 10:13:56 AM
    I'm using the AXL SQL API into our CUCM 6.1 system.
     
    Often, I need to get information on multiple devices. At the moment, I'm making separare AXL calls for each device. (There is no logical CUCM grouping to these devices)
     
    The time taken for one or two requests I find is fine. The problem is when I need to request information on, say, 20 or 30 devices. The total turn around time is quite slow.
     
    Can anyone suggest ways to make this quicker for multiple queries ?
     
    Thanks,
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: David Staudt on 28-10-2009 04:15:17 PM
    AXL can process AXL requests in parallel, though you'll want to be careful about this. 
     
    You may also want to look into writing a direct SQL query via the AXL <executeSQLQuery> request.  The Data Dictionary document provides details on the UCM tables/fields, though sometimes it may take a bit of puzzling to figure out which tables correspond to which AXL/CCMAdmin objects.
     
    For significant amonts of data <executeSQLQuery> can often be very efficient, as you can query for only the exact data your app needs - some of the 'thick' AXL requests - like getPhone - query/join many different tables and compiles/returns a lot of data.  Typically apps are only interested in a few fields.  The downside of SQL is that your app is then directly dependent on the underlying database schema, which could change from release to release - while 'thick' AXL has a backward compatibility goal of 2 years/2 major releases.

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 28-10-2009 04:37:00 PM
    I'm using the AXLSQL Interface at the moment (I'm getting familiar with the table structures ;-) )
     
    What I'm asking about is, what if I want to execute
     
    SELECT fkcallingsearchspace_sharedlineappear FROM numplan WHERE dnorpattern=XXXX
     
    for multiple values of XXXX.
     
    At the minute I'm issuing individual AXL requests for each DN, but this is slow. One way round it might be to create a rather large WHERE clause:
     
    SELECT fkcallingsearchspace_sharedlineappear FROM numplan WHERE dnorpattern=XXXX OR dnorpattern=XXXX OR dnorpattern=XXXX OR....
     
    GTG
     

    Subject: RE: How to improve response to multiple queries
    Replied by: David Staudt on 28-10-2009 05:28:14 PM
    How about something like:
     
    select fkcallingsearchspace_sharedlineappear from numplan where dnorpattern in ['1000','1001,'1002',...]
     
     

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 29-10-2009 08:06:06 AM
    Almost... Your SQL didn't work for me.  A bit of guess work gave me:
     
    SELECT dnorpattern, fkcallingsearchspace_sharedlineappear FROM numplan WHERE dnorpattern IN ('1000', '1001', '1002', '1003')
     
    which works a treat.
     
    Thanks !
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: Daniel Edwards on 29-10-2009 11:41:18 PM
    In case you are interested in another way to retrieve information on a Device you could do:
     
    <?xml version=\"1.0\" encoding=\"UTF-8\"?>
    <soapenv:Envelope xmlns:soapenv=\"<a>http://schemas.xmlsoap.org/soap/envelope/</a>]\" xmlns:xsd=\"<a>http://www.w3.org/2001/XMLSchema</a>]\" xmlns:xsi=\"<a>http://www.w3.org/2001/XMLSchema-instance</a>]\">
      <soapenv:Body>
        <ns1:SelectCmDevice soapenv:encodingStyle=\"<a>http://schemas.xmlsoap.org/soap/encoding/</a>]\" xmlns:ns1=\"<a>http://schemas.cisco.com/ast/soap/</a>]\">
          <StateInfo xsi:type=\"xsd:string\"/>
          <CmSelectionCriteria xsi:type=\"ns1:CmSelectionCriteria\">
           <MaxReturnedDevices xsi:type=\"xsd:unsignedInt\">200</MaxReturnedDevices>
            <Class xsi:type=\"xsd:string\">Phone</Class>
            <NodeName xsi:type=\"xsd:string\"/>
            <SelectBy xsi:type=\"xsd:string\">Name</SelectBy>
            <SelectItems soapenc:arrayType=\"ns1:SelectItem[{0}]\" xsi:type=\"soapenc:Array\" xmlns:soapenc=\"<a>http://schemas.xmlsoap.org/soap/encoding/</a>]\">
              {1}
            </SelectItems>
          </CmSelectionCriteria>
        </ns1:SelectCmDevice>
      </soapenv:Body>
    </soapenv:Envelope>

    You will notice two variables {0} and {1}.  The {0} is replaced by how many records you are going to retrieve and the {1} is replaced by a series of strings following this template:

    <item xsi:type=\"ns1:SelectItem\"><Item xsi:type=\"xsd:string\">{0}</Item></item>

    The {0} in this template is replaced by the device name in question.

    I had to use this method to get IP addresses of phones because I was working with systems with more than 200 devices.  I simply requested a list of all devices and then broke that list up into chunks of 200 running it against this request.

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 30-10-2009 06:54:44 AM
    This actually sounds quite good, as I've posted a question in another forum asking how to get the device IP address but no-one's responded.
     
    I've tried your XML (After tweaking it) and I've got a SOAP error back. This is my XML:
     
    <?xml version="1.0" encoding="UTF-8"?>
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      <soapenv:Body>
        <ns1:SelectCmDevice soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/" xmlns:ns1="http://schemas.cisco.com/ast/soap/">

          <StateInfo xsi:type="xsd:string"/>
          <CmSelectionCriteria xsi:type="ns1:CmSelectionCriteria">
           <MaxReturnedDevices xsi:type="xsd:unsignedInt">200</MaxReturnedDevices>

            <Class xsi:type="xsd:string">Phone</Class>
            <NodeName xsi:type="xsd:string"/>
            <SelectBy xsi:type="xsd:string">Name</SelectBy>
            <SelectItems soapenc:arrayType="ns1:SelectItem[200]" xsi:type="soapenc:Array" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">
              <item xsi:type="ns1:SelectItem"><Item xsi:type="xsd:string">SEP001E4AA99773</Item></item>
            </SelectItems>
          </CmSelectionCriteria>
        </ns1:SelectCmDevice>
      </soapenv:Body>
    </soapenv:Envelope>
     
    But when I send this to my CUCM server (6.1) I get a SOAP error back:
     
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
      <SOAP-ENV:Header/>
      <SOAP-ENV:Body>
        <SOAP-ENV:Fault>
          <faultcode>SOAP-ENV:Client</faultcode>
          <faultstring>No Handler found for SelectCmDevice</faultstring>
          <detail>
            <axl:Error xmlns:axl="http://www.cisco.com/AXL/API/6.0">
              <axl:code>5003</axl:code>
              <axl:message>No Handler found for SelectCmDevice</axl:message>
              <request>SelectCmDevice</request>
            </axl:Error>
          </detail>
        </SOAP-ENV:Fault>
      </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>
     
    I'm sending this to the normal AXL interface (https://1.2.3.4:8443/axl)
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: Daniel Edwards on 30-10-2009 12:36:46 PM
    Two things:
     
    1. In your <SelectItems> tag the value in SelectItem is the count of items you are passing not the maximum results you want.
    2. This uses the RIS service so like the SQL call it gets summited to /realtimeservice/services/RisPort

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 30-10-2009 12:56:05 PM
    OK, I changed my XML.
     
    But I can't submit the request.
     
    I changed the URL to be https://1.2.3.4:8443/realtimeservice/services/RisPort and gave the user read access to the "SOAP Performance Informations APIs" & "SOAP Realtime Informations and Control Center APIs". But when I submit the SOAP request, I get an HTML formated error back saying I don't have permission.
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: Daniel Edwards on 30-10-2009 02:29:23 PM
    I don't have access to my dev environment or workstation right now so I can't go test it real quick but do you have the Applications Administrator account?  If you do try using that for the request, if you get results we know it is just a matter of getting security premissions correct if it doesn't it means there is something else going on here.

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 30-10-2009 02:54:02 PM
    Using my normal account and I get a nice SOAP reply. (Why can't it give a SOAP error message, rather than a flipping HTML error to a SOAP question ?)
     
    Now to start digging and see what perms it needs to have. I suspect it needs to be an end-user account, rather than an application account.
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: Daniel Edwards on 30-10-2009 03:36:12 PM
    I remember vaguely having to do something special when I deployed the app to a system not using the Application Administrator.  I suspect the reason you are getting the HTML is you aren't even being allowed to submit the request due to the account being used.

    Subject: RE: How to improve response to multiple queries
    Replied by: Gordon Ross on 30-10-2009 03:38:26 PM
    I've got to be in tomorrow, so I'll have a dig/play then.
     
    Thanks,
     
    GTG

    Subject: RE: How to improve response to multiple queries
    Replied by: David Staudt on 30-10-2009 08:17:49 PM
    I believe the minimum roles necessary to use the Risport API are:
     
    Standard CCM Admin Users
    Standard SERVICEABILITY Read Only
     
    You may need to create a new user group with these roles.