Need to find out how oftern a translation pattern is getting hit

[jason-calbert_2]

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

[pklos]

You can build query based on different entries in originalCalledPartyNumber and finalCalledPartyNumber. original before TP and final after TP.

--

regards,

PK

[jason-calbert_2]

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

[pklos]

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

[jason-calbert_2]

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

[pklos · Accepted solution]

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]'

[jason-calbert_2]

Thanks for the help this looks like it worked

Jason

[Tanya Smith]

How can i create the same inquiry in CallManager 5.1?