Cisco Support Community
cancel
Showing results for 
Search instead for 
Did you mean: 
Announcements

Welcome to Cisco Support Community. We would love to have your feedback.

For an introduction to the new site, click here. And see here for current known issues.

New Member

CALLMANAGER MSSQL DataBases

WHERE CAN I READ ABOUT MS SQL DATABASES USED WITH CALLMANAGER?

FOREXAMPLE I WANT TO SELECT ALL PHONES FROM ADM.WEB.PAGE "DEVICE->PHONE" BY SQL QUERY

HOW CAN I DO THIS?

I FOUND ONLY TABLE "NUMPLAN" THAT CONTAINS ALL DIALPLAN, BUT THERE IS ALSO DELETED SAVINGS, AND I WANT TO SELECT ONLY REAL DIAL NUMBERS AND PHONES

17 REPLIES
Cisco Employee

Re: CALLMANAGER MSSQL DataBases

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

HTH

HTH

java

if this helps, please rate

www.cisco.com/go/pdi

Re: CALLMANAGER MSSQL DataBases

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 d.name as 'Device', d.Description as 'Description',

n.dNOrPattern as 'DN', rp.name 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 d.name like 'SEP%'

order by n.dNOrPattern, rp.name

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.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Re: CALLMANAGER MSSQL DataBases

Very useful script! Can I use with this script also External Phone Mask and IP address?

New Member

Re: CALLMANAGER MSSQL DataBases

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

Re: CALLMANAGER MSSQL DataBases

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.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Re: CALLMANAGER MSSQL DataBases

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

UserDeviceProfileName,

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 !

Regards

Steffen

Re: CALLMANAGER MSSQL DataBases

Steffen,

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.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Re: CALLMANAGER MSSQL DataBases

Hi Bill,

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

THX

Steffen

New Member

Re: CALLMANAGER MSSQL DataBases

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

As I think, I should use CDR table here?

Re: CALLMANAGER MSSQL DataBases

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.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Re: CALLMANAGER MSSQL DataBases

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

Re: CALLMANAGER MSSQL DataBases

Guy,

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: o=cisco.com,ou=CCN,ou=profiles. While users are located in the o=cisco.com,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.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

New Member

Re: CALLMANAGER MSSQL DataBases

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

New Member

Re: CALLMANAGER MSSQL DataBases

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.

\\10.2.0.20\c$\CiscoWebs\Admin\userprefsphones.asp

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

Re: CALLMANAGER MSSQL DataBases

Actually,

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

my $ccmPhoneIPAddress = '1.3.6.1.4.1.9.9.156.1.2.1.1.15';

my $ccmPhoneRegistered = '1.3.6.1.4.1.9.9.156.1.2.1.1.7';

my $ccmPhoneDescription ='1.3.6.1.4.1.9.9.156.1.2.1.1.4';

my $ccmPhoneType = '1.3.6.1.4.1.9.9.156.1.2.1.1.18';

my $ccmPhoneProductIndex ='1.3.6.1.4.1.9.9.156.1.1.8.1.3';

HTH, please rate useful posts!

Chad

New Member

Re: CALLMANAGER MSSQL DataBases

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.

CCM4.2(3)

Re: CALLMANAGER MSSQL DataBases

Louis,

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: http://www.cisco.com/public/sw-center/netmgmt/cmtk/mibs.shtml

Information on CISCO-CCM-MIB for your version:

http://www.cisco.com/en/US/docs/voice_ip_comm/cucm/service/4_2_3/ccmsrvs/ssappdxa.html

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

Hope this helps.

Regards,

Bill

HTH -Bill (b) http://ucguerrilla.com (t) @ucguerrilla

Please remember to rate helpful responses and identify

550
Views
25
Helpful
17
Replies