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.
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:
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.
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.
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.
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;