Country code in Cdr

Asterisk Asterisk Support
1

Dear All,
i am working on a customer reporting solution based on data provided by Asterisk Cdr.
i’ve a table in DB with almost 18500 Codes around the globe.

Asterisk is stories its cdr into mysql db with the help of adaptive odbc .
What i want is to match countrycode+area code with dialed number and display in front of dialed number.And offcourse depending on highest length match of prefix and dialed number.
for example , if i’ve codes in table like 44 for UK and 441 for Uk landline , i want to have 441123123 with UK Landline only

while results match both country having prefix 44 and 441.
i am trying in SQL from last 2 days but not getting an efficient way. if a raw query does so , on each time it has to filter 18500 codes which slows down everything.

So is it some better way from asterisk itself , or may be some AGI can strip the prefix and send it to database itself instead of running some AFTER CDR function ?
Any help will be highly appreciated
regards
Ahmed

2

Although it would be possible, I think that it is probably easier to do it in the database, given that the codes are of variable length.

Also, please note that +441 is not sufficient to detect UK landline numbers. You need to match, at least, +442 as well.

3

Hi david,
thanks for your reply.
441 was an example , offcourse 442 is also landline . issue on SQL is that how to get what number is dialed .
in code tables i’ve 4 prefixes as follows.

44 => UK All
441=> UK Proper
442=> Uk Proper
447 => Uk Cell

now what i want is if a number say 442123123 is dialed , in SQL or from asterisk i should get its match with 442 code i-e UK Proper only but in results i’ve both 44 and 442 matching the criteria. how can i stop in sql not to look 44 and just 442.
its total of 18500 Codes and say a person calls 100 different numbers in day , will Sql query run on whole table each time new row is generated ?
i am searching all asterisk forums too but amazed why on one needed it earlier ?