08-16-2007 07:26 AM - edited 03-14-2019 11:06 PM
I'm trying to write a SQL query in order to find out how often a translation pattern is being hit.
I'm using a query similar to this select * from CallDetailRecord where finalcalledpartynumber='4xxxx'and having it search through the CDR database. Of course the translation pattern is not the finalcalledpartynumber so I do not get any results. Any help is appreciated. Thanks
Jason
Solved! Go to Solution.
08-17-2007 05:19 AM
when you check difference between original and final called number you'll see how often TP adds numbers. Any other call to PSTN has original and final called number the same.
So:
select * from calldetailrecord where originalcalledpartynumber like '4[0-9][0-9][0-9][0-9]' AND finalcalledpartynumber like '83263[0-9][0-9][0-9][0-9][0-9]'
08-16-2007 07:36 AM
You can build query based on different entries in originalCalledPartyNumber and finalCalledPartyNumber. original before TP and final after TP.
--
regards,
PK
08-16-2007 07:44 AM
PK,
Thanks for the responce I tried query below and came up with nothing.
select * from CallDetailRecord where originalcalledpartynumber='4xxxx'.
Should this even work.
Jason
08-17-2007 02:45 AM
Hi,
For example you translate like this:
4XXX to 845XXX
Query:
select * from calldetailrecord where originalcalledpartynumber like '4%' and finalcalledpartynumber like '845%'
or maybe better:
select * from calldetailrecord where originalcalledpartynumber like '4[0-9][0-9][0-9]' and finalcalledpartynumber like '845[0-9][0-9][0-9]'
regards,
pklos
08-17-2007 05:06 AM
I follow your logic but with my situation I don't think it is going to work. The numbers we add with the translation would match any PSTN call. The scenerio is that we have phones that are not on the callmanager yet and need to route to the PSTN but we make it appear seemless. So the route patter adds 83263XXXXX but this also matches any call to the PSTN.
I'm trying to find a way to find out how many people are hitting that translation pattern to find out if I can get rid of that translation patter.
Again thanks for your help with this
08-17-2007 05:19 AM
when you check difference between original and final called number you'll see how often TP adds numbers. Any other call to PSTN has original and final called number the same.
So:
select * from calldetailrecord where originalcalledpartynumber like '4[0-9][0-9][0-9][0-9]' AND finalcalledpartynumber like '83263[0-9][0-9][0-9][0-9][0-9]'
08-17-2007 05:21 AM
Thanks for the help this looks like it worked
Jason
08-23-2007 06:57 AM
How can i create the same inquiry in CallManager 5.1?
Find answers to your questions by entering keywords or phrases in the Search bar above. New here? Use these resources to familiarize yourself with the community: