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

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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Silver

Re: Need to find out how oftern a translation pattern is getting

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

7 REPLIES
Silver

Re: Need to find out how oftern a translation pattern is getting

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

--

regards,

PK

Re: Need to find out how oftern a translation pattern is getting

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

Silver

Re: Need to find out how oftern a translation pattern is getting

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

Re: Need to find out how oftern a translation pattern is getting

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

Silver

Re: Need to find out how oftern a translation pattern is getting

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

Re: Need to find out how oftern a translation pattern is getting

Thanks for the help this looks like it worked

Jason

Community Member

Re: Need to find out how oftern a translation pattern is getting

How can i create the same inquiry in CallManager 5.1?

184
Views
0
Helpful
7
Replies
CreatePlease to create content