Even though Asterisk has a blacklist feature what we are trying to accomplish here is to find a good method of querying based on the name part the caller ID rather than just the caller’s phone number. The purpose of the blacklist is to block a number you enter into the blacklist. If the number is there the system will either hang up on them or send them to purgatory. This works fine if the number is always the same. If the name is the same but the number changes (because the caller is spoofing the number) you’ll get an endless number of calls from the same bad guys. If you enter each number into the blacklist your list will keep growing. Imagine a local business who’s number has been spoofed by the bad guys. You could use the blacklist and block that number. In the end if the real owner of that number calls they get the blacklisted behavior. Blocking based on name is more useful.
If you look at your phone’s caller ID feature you’ll note that some callers come in with odd caller IDs. You’ll see them come in with a caller ID of something like “V2093920039423432”. Others come in with a caller ID of “policeofficerspac” or “cancerrelief”, or “firedepartment”. That is done as a trick to get you to take the call.
Using the blacklist feature of Asterisk works fine if the number is repeated between calls. It doesn’t take care of the other caller IDs that I just mentioned. The caller ID has two components: 1) the name and 2) and the number. The Asterisk blacklist module has three fields. The name, number, and description. When the Asterisk blacklist module’s data is added typically you view it as a phone number look up. This is because the number is the primary field that the blacklist relies upon in order to work. When you enter data into the blacklist the button you click to add something is called “Blacklist Number”. When the blacklist module works it looks up the number. For the most part the name and description fields are disregarded.
One thing I discovered while working on this is that even though it asks you to blacklist a number you can instead add a name. In place of the number you just enter a text name. When the blacklist processes the call it will look at the caller ID and compare it. If you have an exact match on the spelling of the caller ID it will block the call.
There is a problem with using the blacklist module in conjunction with a Dynamic Route, re:the caller’s info is entered into the blacklist module that comes with Asterisk:
- If the blacklist is active with entries and one of those entries match the caller and there’s a match from the Dynamic Route the Dynamic Route can’t be used to determine the action to take, because the blacklist module will process the caller. In other words, you cannot further process the call with the Dynamic Route, because the blacklist module will just hang up.
- The name can’t have a space in it. If you try to enter text in the number field if there’s a space in the text, Asterisk’s blacklist module will substitute a \ in place of the space when saving your entry. This means that the number will never match and the call will go though. If the bad guys start adding spaces to their name then the lookup will also fail. So, when they add a space to their name and you try to account for that using the blacklist module, the call will go through.
- Another is that the blacklist module doesn’t normalize case (set everything to lower or upper case). You must have multiple entries covering the various spellings and capitalization (for example “Policeofficerspac” or “policeofficersPAC”). This means your blacklist will grow over time.
Beginning with the release of Freepbx 15 the company introduced this feature called Dynamic Routes. In reading the description of what this does it is used as the first point in the call flow — the inbound route. The call comes in, the Dynamic Route triggers, from there you get a match, or not. The match does one thing and the non-match can do another. In the case of the blacklist module you’d need a match, and if it does match you’d want it to hang up. In the case of the non-match you’d want to continue the call flow. If there is no match on the blacklist you may want it to go either to another Dynamic Route or to branch off to say the Time Conditions which checks for, for example, a holiday. If it isn’t a holiday you might want it to branch off to another time condition and check hours of operation. In my case, I use this to ultimately present an IVR that offers up things such as to play back a message covering the hours of operation or a message wishing happy holidays.
As mentioned above, the problem with using the blacklist module where the bad caller is listed is that if the caller is on the blacklist, the blacklist module will follow it’s own process which means that you can’t control it further via the Dynamic Routes. If you turn off the blacklist module (by uninstalling it) then it won’t hang up and your bad caller will just go through.
Instead of processing the calls via the blacklist module (which you can leave active (and empty) — in case you still want to have the blacklist module hang up on callers that are “blocked” or “restricted”, etc) is using a Dynamic Route with the source type of MySQL with a back-end database to act as your blacklist. This will allow you to use MySQL select statements to query the MySQL database for a match, and if one is found it can process the caller and hang up or send them to something that will eat their time up (as an example, the Lenny module was one such idea being implemented to waste a bad caller’s time). A thought that crossed my mind would be, instead of hanging up, forward to ring directory assistance (18005551212).
To accomplish this you’ll need to set up the MySQL database, and the Dynamic Route(s).
What you’ll need is a MySQL database that you can access from the Asterisk program. This database should have the appropriate fields such as name, number, description, etc. You’ll need to have MySQL (or some other compatible database such as MariaDB), then create your account, create the database, create the user, grant the user rights to access the MySQL server from whatever location, grant the user rights to the database, then find a way to add and update the data in the table, etc. Once this database is ready to go and you can access it using that account from say another computer then you will want to create the Dynamic Route in FreePBX. Remember, the Dynamic Route module requires FreePBX 15 or later.
Within the Dynamic Route have the first one check for a caller ID that is similar to “V2039450203929348” and if is just send the tone indicating that the number is no longer valid and hang up. Otherwise branch to a second Dynamic Route with the Source Type of MySQL and query the MySQL database to see if the caller name or number is listed. If it is, then you would naturally want to send the tone indicating that the number is no longer valid and then hang up on the caller.
In creating the Dynamic Routes you will need to decide the lookup source. Here, for the “V2093440384….” type numbers you’ll use a Dynamic Route that uses an asterisk variable. So the lookup source is an asterisk variable and the asterisk variable is something like “${REGEX(“^V[0-9]*” ${CALLERID(name)})}”. This asterisk variable simply looks at the caller ID and determines if it matches the regex pattern and if so it will send the tone that the number is no longer valid and hang up.
If the caller doesn’t match the regex pattern then invokes another Dynamic Route that will use a Lookup Source Type of “MySQL”. Here is where you’ll enter your credentials to access the MySQL database. It wants the database name, the user name, the host (such as 10.10.22.13, if that is the IP address of the MySQL server — you can also use a fully qualified domain name (FQDN)), and the database user’s password. It also needs the query. I’ve blocked out the IP address that I use, the username, and the password. Below the following image is the text of the query.
The query would look something like this:
SELECT COUNT(*) FROM (SELECT phone,name FROM blacklist WHERE (name LIKE ‘${CALLERID(name)}’ OR phone LIKE ‘${CALLERID(num)}’) LIMIT 1) AS myvar
This queries the MySQL database called contacts and the table called blacklist. This query returns a 1 or 0 depending on whether it matches the caller id name or the caller id number. If this matches it sets a “1” and if it doesn’t match it sets a “0”.
Above is a sample of the database. It can be more or less complex. This example is for explaining how to query based on name so the phone number is less important. If you look at the query you’ll see it looks at both name and number.
This table is called blacklist, however you could just create a contacts database with a callers table with an additional boolean field that indicates whether this is a blacklisted number. If you go that route your query will have to be adjusted appropriately.
SELECT COUNT(*) FROM (SELECT phone,name,block FROM contacts WHERE ((name LIKE ‘${CALLERID(name)}’ OR phone LIKE ‘${CALLERID(num)}’) AND block=”1″) LIMIT 1) AS myvar
The above SQL select statement uses the field named “block” to indicate that this caller is blocked. Since this has been an exercise in blocking by the name more than the number the number is included regardless as we might block by number as well. Note: This is a check for ((name or number) and block). This uses order or precedence to ensure that name or number are evaluated first and then that block also is equal to 1.
Settings for ODBC
Since for some unknown reason Asterisk is discontinuing support for mysql rather than enhancing a solid product, you can use ODBC (which is a Microsoft technology) to access your mysql database.
In the /etc/odbc.ini file are sections that you can create that indicate the type of driver, the location of the server, the database name, etc. Here’s an example:
[pbsql]
Description=MySQL connection to sql database
Trace=Off
TraceFile=stderr
Driver=MySQL
SERVER=asterisk.example.com
PORT=3306
DATABASE=contacts
SSLVERIFY=1
CONN_TIMEOUT=1
readtimeout=1
With this set in freepbx within the dynamic route you would set the source type to ODBC.
The main reason for using ODBC in this way is because you can use SSL to secure the transport of the username, pass, and data.
The ODBC function is defined in the /etc/asterisk/res_odbc_custom.conf
An example:
[dasqlfunction]
enabled=>yes
dsn=>dasqlfunction
username=>freepbx
password=>longcomplexpassword
pooling => no
limit => 1
pre-connect=>yes
connect_timeout=>1
When doing it this way via odbc not only do you get to use SSL but there is a proper time out. In our implementation if your mysql database is offline Freepbx’s dynamic route will take a long time to timeout and that will cause asterisk to ultimately fail with 0 calls coming in. With the ODBC implementation with the timeout even if the sql database is offline the calls will continue on, except the blacklist dynamic route will not be processed. It is always better to just have the calls come through if the lookup fails.
Note: There’s a problem with taking down the router. If it goes down the ODBC source pointing to the server fails to connect and the phones don’t ring. In my case, even after bringing up the router I still have to reboot the asterisk container in order for calls to ring through.
Note: When doing regex matches, if using special characters such as parenthesis “(” or “)”, use a “.” in place of those to match that. It will make it easier for regex to find the match.
One way to solve this issue is to have the mysql database on the same computer as the asterisk server. I looked at setting this up as a shared mysql database for the purpose of allowing multiple phone systems use the same blacklist. This in my mind is still the preferred method. However, with the flaws in Asterisk where it isn’t properly handling the time out your hands are tied. The developer of Asterisk blamed the dynamic route developer and the dynamic route developer blamed Asterisk developers. Just one of those things.
When I set up my dynamic route I noted that I ultimately would need multiple dynamic routes. I wanted to process regex expressions and I wanted to use the mysql database. So, in order to accomplish this I numbered the dynamic routes using 1, 2, 3, 4, 5 at the beginning of the name. I could also then when setting up the route flow know based on which one I was editing which one was next.
