Aurus Blog

This blog is to share our expertise in Cisco UCM, UCCX/UCCE and Cisco Telepresence.

  • Archive

    «   December 2017   »
    M T W T F S S
            1 2 3
    4 5 6 7 8 9 10
    11 12 13 14 15 16 17
    18 19 20 21 22 23 24
    25 26 27 28 29 30 31
                 

Querying CUCM Database from the Command Line

Why?

Some operations on CUCM objects could be made much easier and faster through CUCM database, for example - to get a list of devices, to add several devices to the list of devices controlled by some axl-user, etc.

How?

1. Download CURL for Windows - http://curl.haxx.se/download.html
2. Then any AXL-request can be executed from the command line:
curl.exe -k -u axluser:axlpass -H "Content-type: text/xml;" -H "SOAPAction: CUCM:DB ver=8.5" -d @axlreauest.xml https://ccm9.bcs-it.loc:8443/axl/

where:

a) axluser:axlpass - login and password of the CUCM Application User
b) https://ccm9.somedomain.loc:8443/axl/ - CUCM AXL Service address
c) axlreauest.xml - the file with the request, for example:

<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:executeSQLUpdate sequence="?">
<sql>
insert into applicationuserdevicemap (description, fkdevice, fkapplicationuser, tkuserassociation)
values ('', 'f37738df-222b-473c-813f-7872709ab221', '1abf2126-3339-5946-e755-6d59f368a7a5', 1)
</sql>
  </ns:executeSQLUpdate>
    </soapenv:Body>
</soapenv:Envelope>

Queries to CUCM tables are performed with the executeSQLQuery. Modification of data (insert / delete / update) – with executeSQLUpdate.

What is CUCM DB?

It is actually Informix.

To find out the exact version of the database server you can perform this query:

<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 DBINFO('version', 'full') from systables where tabid=1
</sql>
  </ns:executeSQLQuery>
    </soapenv:Body>
</soapenv:Envelope>

Read more about Informix here: http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp

The structure of the database

1. CUCM 6: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/6_0_1/dd601.pdf
2. CUCM 7: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/7_0_1/DD_701.pdf
3. CUCM 8: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/8_0_1/datadictionary_801.pdf
4. CUCM 9: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/9_1_1/datadictionary_911.pdf
5. CUCM 10: http://www.cisco.com/c/dam/en/us/td/docs/voice_ip_comm/cucm/datadict/10_0_1/datadictionary_1001.pdf

Ask Cisco about the structure of the other databases: http://tools.cisco.com/search/results/en/us/get#q=Communications+Manager+Data+Dictionary

Reference

Here's our friend having fun on CUCM SQL quieries: http://www.ucguerrilla.com/2012/03/cucm-sql-queries-series.html

Examples of requests

Get the list of All CUCM devices
select * from device

Get the list of devices controlled by the user (returns identifiers)
select * from applicationuserdevicemap
where fkapplicationuser = 'a59e7a1c-3527-c5e4-89d8-edb3e0c10dab'

Remove a bunch of devices from the “controlled devices” list
delete from applicationuserdevicemap where fkapplicationuser = 'a59e7a1c-3527-c5e4-89d8-edb3e0c10dab'
and fkdevice in (select pkid from device where name like 'zCTIPort%' and name[9,15] > 5565400)

Add a bunch of device to the “controlled devices” list
into applicationuserdevicemap (description, fkdevice, fkapplicationuser, tkuserassociation)
select '', pkid, 'a59e7a1c-3527-c5e4-89d8-edb3e0c10dab', 1 from device where name like 'zCTIPort%' and name[9,15] > 5565402

RIS Service

To test the RIS the "/ realtimeservice / services / RisPort" path should be used in the URL instead of "/ axl".

The example of the RIS-query:

<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 href="#id0"/>
</ns1:SelectCmDevice>

<multiRef id="id0" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
  xsi:type="ns2:CmSelectionCriteria"
  xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/"
  xmlns:ns2="http://schemas.cisco.com/ast/soap/">

  <MaxReturnedDevices xsi:type="xsd:unsignedInt">200</MaxReturnedDevices>
  <Class xsi:type="xsd:string">>Phone</Class>
  <Model xsi:type="xsd:unsignedInt">255</Model>
  <NodeName xsi:type="xsd:string" xsi:nil="true"/>
  <SelectBy xsi:type="xsd:string">Name</SelectBy>

  <SelectItems soapenc:arrayType="ns2:SelectItem[1]" xsi:type="soapenc:Array">
<item href="#id1"/>
  </SelectItems>

</multiRef>

<multiRef id="id1" soapenc:root="0" soapenv:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/"
  xsi:type="ns3:SelectItem"
  xmlns:ns3="http://schemas.cisco.com/ast/soap/"
  xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/">

  <Item xsi:type="xsd:string">test</Item>
</multiRef>

    </soapenv:Body>
</soapenv:Envelope>