How to query CUCM DB or one of the AXL APIs to list phone image load information

[Victoria Taxdal]

Is there a simple way to list phone name, model, and image load information for all phones in the CUCM system?

Could some kind soul please tell me if this is even possible (list phone versions, preferably with name and model)?

I see that I can do it for a single phone using getPhone and specifying the loadInformation tag.

But I cannot do it as a list for all phones using listPhone.  And I don't have enough familiarity with the database and have run out of stamina looking through the database dictionary trying to figure out how to do this with a sql query.

Here is as far as I have gotten, using just getPhone -- I hope the answer is not going to be to execute this query thousands of times, once for each phone?

XML query file:

<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:getPhone sequence="?">
        <!--You have a CHOICE of the next 2 items at this level-->
            <name>SE123456ABCDEF</name>
            <returnedTags>
               <name></name>
               <model></model>
               <loadInformation></loadInformation>
            </returnedTags>
        </ns:getPhone>
    </soapenv:Body>
</soapenv:Envelope>

CLI query syntax:

 curl -kqs -u <username> -H "Content-type: text/xml;" -H "SOAPAction:CUCM:DB ver=8.5" -d @getPhone.xml https://<cucmservername>:8443/axl/ | xmllint --format -

Response:

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <ns:getPhoneResponse xmlns:ns="http://www.cisco.com/AXL/API/8.5">
      <return>
        <phone ctiid="515" uuid="{12AF9876-FE0A-ABCD-1234-AB1234BA987A}">
          <name>SEP123456ABCDEF</name>
          <model>Cisco 8945</model>
          <loadInformation special="false">SCCP894x.9-4-2SR1-2</loadInformation>
        </phone>
      </return>
    </ns:getPhoneResponse>
  </soapenv:Body>
</soapenv:Envelope>

Vikki (still very much a novice with the APIs)

[Aaron Harrison]

Hi Vicki

You can easily script curl to iterate through an export of 'all phones' - pick out the dev name column and feed it into a command line to execute CURL.

When I'm not in the mood for programming I often build CURL command lines in Excel with 'concatenate' statements and an exported list of phone names.

If I recall correctly 'listphone' disappeared a few versions ago, so you need to retrieve dev lists from SQL via AXL.

Last time I checked not all devices populated the 'load information' element in CCM. I wrote an application that can inventory all the phones on a cluster - it reads from AXL, RIS, and also connects directly to each phone to collect settings from that such as firmware version, CDP info, TFTP config, etc etc. It's called Phone Operations Manager and is available at www.ipcommute.co.uk. There is a free/evaluation version that does basic inventory function.s

Regards

Aaron

[Victoria Taxdal]

Thank you Aaron.

Yes, I can script curl to iterate through a list gotten from SQL via AXL.  But if I do it the way I made it work with the single phone (per the getPhone query in my original post), then I will be firing a kazillion separate requests to the API and getting back a kazillion answers instead of asking it just one question and getting a long list for an answer.  Would that not be hard on the CUCM server?  (And how can I tell???)

Like you, I also wrote a script that reads from AXL, RIS, and the phones themselves.  I get the phone status, protocol, model, and description from RIS, and the serial number and CDP info from the phones via HTTP.  

The problem is, I can't do it with devices that use HTTPS instead of HTTP, because I can't log into them, whereas CUCM *can* talk to them.  That's why I was hoping to do this by SQL query alone.  Isn't it possible?  Is the only solution going to be to get what I'm looking for from as many phones as will talk to me over http and then queue the rest up for getPhone queries?

Vikki

[Victoria Taxdal]

By the way, you both helped me answer my own question, which is probably the best thing in the long run, and you both enriched my understanding of the APIs.  In the end, I used the listPhone query exactly as originally posted, as it gave me the best result (now that I actually *understand* the result).  I also compared those results with the ones from Gordon's query; the loadInformation and speicalLoadInformation output matched.  I will probably also go ahead and construct the http query to all the phones the way Aaron did as well, in the interest of seeing if the phones have a different point of view from the CallManager.    Thanks again, Vikki

[Aaron Harrison]

Hi Vikki

You should be able to query HTTPS the same as HTTP on the phones no? Is this a problem with your calling code?

Bear in mind that queries to AXL/SQL are administrative. As you've seen they show you configuration - that MAY be what you want.

If you want the active load info (e.g. showing phones that have not updated or for whatever reason run different loads) you need to talk to RIS or the phones themselves.

Aaron 

[Victoria Taxdal]

Thanks Aaron.  

I did not notice before that I can get the ActiveLoadID from RIS.  

Thanks for pointing that out; I will try it that way.

(Can't believe that was just staring me in the face and I just passed right over it!!)

Vikki

[Victoria Taxdal]

Yep, RisPort gives me exactly what I was looking for.  Thank you again Aaron.  

Query to RIS is the correct answer if not to the question as posted then to the the actual requirement , which is to get the AppLoadID for each phone.  

For some reason, I missed it in RIS and was trying to get it from the SQL DB.  But from the exercise with the SQL query I did at least learn that the SQL DB will list which phones are not running the default load files.

[Aaron Harrison]

Hi Vikki

Good to hear you are getting what you need :-)

Aaron

Please remember to rate useful responses and mark threads 'answered' when appropriate to highlight useful content..

[Gordon Ross · Accepted solution]

On the publisher:

admin: run sql SELECT device.name, device.description, device.specialloadinformation, typemodel.name, typedeviceprotocol.name FROM device, typemodel, typeclass, typedeviceprotocol WHERE device.tkmodel = typemodel.enum AND device.tkclass = typeclass.enum AND typeclass.name = 'Phone' AND device.tkdeviceprotocol = typedeviceprotocol.enum

GTG

[Victoria Taxdal]

Thank you Gordon.  

I tried your query but got the same kind of response as with listPhone.  

That is, with listPhone, I asked for loadInformation and got back a line that said <loadInformation/>, but no value with it.  

And with your query, we are asking for specialloadinformation and get back a line that says "<specialloadinformation/>", but no value.

Here is my query file using your sql syntax:

<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="?">
      <sql>SELECT device.name, device.description, device.specialloadinformation, typemodel.name, typedeviceprotocol.name FROM device, typemodel, typeclass, typedeviceprotocol WHERE device.tkmodel = typemodel.enum AND device.tkclass = typeclass.enum AND typeclass.name = "Phone" AND device.tkdeviceprotocol = typedeviceprotocol.enum</sql>
    </ns:executeSQLQuery>
  </soapenv:Body>
</soapenv:Envelope>

And here is a sample of the response --why don't we see the phone name?  and the load file name???

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
  <soapenv:Body>
    <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/8.5">
      <return>

        <row>
          <name>SCCP</name>
          <description>AB-SomePhone-31234</description>
          <specialloadinformation/>
          <name>SCCP</name>
          <name>SCCP</name>
        </row>

        <row>
          <name>SIP</name>
          <description>CD-SomeOtherPhone-41234</description>
          <specialloadinformation/>
          <name>SIP</name>
          <name>SIP</name>
        </row>

Can you tell me what's wrong?  Also, why can't I find the place in the data dictionary that should tell me how to get this information?  How do you actually get to know this information??

[Gordon Ross]

If the load information is blank, it means the device is using the system default.

All the database table information is in the data dictionary PDF (all 1,000-odd pages) trust me. The challenge is finding what you want in the data dictionary :-/ Oh - and some things in the data dictionary are called different things than in CCMAdmin.

GTG

[Vikki Taxdal]

OK, I did my homework and slogged my way through that data dictionary enough to figure out how to extract exactly the information I need, no more and no less, with the SQL in these two XML files, which I send to CUCM:DB using curl:

1. This gets me the default firmware listing, but just for the models we are actually supporting:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/10.5">
<!--Like https://[CCM-IP-ADDRESS]/ccmadmin > Device > Device Settings > Device Defaults -->
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="">
         <sql>
            SELECT count(dev.tkmodel), dev.tkmodel AS tkmodel, tp.name, defaults.loadinformation
            FROM device AS dev
            INNER JOIN TypeProduct AS tp ON dev.tkmodel=tp.tkmodel
            INNER JOIN defaults as defaults ON tp.tkmodel=defaults.tkmodel
            WHERE dev.name like 'SEP%' AND defaults.loadinformation IS NOT NULL
            GROUP BY dev.tkmodel, tp.name, defaults.loadinformation
         </sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

2.  And this gets me just the phones which are running something other than the default, along with model, description, and specialloadinformation:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/10.5">
<!-- Like https://[CCM-IP-ADDRESS]/ccmadmin > Device > Device Settings > Device Firmware Load Information -->
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="">
         <sql>
            SELECT d.name, d.specialloadinformation, d.description, tp.name AS model
            FROM device AS d
            INNER JOIN TypeProduct AS tp ON d.tkmodel=tp.tkmodel
            WHERE d.name like 'SEP%' AND d.specialloadinformation != ''
         </sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

[Victoria Taxdal]

Hmmm... OK.  I will need to do a deep dive into that document with a nearby sandbox then.  

In the meantime:

A.  What happened to phone name, description, and model in the response to the query you showed me?

B. Do you know a query that will list the defaults for loadInformation and specialLoadInformation by model?

C. We just downgraded a phone to something other than the default, in order to test whether the results we are getting from these queries (your sql query and my listPhone query) will provide a value in the case we have a phone running something other than the default.  But we still get no value for either your specialloadinformation field or my loadInformation tag.  Any idea why not?

[Victoria Taxdal]

Oops... scratch item C... I was looking at the result wrong.  Both queries *do* show exceptions to the default, and they list what the exception is (loadfilename).

[Gordon Ross]

A) SELECT device.name AS devicename, device.description AS devicedescription, device.specialloadinformation, typemodel.name AS model, typedeviceprotocol.name AS protocol FROM device, typemodel, typeclass, typedeviceprotocol WHERE device.tkmodel = typemodel.enum AND device.tkclass = typeclass.enum AND typeclass.name = 'Phone' AND device.tkdeviceprotocol = typedeviceprotocol.enum