CCM 4.1 SQL Query Question

Unanswered Question
Apr 30th, 2007


I am trying to generate a SQL Query containing the Alerting Name out of the Device Profiles.

I am using the following query:

SELECT DISTINCT Device.Name, Device.Description, Device.LoginUserid, NumPlan.DNOrPattern, DeviceNumPlanMap.Display, DeviceNumPlanMap.Label, DeviceNumPlanMap.MaxNumCalls, DeviceNumPlanMap.BusyTrigger,

DeviceNumPlanMap.E164Mask FROM Device INNER JOIN DeviceNumPlanMap ON Device.pkid =

DeviceNumPlanMap.fkDevice INNER JOIN NumPlan ON DeviceNumPlanMap.fkNumPlan = NumPlan.pkid

WHERE (DeviceNumPlanMap.NumPlanIndex = 1)

I have this problem too.
0 votes
  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
virverma Tue, 05/01/2007 - 01:41


Would like to know the purpose for extracting alerting name out of device profiles.

If you are trying to update alerting names, then you can use BAT tool as well.


Steffen.Baier Tue, 05/01/2007 - 06:10

Quite easy ;-)

Housekeeping and I need to forward all our extension profiles (3500) to our internal billing department for CDR reasons.

Any takers ?

Steffen.Baier Thu, 05/10/2007 - 05:15


not only would I still like the Alerting name I would now also like to include the IP address.

Any takers ?



virverma Fri, 05/11/2007 - 02:14


I think you can try this, this would pull all DNs and the info:

select numplan.DnOrPattern as extension, device.description,

DeviceNumPlanMap.E164Mask as external_phone_mask,

numplan.alertingname as alerting_name, devicenumplanmap.display,

devicepool.Name as device_pool_name

from numplan, devicenumplanmap, devicepool, device

where DeviceNumPlanMap.fknumplan = numplan.pkid

and DeviceNumPlanMap.fkdevice = device.pkid

and device.fkdevicepool = devicepool.pkid

and numplan.tkPatternUsage = 2

order by numplan.DnOrPattern

this would pull something like:

extension description external_phone_mask

alerting_name display device_pool_name

5874 SEP123456785499




SteffenBaier Mon, 05/14/2007 - 05:03


thanks for your help ;-)

I have now created the following:

select NumPlan.DNOrPattern as DirectoryNumber, Device.Name as DeviceName, Device.Description as UserDeviceProfileName, Device.LoginUserid as UserId, DeviceNumPlanMap.Display as DisplayIntCallerID,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

And I get:


This all is already fantastic !

One last request would be the IP of the Device and 7970 and 7936 and 7920 as one result ;-)

Thanks from the UK


Steffen.Baier Wed, 10/31/2007 - 03:46


I am still after the IP address of the phone and the registered CCM.

I would need the right Table in the CCM0300 Database to get to this but I have not yet found it.

I know the CDR Part of the Database keeps that Info but if I check in Call Manager Devices and then select phones it shows the CCM Ip and the Phone IP.

I have tried Cisco CallManager Serviceability and it does exatecly that but only shows 200 Entry's and has no export feature.

Cisco Works can export the data but he shows the List based on DN's and therefore if a DN is on more than 1 phone it shows the IP's for every Phone that has that DN.

I would rather only see the Primary DN & the IP's

Any takers ?




This Discussion