SQL query listing devices with a specific XML config

[MityoValchev32163]

Hello,

My customer is running on a CUCM 12.5 deployment with phones and respective Extension mobility profiles logged in on them. We need an SQL query which will list those devices with those profiles who have a specific value in their running XML config. More specifically, we need the show those devices that have a very specific URL behind their Login/Logout button.

Thanks in advance.

[Alexander Stevenson]

Hello @MityoValchev32163,

You should check out the:

DevNet Automation Exchange

https://developer.cisco.com/network-automation/

and

Cisco DevNet Code Exchange

https://developer.cisco.com/codeexchange/

[dstaudt]

Normally you would retrieve a list of IP Phone services/URLs using the AXL API via getPhone

However, this doesn't really work when you want to query all phones, as you'd have to retrieve each phone one-by-one in a separate request to check.  This is where the executeSqlQuery request can help, as we can (if we are familiar with the CUCM SQL database schema) ask for only the info we need, joining the tables desired.

Per the Data Dictionary the tables of interest would be:

• telecasterservice - holds the service definitions, e.g. name and URLs
• telecastersubscribedservice - maps service to devices, also happens to include the service URL
• device - holds details about each device, i.e. the device name

This is the AXL request/response using an example SQL query:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns="http://www.cisco.com/AXL/API/14.0">
   <soapenv:Header/>
   <soapenv:Body>
      <ns:executeSQLQuery sequence="?">
         <sql>SELECT fkdevice, name FROM telecastersubscribedservice,device
              WHERE serviceurl LIKE 'http://testService.example.com%' AND
              telecastersubscribedservice.fkdevice=device.pkid</sql>
      </ns:executeSQLQuery>
   </soapenv:Body>
</soapenv:Envelope>

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/">
   <soapenv:Body>
      <ns:executeSQLQueryResponse xmlns:ns="http://www.cisco.com/AXL/API/14.0">
         <return>
            <row>
               <fkdevice>6118ec77-b351-0aa9-cac4-46c26fd8edc6</fkdevice>
               <name>IPCMRAEU5UCM5X7</name>
            </row>
         </return>
      </ns:executeSQLQueryResponse>
   </soapenv:Body>
</soapenv:Envelope>

Note the '%' in the LIKE statement acts as a wildcard.

A number of AXL sample code projects are available on Code Exchange, with the Python project being the most complete.

[dstaudt]

One note, 'enterprise' service subscriptions (where a service is defined as being present on all devices) will not have entries in telecastersubscribedservice...you can just use the telecasterservice.enterprisesubscription field of services with your URL to assume all devices.

[dhellwig]

Those queries look nice.

I am looking for a way to update hundreds of phones whose Button Template allows two IP Phone Service URLs.

The phones are currently subscribed to CAD Login and I can use the Bulk Edit to subscribe the phones to the Finesse Login

but do not know how to update the button instances.

Thanks!

D

[MityoValchev32163]

After some research we put together the following SQL queries:

run sql select d.name, d.description, tss.servicename, tss.serviceurl from device as d inner join TelecasterSubscribedService as tss on tss.fkdevice=d.pkid where d.tkclass = 254 order by d.name, tss.servicename

The above will list all the UDPs and their corresponding Extension Mobility URL;

run sql update TelecasterSubscribedService as tss set tss.serviceurl = "http://cucm.xxxxxxxxxxxxxx/emapp/EMAppServlet?device=#DEVICENAME#EMCC=#EMCC#" where (fkdevice IN (SELECT ts.fkdevice AS fkdevice FROM telecastersubscribedservice AS ts INNER JOIN device AS d ON d.pkid = ts.fkdevice WHERE d.name = "NAME"))

The above will change the Extension Mobility URL to a desired one. The UDPs can be filtered by different conditions in the last clause;

Thanks!

[dhellwig]

Example of phone button configured