Unanswered Question
Feb 1st, 2008
User Badges:





  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (5 ratings)
Jaime Valencia Fri, 02/01/2008 - 15:23
User Badges:
  • Cisco Employee,
  • Hall of Fame,


there was none document about CCM DB before CUCM 5, starting on that version a data dictionary became available.

you can associate the data from the numplan table with device table using the device foreign key with an inner join so only devices that are under ccmadmin show up


William Bell Fri, 02/01/2008 - 18:36
User Badges:
  • Purple, 4500 points or more

As javalenc's post suggests there is a data dictionary with CUCM 5.x that can be a reference resource but there are a decent number of changes between the 4.x data dictionary and 5.x that you should double check before building extensive queries.

How I learned the 4.x SQL database was by using SQL query manager and looking at the table columns. I found many columns to be self explanatory.

Now, for your specific question. You need to deal with the NumPlan, Device, DeviceNumPlanMap, and RoutePartition. The NumPlan table contains all digit patterns. Patterns in the table are not unique but patterns and route partition pairs are.

Device contains all devices such as IP phones, gateways, even Route Lists. Yes, route lists are devices in CUCM 4.1 and later.

DeviceNumPlanMap is the table that matches IP phone line appearances to directory numbers. Specifically, it maps Device elements to NumPlan elements.

RoutePartition isn't necessarily required to build your query but it will resolve guid's in the NumPlan table to an actual name. Which you will appreciate rather quickly.

Some query examples:

select as 'Device', d.Description as 'Description',

n.dNOrPattern as 'DN', as 'Partition'

from Device as d

inner join DeviceNumPlanMap as dmap on dmap.fkDevice = d.pkid

inner join NumPlan as n on n.pkid = dmap.fkNumPlan

inner join RoutePartition as rp on rp.pkid = n.fkRoutePartition

where like 'SEP%'

order by n.dNOrPattern,

The 'where' clause is getting IP phones for you (but not device profiles, or analog stations, etc.). There are other ways to get phones only such as using values in the Device.tkDeviceProtocol, Device.tkClass, or NumPlan.tkPatternUsage fields. Play around and see what works for you

Some primer info.

In a given table any column with the name tk<*name*> is a reference to an enumerator in a type table. To find out which table simply substitute type for tk. So tkClass in the Device table is referring to a value in the enum column of the typeClass table.

In a given table any column with the name fk<*name*> is a reference to a pkid in another table. Again, to find out which table simply drop the fk. So fkRoutePartition in the NumPlan table is referring to the pkid column in the RoutePartition table.

This is consistent throughout the database.

Keep in mind that if you get into things like hunting down Calling Search spaces. You will find multiple references to the CSS table in a single row of the NumPlan table. In these cases you will find the convention fkCallingSearchSpace_. The gives you a clue as to what the CSS is used for and again fkCallingSearchSpace is the pkid in CallingSearchSpace.

Hope that helps.


Bill Fri, 02/08/2008 - 03:02
User Badges:

Very useful script! Can I use with this script also External Phone Mask and IP address? Fri, 02/08/2008 - 05:19
User Badges:

I know that there is statistics about delay, jitter, packet loss. Where can I find this specific info?

William Bell Fri, 02/08/2008 - 07:24
User Badges:
  • Purple, 4500 points or more

If you have Call Diagnostics enabled then this information will be written to the CMR records in the Publisher's CDR database.

The table of interest is CDR.CallDetailRecordDiagnostic

To enabled CMR records you must configure the Call Manager service on *all servers* (not a globally synch'd parameter). Specifically, 'Call Diagnostics Enabled' should be set to true.

For each record, the number of packets lost, jitter, and latency are provided in individual columns. You should note that jitter and latency are average values calculated over the entire duration of a call. So, problems in a call may smooth out over the duration.

You can use the globalCallId_* fields in a record to map the information to the call party records in the CallDetailRecord table. This would be used to get IP addresses, call leg details, node information, etc.

CMR records are written at the same time a CDR record is written. Meaning that when the call is terminated (normal or abnormal) the subscriber(s) involved in the call will write records to a file share on the Publisher. The publisher inserts (insertCDR service) records into the databases. So, allow a couple of seconds for the records to be written.

You can also scrape HTTP output from IP phones during an active call to get information about jitter, latency, packet loss. This is not an operation that is recommended for real time polling solutions but you can get snapshots of information if you wanted to. I have found that scraping HTTP output from IP phones aggressively will eventually cause the HTTP server on the phone to become unresponsive.

FYI. All of this information is for 4.1 only. In later versions some things have changed.

Hope that helps.



Steffen.Baier Fri, 02/08/2008 - 05:42
User Badges:

select NumPlan.DNOrPattern as DirectoryNumber, Device.Name as DeviceName, Device.Description as


Device.LoginUserid as UserId,

DeviceNumPlanMap.Display as DisplayIntCallerID,

DeviceNumPlanMap.tkRingSetting as Ringsetting,

DeviceNumPlanMap.NumPlanIndex as NumPlanIndex,

DeviceNumPlanMap.Label as LineTextLabel,

numplan.alertingname as AlertingName,

DeviceNumPlanMap.MaxNumCalls, DeviceNumPlanMap.BusyTrigger,

TypeModel.Name AS PhoneType, DeviceNumPlanMap.E164Mask as ExtPhoneMask, NumPlan.CFADestination as

CallFWDALL, NumPlan.CFNADestination as CallFWDNoAnswer, NumPlan.CFBDestination as CallFWDBusy,

devicepool.Name as DevPoolName from numplan, devicenumplanmap, devicepool, device ,TypeModel

where DeviceNumPlanMap.fknumplan = numplan.pkid

and DeviceNumPlanMap.fkdevice = device.pkid and device.fkdevicepool = devicepool.pkid and TypeModel.Name = 'Cisco

7970' and numplan.tkPatternUsage = 2

order by numplan.DnOrPattern

Above works fine on 4.1.3 !

I am personally after something like from Cisco CallManager Serviceability Tool.

Basically there you can choose the Device Option,then Phone, choose all Registered phones, for example then all 7970 and then on Finish and it displays you all Phones incl. the Logged on Extension (Using Extension Mobility) The IP, the Mac and even in a row the Primary Extension and all shared DN's on the Phone.

It only does 200 at a time and you cannot export this.

Any hint for something like this for all phones would be much apriciated !



William Bell Fri, 02/08/2008 - 08:00
User Badges:
  • Purple, 4500 points or more


Well, you can emulate the RTMT (Serviceability Tool) by putting your own client script/app together and use the same URI queries that RTMT does. It is a reverse engineering task. With 4.x you can execute sample queries with RTMT and then look at IIS logs to piece commands together. This works for me.

BUT, the 200 row limit is hardcoded in the server-side application somewhere. I have tried to force it to give me more records and it won't. I never figured that one out (sorry).

With that said, you can also use the devicelistx.asp as a foundation to build your own custom server side application to get the information you want. The non-realtime information is all done via SQL, which you appear to have a good handle on. The real time information is grabbed via a COM object(s) on the CUCM host. I can't guarantee every method you want will work, but you can test that out. Keep in mind that you can use the URI to pass variables/filters of your own to devicelistx but that requires code modification of the devicelistx.asp.

As a disclaimer: Cisco doesn't support modifications of the devicelistx.asp so play at your own risk. Test on non-production systems, use proper security measures, etc.

Basically, exercise good common sense and care when trying to customize the system to meet your needs. I have found that you can get what you want and not pose a threat to stability easily by just working inside the context rules already laid out in the server side app.

Hope that helps.



Steffen.Baier Wed, 07/09/2008 - 11:23
User Badges:

Hi Bill,

sorry to Highjack this post but could you be so kind and email me some of your "scripts" ?


Steffen Mon, 02/11/2008 - 06:11
User Badges:

And the last question.How can I get the information about calls that no answered?

As I think, I should use CDR table here?

William Bell Fri, 02/08/2008 - 07:44
User Badges:
  • Purple, 4500 points or more

Yes, you can get the external phone mask with some slight modifications. The external phone mask is actually a device specific attribute and is not found in NumPlan. Recall that NumPlan contains a single instance of a digit pattern/route partition pair.

DeviceNumPlan map is the table that would include the external mask (field: e164Mask).

As far as using the IP address. In CUCM 4.1 you can use devicelistx (https:///ccadmin/reports/devicelistx.asp)

This generates an XML content page (text/xml) that you can parse. If you edit the ASP file you will see that it constructs a SQL query at the top of the file. You may tweak this to add other fields you wish to have or modify the device array (built just in front of the SQL query) to include devices you care about.

Note I have never seen anything that says Cisco supports modifications of the devicelistx.asp file but I have modified it extensively to deal with various views I want and I have never had any issues (13,000+ IP phones).

What you would need is a client script that can parse the XML content. Perl, vbscript, java script, etc. all work fine.

Devicelistx is the primary method for getting device information based on IP address. "Out of the box" you don't really query by IP but you could modify the script to accept query parameters in the URI and then kick out the devices you want.

If you look closely you will find that the SQL query in the ASP does not do anything with IP addresses. The IP address piece doesn't come in until the getRISInfo() function.

Another method exists where you can query device information using the same API that RTMT uses. This would be completely custom though and you have reverse engineer things a tad by looking at the IIS logs to piece together queries/URIs that RTMT uses (yes, RTMT is basically a web client). I don't recommend going down this road unless you are really bored. ;-)

Again, the devicelistx.asp method works in CUCM 4.x and 3.3(4) and later 3.x versions. It is not available in 5.x/6.x. In these versions you must use SOAP/AXL API.

Hope that helps.



noeparenteau Wed, 07/09/2008 - 11:07
User Badges:

I think I found a thread I can live with...

I posted this elsewhere but I try here:

I use CCM 4.1(3).

I need to "dis - associate" a device/DN from a CTI-User (Enable CTI Application Use and Enable CTI Super Provider)

When I display all the devices currently "associated" to a User - I have to page, by page (64 of them) find the exact device/DN that I need to "dis-associate".

Needless to say this is a very slow (Is it ok to say tedious) way of doing things.

Help someone out there...

Yours truly,

"Next Page" Guy

William Bell Wed, 07/09/2008 - 20:55
User Badges:
  • Purple, 4500 points or more


The device associations are actually maintained in the directory that CUCM 4.1 integrate with. By default, this is DC Directory. Specifically, the Application Profile attached to the user object.

So, there are few ways to get at the information.

One method would be to use perl to query and pull the information from LDAP. Then push the updated information back into LDAP. You would need to use LDAP to pull the user object. When doing this you would want to filter on one or a combo of the following:

cn: This will be the user ID (e.g. myCTIUser)

givenName: First name field

sn: Last name field

The user object doesn't contain the device associations but does contain attributes you will need to find the information. Specifically, the ciscoatGUID or the ciscoatUserProfile (duplicate of ciscoatUserProfileString, one of these is legacy and I forget which one). Anyway, the point is that all users have what is called a user profile in LDAP. They will also have an application profile (*CCNProfile*). Using the ciscoatGUID (my preference) you can then run a query against LDAP for a cn=*CCNProfile-. Keep in mind that in DCD (and AD) the CCNProfile object is located in a different context. For DCD:,ou=CCN,ou=profiles. While users are located in the,ou=users context.

If you go this route, the attribute in the CCNProfile that you care about is the 'ciscoCCNatControlDevices' attribute.

The second option also requires some programming and leverages the AXL/SOAP interface. You would use the getUser request to retrieve the user object. You will want to do this to get a list of associated devices. Once you have the list you would use the updateUser to change the associated devices. Keeping in mind that when you use updateUser and specify the associated devices XML tag the AXL API will erase the existing list and write what you specify.

I know this is a general/high level overview but these are the programatic paths you could follow. Depending on how you like to do things, this may be more tedious than what you are currently doing with CCMAdmin. The key point is that the associations are in DCD and not SQL.

Hope that helps.



noeparenteau Thu, 07/10/2008 - 11:36
User Badges:

Can I expand webpage size in USERPREFSPHONES.ASP for DEVICE ASSOCIATIONS from default of CHUNKLENGTH=20 to 1,000??? Then I could just use Browser's FIND to look for Device/DN I want to Dis-Associate", then UN-Check radio box...

noeparenteau Fri, 07/11/2008 - 05:38
User Badges:

Eureka...It works!

I changed the "userprefsphones.asp" code to reflect this change:

FROM : var chunkLength = 20;

TO : var chunkLength = 2000;

as I have over 1200 devices associated with this particular USER.

The display takes a while (3 seconds in the original asp, versus 11 seconds in the modified asp) to complete, but it gives me all of the devices on one web page!

All I do now is "FIND XXXX", then check the radio box to "dis-associate" the device and Vola! Done...

PS - I have four Call Managers/7845's so I tested this on one of the Subscriber's asp file.


Then in the Browser's address field, I pointed to that Subscriber's file location (see above).

Now all I have to do is wait for Mr./Ms. Cisco to find out what I did and shake his/her finger at me.

Mr. No-Page Next guy...

Chad Stachowicz Wed, 07/09/2008 - 11:27
User Badges:
  • Silver, 250 points or more


I used SNMP in all my tools to recover this information! Here are the relevant OID's!!!

my $ccmPhoneIPAddress = '';

my $ccmPhoneRegistered = '';

my $ccmPhoneDescription ='';

my $ccmPhoneType = '';

my $ccmPhoneProductIndex ='';

HTH, please rate useful posts!


louis.engelbrecht Sun, 08/17/2008 - 03:22
User Badges:

Hi Chad

can you please expand a bit on how you are using snmp to get the ip addresses of your phones?

basically what i want to accomplish is to have live ip address data of all the phones registered in callmanager.

currently i have to manually run a modified devicelistx asp page to insert the data into a sql table.


William Bell Sun, 08/17/2008 - 06:23
User Badges:
  • Purple, 4500 points or more


Hello. I know you asked Chad, but I will pipe in if that is OK. I haven't look at 4.2(3) but I believe that the CISCO-CCM-MIB is pretty much uniform across versions for the information you are looking for.

The MIB (and dependencies) can be downloaded from Cisco. See:

Information on CISCO-CCM-MIB for your version:

Information you are probably most interested in based on your query is in the ccmPhoneTable (OID: . In particular: ccmPhonePhysicalAddress, ccmPhoneDescription, ccmPhoneInetAddress, ccmPhoneStatus. Note that there is a ccmPhoneIpAddress object but it is obsolete and replaced with InetAddress.

Hope this helps.




This Discussion