FreePBX | Register | Issues | Wiki | Portal | Support

Asterisk Phonebook vs CID Lookup source


(Milauria) #1

While Freepbx and Asterisk has incredible feature sometimes they fall shorts on basic things IMHO.

I am trying to perform a CID lookup with the internal database of the Asterisk Phonebook

Aside of the limitations of the Phonebook module with apparent random behaviours when uploading a CSV with 00’s of names (I limit to 900 per upload) … fact is that my inbound numbers all show with a +xx international code in front and I cannot match this to the phonebook.

I tried to modify the Phonebook entries by adding a +xx in front … but Phonebook does not take the “+” sign as valid. Or I have tried to use the “Set Caller ID” module to manipulate the incoming CID by removing the leading 3 digits, but I noticed that the CID lookup kicks in before the CID gets trimmed, and therefore does not find any match.

Bottom line I am stuck without CID lookup when a “+” sign is added by my VOIP carrier in front of all numbers!

Any idea suggestion or best practices to make it work as it should ?


#2

You might want to “normalize” your CID’s at the incoming point, perhaps using the [from-pstn-e164-us] context which is designed explicitly to handle your problem, you can read it in /etc/asterisk/extensions.conf (many phones in NANP land do not have a + button)


(Milauria) #3

Thanks for the advice, it’s starting to make sense … 2 clarifications:

  1. The CDR reports now shows “+61467xxxxxx” <01161467xxxxxx> … which of the 2 numbers will be used for CID lookup ?

  2. One more step … Can I create a custom context that it is not overwritten by FreePBX scripts just to remove the “+” sign without adding “011” for non-NPA country code ?

Below the current context and I am not touching it as Freepbx seems it will recompile by itself … ?

; CallerID(number) presented in e164 non-NPA (country code other than 1) will be
; reformated from: + to 011
;
[from-pstn-e164-us]
exten => +1NXXNXXXXXX/+1NXXNXXXXXX,1,Set(CALLERID(number)=${CALLERID(number):2})
exten => _+1NXXNXXXXXX/_NXXNXXXXXX,2,Goto(from-pstn,${EXTEN:2},1)
exten => +1NXXNXXXXXX/+NX.,1,Set(CALLERID(number)=011${CALLERID(number):1})
exten => _+1NXXNXXXXXX/_011NX.,n,Goto(from-pstn,${EXTEN:2},1)
exten => _+1NXXNXXXXXX,1,Goto(from-pstn,${EXTEN:2},1)
exten => [0-9+]./+1NXXNXXXXXX,1,Set(CALLERID(number)=${CALLERID(number):2})
exten => _[0-9+]./_1NXXNXXXXXX,1,Set(CALLERID(number)=${CALLERID(number):1})
exten => _[0-9+]./_NXXNXXXXXX,n,Goto(from-pstn,${EXTEN},1)
exten => [0-9+]./+NX.,1,Set(CALLERID(number)=011${CALLERID(number):1})
exten => _[0-9+]./_011NX.,n,Goto(from-pstn,${EXTEN},1)
exten => [0-9+].,1,Goto(from-pstn,${EXTEN},1)
exten => s/
+1NXXNXXXXXX,1,Set(CALLERID(number)=${CALLERID(number):2})
exten => s/NXXNXXXXXX,n,Goto(from-pstn,${EXTEN},1)
exten => s/
+NX.,1,Set(CALLERID(number)=011${CALLERID(number):1})
exten => s/_011NX.,n,Goto(from-pstn,${EXTEN},1)
exten => s,1,Goto(from-pstn,${EXTEN},1)

Many thanks- Cheers


#4

Question 1 , you would need to similarly modify CallerID(name) , the string between the Quotes is the CallerID(name) , that between the “angle brackets” the CallerID(num)

Question2 I am unaware of which “FreePBX scripts” that would do that (perhaps you refer to Superfecta? ), otherwise You can do anything you want,

For a resolution to both of your questions, I suggest you build yourself a [from-pstn-e164-myneeds] context in /etc/asterisk/extensions_custom.conf based on the original context’s ‘what works’ and adding ‘what you want more’


(Milauria) #5

Ok I am making progress with your tips … one more hint: what does the “/” character mean in the dialplan syntax ?

I have been googling but could not find an answer e.g. what does it mean “[0-9+]./+NX.” for example?

I was hoping to be able to add/edit a custom dialplan within Freepbx GUI but the module “Custom Context” seems to do something else! Seems more straightforward to create a custom context from command line

Cheers


#6

In effect the bit before the / is a literal or pattern to match for the DID called (${EXTEN} within the context) , that after the / is the same for the CID of the caller as presented by your VSP

Perhaps http://www.voip-info.org/wiki/view/Asterisk+config+extensions.conf will help . . .


(Milauria) #7

Ok thanks for the suggestions and I have copied below a simplified dialpan for italian country code to strip the international prefix and/or replace the “+” with a “00”… I don’t entirely understand what it does as I just applied some reverse engineering form the “us” dialplan. It seems to work OK … but If somebody can put a second set of eyes to check it would be great! :innocent:

Two things I am not sure I get it right …

  • what is the purpose to have an “s” value in the syntax ? Is it a “catch all” DID ?
  • if this dialplan can process incoming hidden numbers appearing as “unknown” and pass them through without manipulation ?

Appreciate the help so far …

;-------------------------------------------------------------------------------
; from-pstn-e164-it:
;
; CallerID(number) starting with IT country code will be truncated of the leading “+39”
; All other country codes will be reformatted from: + to 00
;-------------------------------------------------------------------------------
[from-pstn-e164-it]
exten => [0-9+]./+39X.,1,Set(CALLERID(number)=${CALLERID(number):3})
exten => _[0-9+]./_X.,n,Goto(from-pstn,${EXTEN},1)
exten => [0-9+]./+Z.,1,Set(CALLERID(number)=00${CALLERID(number):1})
exten => _[0-9+]./_00Z.,n,Goto(from-pstn,${EXTEN},1)
exten => [0-9+].,1,Goto(from-pstn,${EXTEN},1)
exten => s/
+39X.,1,Set(CALLERID(number)=${CALLERID(number):3})
exten => s/X.,n,Goto(from-pstn,${EXTEN},1)
exten => s/
+Z.,1,Set(CALLERID(number)=00${CALLERID(number):1})
exten => s/_00Z.,n,Goto(from-pstn,${EXTEN},1)
exten => s,1,Goto(from-pstn,${EXTEN},1)
;-------------------------------------------------------------------------------


#8

Well. progress but you are not there yet, when an ${EXTEN} lands in a context then the first match is executed, wild cards always need a _ at the beginning, if there is NO match then the s (start) extension is executed. [0-9+] matches exactly that so start with things like +39./+39.,1,set caller id and goto the appropriate normalized from-pst context. Then _+. to do any other countries. but watch out as Italy is harder than NANP because of things like the Vatican and San Marino.

so go for it.

Outbound dialing is perhaps more convoluted because of the ‘open dialing’ in Italy if you don’t want to wait five seconds for any call to complete.


(Milauria) #9

I have been following your suggestion but not sure what I am doing … when testing the below plan from non-IT country (e.g. +49) it does not do anything which means that it process the [0-9+]. “catchall” line without any manipulation . Also not sure where the "" should go … why _+. and not _+39. as both contain the . wildcard ? … before and/or after the “/” ?

I feel I got stuck and any help you can provide to fix this plan is appreciated … and I have no need for Vatican as I am not expecting calls from the pope!

[from-pstn-e164-it]
exten => +39./_+39X.,1,Set(CALLERID(number)=${CALLERID(number):3})
exten => +39./X.,n,Goto(from-pstn,${EXTEN},1)
exten => +./
+Z.,1,Set(CALLERID(number)=00${CALLERID(number):1})
exten => +./_00.,n,Goto(from-pstn,${EXTEN},1)
exten => [0-9+].,1,Goto(from-pstn,${EXTEN},1)
exten => s/
+39.,1,Set(CALLERID(number)=${CALLERID(number):3})
exten => s/X.,n,Goto(from-pstn,${EXTEN},1)
exten => s/
+Z.,1,Set(CALLERID(number)=00${CALLERID(number):1})
exten => s/_00Z.,n,Goto(from-pstn,${EXTEN},1)
exten => s,1,Goto(from-pstn,${EXTEN},1)

==== Edit ====
Asterisk giving me this error: "Call from ‘myDID’ to extension ‘myDID’ rejected because extension not found in context ‘from-pstn-e164-it’"
I went back to the syntax “[0-9+]./_+39,1, Set” … but I got from Asterisk : “Catch-All DID Match - Found” … and no number manipulation happens
And I got lost … :frowning:


#10

again without the preliminary _ then [0-9+]. will match nothing but _+. will after you already processed _+39., please RTFM it WILL help.


(Lorne Gaetz) #11

User in another thread solved this problem with much simpler code in case you missed it:

Also you can use the dial pattern rules in Caller ID Superfecta to adjust numbers prior to lookup.


#12

I can’t disagree but rigorously it won’t work for San Marino if you are in Italy, any country with ‘open dialing’ needs very explicit inbound and outbound so the “dial pattern rules” for both can get extraorinarily complicated and the “convertional dial patterns” that your Joe Blow user is acustomed to must absolutely be honored or you fail your client, thank god I and 95% of my clients are NANP


(Milauria) #13

Ok this is what I got for my simple need and it seems working so far, with time I will dig into more elaborated dialplans but for what I need is good enough

Thanks to all !!

[from-pstn-it]
exten => _.,1,ExecIf($[ "${CALLERID(name):0:3}" = "+39" ]?Set(CALLERID(name)=0${CALLERID(name):3}))
exten => _.,n,ExecIf($[ "${CALLERID(number):0:3}" = "+39" ]?Set(CALLERID(number)=0${CALLERID(number):3}))
exten => _.,n,ExecIf($[ "${CALLERID(name):0:1}" = "+" ]?Set(CALLERID(name)=00${CALLERID(name):1}))
exten => _.,n,ExecIf($[ "${CALLERID(number):0:1}" = "+" ]?Set(CALLERID(num)=00${CALLERID(number):1}))
exten => _.,n,Goto(from-pstn,${EXTEN},1)

(Milauria) #14

For the records I think I completed my project to perform a reliable CID lookup using Freepbx. For this I created a new table in the mysql already present in the Freepbx server and used the CID Lookup module in freepbx.

I have found many good suggestions on this forum and on the internet and I want to summarise here the steps so that it might be useful for the community.

1 - Using SSH you need to create a new database (eg “addressbook”) with a new table (“callerid” in my case) where you will load your address book names and numbers. Note that the table contains also a key field which auto increments (otherwise I found problems to update it using ODBC connector, see later).
Example of table structure below and you find tons of tutorials on how to do this from the mysql CLI :

mysql> describe callerid;
±-------±--------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-------±--------±-----±----±--------±---------------+
| id_num | int(11) | NO | PRI | NULL | auto_increment |
| name | text | YES | | NULL | |
| number | text | YES | | NULL | |
±-------±--------±-----±----±--------±---------------+
3 rows in set (0.00 sec)

2 - Still in the SSH you need to create a new mysql user to access the table (I did not want to use root which comes with no password in mysql and it is limited to localhost usage by default). Note that the new user needs to have permission to access from a different LAN IP other than “localhost” and you can use the x.x.x.% as jolly symbol to define a range of IP’s (I need to update the table from another windows machine using VBA and ODBC connector). The mysql user is added only to mysql, no need to create a new user in linux. To connect with ODBC from the LAN don’t forget to comment the “;bind-address=127.0.0.1” into the mysql conf file at /etc/my.cnf so that the machines can listen from all IP’s and not only from localhost (ie should be equal to “bind-address=0.0.0.0”)

3 - If you need to cleanup the CallerID transmitted from the trunk like in my case (I wanted to replace the + sign with 00 or remove the international prefix for same country calls for example) edit /etc/asterisk/extensions_custom.conf to include the following code. Note the context must be called [from-pstn-custom] to use the Freepbx predefined context structure (no need to specify a context into the trunk settings), for the rest feel free to customize the number manipulation the way you like.

[from-pstn-custom]
exten => _X.,1,ExecIf($[ “${CALLERID(num):0:3}” = “+39” ]?Set(CALLERID(num)=${CALLERID(num):3}))
exten => _X.,n,ExecIf($[ “${CALLERID(num):0:1}” = “+” ]?Set(CALLERID(num)=00${CALLERID(num):1}))
exten => _X.,n,Set(CALLERID(name)=${CALLERID(num)})

If your country has a better number plan than mine probably you can optimize the “_X.” pattern in the front, not sure of this though.

4 - In my case I also downloaded the ODBC Mysql driver on my Windows machine to link the mysql address book into MS Access and maintain the records from there. No special settings were required other than matching the 32/64 bit version. My case was a 32bit Office on a 64bit Windows and I had to use the 32bit Mysql ODBC driver.

5 - Back to Freepbx go to Admin – Caller ID Lookup sources and create an entry for your mysql address book created above. No strange settings here if you have created the table and the mysql user right as above. Local host is "localhost’ and user is the mysql user you created above, connection string as per Freepbx suggestion.

6 - Go to inbound routes and modify the route you are using. Put there you DID number, if you leave this empty the route will perform a catch all and I think it will not run the CID lookup script. In my case I have 1 incoming trunk only so I have 1 incoming route only and 1 DID. Then in the CID lookup source specify you are using the CID lookup source you have created to access the mysql table.

As an alternative you can achieve a similar result embedding the code straight into a new customized context that will bypass the freepx modules. In this case the name of the context must be specified into the trunk settings.

[my-custom-context]

exten => _X.,1,ExecIf($[ “${CALLERID(num):0:3}” = “+39” ]?Set(CALLERID(num)=${CALLERID(num):3}))
exten => _X.,n,ExecIf($[ “${CALLERID(num):0:1}” = “+” ]?Set(CALLERID(num)=00${CALLERID(num):1}))
exten => _X.,n,Set(CALLERID(name)=${CALLERID(num)})
; here starts the CID lookup
exten => _X.,n,MYSQL(Connect connid localhost mydbname mysqluser mysqlpasswd)
exten => _X.,n,MYSQL(Query resultid ${connid} SELECT name FROM callerid WHERE number LIKE ${CALLERID(num)})
exten => _X.,n,MYSQL(Fetch fetchid ${resultid} name_out)
exten => _X.,n,ExecIf($[ “${fetchid}” = “1” ]?Set(CALLERID(name)=${name_out}))
exten => _X.,n,MYSQL(Clear ${resultid})
exten => _X.,n,MYSQL(Disconnect ${connid})
; here continues to the std call processing
exten => _X.,n,Goto(from-pstn,${EXTEN},1)

That’s all. Note that I did not invent anything, I just tried to make the best out the the Freepbx modules flexibility and credit to all the suggestions that I received and found on the community forums !


(Lorne Gaetz) #15

I don’t recommend you populate the context [from-pstn-custom[. You should instead create a new context such as noted in your post above such as [from-pstn-it] and the final line of this new context should goto [from-pstn]. Every line you put in [from-pstn-custom] overwrites another auto generated line, and you may have unintended consequences as a result.


(Milauria) #16

Reason I named it “[from-pstn-custom]” is because I read in the “extensions.conf” a comment on how to customise the dialpaln according to the Freepbx “rules” and leaving as much as possible the “standard” settings. From what I see the “extensions_custom.conf” file is left out purposely from the auto-generation mechanism. I am runnimg on the latest freepbx version with asterisk 13.

;-------------------------------------------------------------------------------
; from-pstn:
;
; Entry context for calls from the outside world to hit FreePBX
[from-pstn]
include => from-pstn-custom ; create this context in extensions_custom.conf to include customizations
include => ext-did
include => ext-did-post-custom
include => from-did-direct
include => ext-did-catchall ; THIS MUST COME AFTER ext-did
;-------------------------------------------------------------------------------


(Milauria) #17

Le me be more precise on my settings on inbound route (item 6 above):

It needs the DID field filled in to work, if you use leave it blank (accept “any DID”) it won’t work ad it goes through a different code sequence (kind of “catch all” settings)
In my case I set a DID in the registration string for the incoming trunk (e.g. user:password@sip.server.com/DID) and used the same DID in the inbound route DID setting.
Just used trial and error approach and this is the conclusion I came to.

Cheers


Remove inbound country code +39
(Milauria) #18

For the future reference I have used these commands to create the callerid mysql table referenced above

MariaDB [(none)]> create database addressbook;
MariaDB [(none)]> use addressbook;
MariaDB [addressbook]> create table callerid (idnum int(11) auto_increment, name text, number text, primary key (idnum));

cheers