Table name in SQL for partition

Answered Question
Jul 23rd, 2009
User Badges:

If I wanted to do an SQL search in CUCM6.1.2 for calls made by a particular partition what would I use for table name?


The partition we have setup for some users is: pt-Hastings


So I was wondering how do I list my query:

run sql select callingpartynumber, originalcalledpartynumber, finalcalledpartynumber, duration, datetimestamporigination, datetimestampdisconnect, "partition(??)" from car:tbl_billing_data where "partiton" IN ('pt-Hastings')

Correct Answer by Steffen.Baier about 8 years 3 days ago

Sorry, was to quick..You want CDR ...


Info:

callingPartyNumber = 5 Digit Extension Number of the Calling Party

originalCalledPartyNumber = 5 Digit Extension or Longdial Number with 0 for Access Code or 97xxx

finalCalledPartyNumber = 5 Digit Extension or Longdial Number with 0 for Access Code or 97xxx

originalCalledPartyNumberPartition = example P-Trunk-Select

callingPartyNumberPartition = example P-IPphones

finalCalledPartyNumberPartition = example P-Trunk-Select

origDeviceName = SEPxxxxxxxxxxxx 12 Digit in Capitals

destDeviceName = example [email protected]


Example:


select * from CallDetailRecord where callingPartyNumber='58999'

gives you exactly all calls made by calling Party 58999


select * from CallDetailRecord where callingPartyNumber like '5899%'


gives you calls made by the digits before the % and

any length behind it.


0004969% would cover all calls to Frankfurt Area Code Germany


select * from CallDetailRecord where finalCalledPartyNumber like '0004969713671_8'

would give you the exact range from

0->9 before the last digit


  • 1
  • 2
  • 3
  • 4
  • 5
Overall Rating: 5 (1 ratings)
Loading.
Steffen.Baier Fri, 07/24/2009 - 03:50
User Badges:

Have a look if you find it here:


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

Correct Answer
Steffen.Baier Fri, 07/24/2009 - 03:53
User Badges:

Sorry, was to quick..You want CDR ...


Info:

callingPartyNumber = 5 Digit Extension Number of the Calling Party

originalCalledPartyNumber = 5 Digit Extension or Longdial Number with 0 for Access Code or 97xxx

finalCalledPartyNumber = 5 Digit Extension or Longdial Number with 0 for Access Code or 97xxx

originalCalledPartyNumberPartition = example P-Trunk-Select

callingPartyNumberPartition = example P-IPphones

finalCalledPartyNumberPartition = example P-Trunk-Select

origDeviceName = SEPxxxxxxxxxxxx 12 Digit in Capitals

destDeviceName = example [email protected]


Example:


select * from CallDetailRecord where callingPartyNumber='58999'

gives you exactly all calls made by calling Party 58999


select * from CallDetailRecord where callingPartyNumber like '5899%'


gives you calls made by the digits before the % and

any length behind it.


0004969% would cover all calls to Frankfurt Area Code Germany


select * from CallDetailRecord where finalCalledPartyNumber like '0004969713671_8'

would give you the exact range from

0->9 before the last digit


j.mccartney Fri, 07/24/2009 - 06:16
User Badges:

Thanks for your help, I was able to get the query working from your posting. It works great!


Thanks again.


John

Actions

This Discussion