SQL query for call details

Version 1
    This document was generated from CDN thread

    Created by: Srikanth B on 12-10-2009 10:18:50 AM
    Hi,
     
    Is it possible to get the real time call details by executing SQL query and accessing Call Manager database?
     
    Which tables in CUCM provide below information:
    1. For active phones
    2. Concurrent call details
    3. Registered Users info
    4. Call state
     
    Which CUCM tables will provide these information? Any help in this regard is highly appreciated.
     
    thanks,
    Srikanth.

    Subject: RE: SQL query for call details
    Replied by: David Staudt on 12-10-2009 02:24:35 PM
    Information associated with real-time call state is not stored in the database (rather in in-memory running code structures.)  If you need to monitor real-time events and status of phones, you will need to look into the CTI interfaces (TAPI/JTAPI)
     
    I'm not sure what you mean by '3. Registered Users info' ?

    Subject: RE: SQL query for call details
    Replied by: Srikanth B on 13-10-2009 06:08:38 AM
    Information associated with real-time call state is not stored in the database (rather in in-memory running code structures.)  If you need to monitor real-time events and status of phones, you will need to look into the CTI interfaces (TAPI/JTAPI)
     
    I'm not sure what you mean by '3. Registered Users info' ?


     
    I mean 'Registered Phones with Call Manager' details. Apart from CTI interface option, is it possible to get this list of Phone details(like Registered, Unregistered etc,) from Call Manager?

    Subject: RE: SQL query for call details
    Replied by: Stephan Steiner on 13-10-2009 09:35:20 AM
    You mean like the devicelistx report? Yes you can do that.. Look at the RisPort in XML Serviceability: http://developer.cisco.com/web/sxml/home.
    Be aware though that you won't get all the sates - if memory serves me right, you will not get the devices in unknown state. Those you have to get by doing a diff between all the devices and the ones that the RisPort (the SelectCmDevices command to be precise) returns. And since SelectCmDevices is limited to returning 200 devices per call, you need to use AXL first anyway.
     
    So the process is:
    Use AXL's selectSqlQuery command to extract all devices (e.g. with an SQL command like SELECT name FROM device WHERE name LIKE 'SEP%'), then for every 200 devices, do a SelectCmDevices command, and for the phones that you queried but got no response you know they're in state unknown (or was it unregistered? either way it's pretty easy once you start making the calls.. there'll be a bunch of devices for which you won't get a result and you can look those up in the ccmadmin pages).
     
    For real time information about call state, you will need to use a CTI interface though. And for extension mobility information (who is logged in on which phone or is somebody logged in on phone x? you need to look at the extension mobility api: http://developer.cisco.com/web/emapi/home (though you can also get the information formulating appropriate sql queries and sending them via AXL... the syntax is different for CCM4, 5 and 6+ though so be careful when you're working against multiple CCM versions). The EM API is more stable so you might just want to use that if you're unfamiliar with the CCM's SQL database.