PIONEERS Least Cost Routing Module for FreePBX

[wardmundy]

Right. LCR is great for lots of traffic, and lots of smaller carriers that offer low flat rate hate LCR people as they will abuse them and only use them for routes that they would actually lose money. Therefore, many flat rate mandate that traffic must be normalized and not just using them for example to only call the high rated terminations. Your list is good and helpful to most of the people on here.

This is one area in which FreePBX isn't too helpful. It would be extremely handy to be able to inject some rules logic into Outbound Routes. The default logic is "IF Congested, SKIP to Next Provider." I doubt the FreePBX folks are going to do this since they're now in the business, but it might be an interesting exercise one of these days.


CORRECTION: Looks like [macro-dialout-trunk-predial-hook] might do the trick. You could build some pricing logic for each ${DIAL_TRUNK} using an AGI script or even some dialplan code with a MySQL table lookup and, if a route were too expensive, you could set ${PREDIAL_HOOK_RET} value to "BYPASS" which tells FreePBX to skip that trunk. Nice!

 

[tm1000]

An LCR module has been on the list for a long long time. And nothing is stopping someone from writing a module to do this in FreePBX, however clarification being in the "business" isn't stopping something like this from happening. We have many other things to do to make you all happy before we can move forward on this...



PS Not to throw this on my buddy lgaetz but he really likes taking on these projects and getting them started and then I usually come along and add little tid-bits and fixes

This is one area in which FreePBX isn't too helpful. It would be extremely handy to be able to inject some rules logic into Outbound Routes. The default logic is "IF Congested, SKIP to Next Provider." I doubt the FreePBX folks are going to do this since they're now in the business, but it might be an interesting exercise one of these days.


CORRECTION: Looks like [macro-dialout-trunk-predial-hook] might do the trick. You could build some pricing logic for each ${DIAL_TRUNK} using an AGI script or even some dialplan code with a MySQL table lookup and, if a route were too expensive, you could set ${PREDIAL_HOOK_RET} value to "BYPASS" which tells FreePBX to skip that trunk. Nice!

 

[PBX@Home]

I will second that thought that make it user defined / updated. I don't think the db / api should look at all possible providers as let's say I use VoIP.ms and Vitelity but someone else uses four others. I wouldn't want my system spending resources looking into providers I don't use. Also, I haven't been doing PBX for long, I am not sure how much pricing changes, but querying an api from a provider may also use valuable bandwidth (if some are limited).

 

[w1ve]

I will second that thought that make it user defined / updated. I don't think the db / api should look at all possible providers as let's say I use VoIP.ms and Vitelity but someone else uses four others. I wouldn't want my system spending resources looking into providers I don't use. Also, I haven't been doing PBX for long, I am not sure how much pricing changes, but querying an api from a provider may also use valuable bandwidth (if some are limited).

Querying a RESTfull xml api, which many of the providers have, is very cheap. It would only be 10s of K for a very detailed list. If you are querying a particular rate center, it would be very tiny.
So it could be done.

 

[wardmundy]

I'd rather not be dependent on providers for all lookups if we can help it. Then we have to worry about network issues. Plus they have some incentive to not be completely accurate. For example, US/Canada rates don't change that often. We'd still use their rate tables, just don't do it by network lookups. For foreign rates that change all the time, this might have to be optional based on the call destination or the provider?? There certainly needs to be a way to turn LCR on and off for different types of calls: US48, Canada, Europe, CruiseShips, etc.

Get a quick list of your trunks with either of these commands:

grep OUT_ /etc/asterisk/extensions_add* | awk '/ = / { print $0 }'
 
mysql -uroot -ppassw0rd asterisk -e "SELECT trunkid,name,tech,channelid FROM trunks ORDER by trunkid"

I'll try to build a couple MySQL tables for AnveoDirect based on cheapo and premium US48 rates just to give us a starting point. In the non-commercial sector, nobody comes close to AnveoDirect pricing so that's probably a good place to start.

Once we have these built, it shouldn't be too hard to create others. My thought was to take the ${DIAL_TRUNK} number, look it up to see if we have a matching table for that provider and, if so, do a query against the table based upon some ${MAX_COST}. If no table for lookups, then check whether we have a MAX_COST for this provider and compare that to the MAX_COST for the proposed call. Then return ${PREDIAL_HOOK_RET} ="BYPASS" if the cost of the call would be too high or "" if the cost is OK or we can't find a match (or do we bypass without a match?? another variable?).

We obviously will have to store a ${MAX_COST} for each trunk (and for each type of call) somewhere, perhaps just in the dialplan code or maybe we add a couple fields to the providers table to include cheapo vs. premium (i.e. name of table to use) plus max cost for the provider. The other thing to nail down is whether we want both max. call cost and provider cost. Then, if we have a provider without a table, we just compare the max. call cost against that provider's flat-rate cost to see if they qualify to handle the call. This gets more complicated when we introduce non-US/non-Canadian calls obviously. Just some rough ideas on how this might work.

The tricky part might be how to sort out different types of calls. For example, US48 and Canada should be straight-forward. But maybe we need another database to handle foreign calls or Alaska, Hawaii, Yukon, etc. Those rates are always going to be higher and we don't want a max. call setting for a US48 call to impact a call to Alaska. What would be nice is to have multiple trunks for the same provider for different types of calls (e.g. Anveo-US48, Anveo-Alaska, Anveo-Europe), but we can only have one trunk registration. So I'm not sure how we'd handle that in the current FreePBX trunk design. Perhaps we build a separate LCR trunk table and tie entries back to the provider's table. Then we could have multiple records for different types of calls using the same provider, i.e. one trunk in the provider's table and multiple call types for the provider in the LCR table.

If this is all going to be FreePBX-driven, then we need a mechanism for adding new provider rate tables and managing LCR settings and call types for existing and new providers... perhaps along the lines of CallerID Superfecta. Whew!

 

[wardmundy]

Here are some random thoughts on design to get everyone focused. Have a beer first.

To do least cost routing manipulation, the key component we're going to need is the destination of the call. We typically don't identify this in FreePBX, but I think we can make it work.

Find trunkid for the call and then identify the match pattern of the numbers dialed. If set up correctly with LCR in mind, this will tell us the provider and the call destination e.g. US, Europe, etc.

Then we would lookup trunkid in trunks
Lookup valid match_pattern(s) by seq in trunk_dialpatterns
Lookup trunkid,match_pattern in trunk-lcr (new table) to get to proper max_provider_rate and provider_rate_table

trunk_lcr table structure might look like this:

trunkid (ties back to trunks)
match_pattern (ties back to trunk_dialpatterns)
seq (so we can have more than one provider record for different types of calls based on different dial patterns)
max_provider_rate (what's the most expensive call permitted with this provider record entry)
provider_rate_table (name of (new) asterisk.provider rate table when there's a match on this record)
provider_rate_url (if we're looking up the provider rate on line instead of in a MySQL table)


* if provider_rate_table and provider_rate_url both are empty, max_provider_rate will be considered fixed (flat) call rate for this provider for this type of dial pattern
* always match 1NXXNXXXXXX and NXXNXXXXXX and pure XX. type entries LAST! so sort the match_patterns so that those with letters and 1+letters are always at the bottom. In this way, we can find more specific rules for ALASKA, HAWAII, etc first. This can be handled with seq field in trunk_lcr.
* don't know an easy way around 23 Canadian area codes?? http://www.allareacodes.com/canadian_area_codes.htm. Maybe we can handle it with a dial prefix in which case we also need the prefix from trunk_dialpatterns added to trunk_lcr. Canadians could use dial prefix for US calls.

* note here that people will need to be more specific with match patterns if dealing with AK, HI, CAN
* if you want this to work properly. then we can fall back to NXXNXXXXXX type entries last in the search.

* logic for call should be IF no trunkid match in trunk_lcr, then OK (we're not using LCR for this provider)
* IF max_provider_rate > call_max_rate then BYPASS (this provider is too expensive for this call)
* IF no rate table/lookup tables and max_provider_rate <= call_max_rate then OK (this provider qualifies for call but there is no rate database available for number being called)
** NOTE: for Anveo, you probably need to do a 7-digit query (1902132) and, if it fails, do a 4-digit query (1902) before returning OK because of no match on US phone numbers! Length of prefix varies depending upon country. In Afghanistan, for example, you'd need to test 6 digits, then 4, then 2!
* IF no match found in rate table/lookup table and max_provider_rate is <= call_max_rate then OK (this provider qualifies for call but there is no rate info in existing database for the number being called)
* IF match found in rate table/lookup table and rate is <= max_provider_rate then OK else BYPASS (found a match on the number being called in the rate DB so figure out if the price is right)

 

[wardmundy]

Here's how to build or update your Anveo rates table in MySQL from the command line:

# Find desired rates table: http://anveodirect.com/did/prices (using value file below as example)
cd /root
rm anveo*.csv
wget http://www.anveo.com/anveodirect.value.csv
mv anveodir* anveo.csv
mysql -uroot -ppassw0rd asterisk -e "create table anveo (destination VARCHAR( 50 ) DEFAULT NULL ,prefix VARCHAR( 20 ) DEFAULT NULL ,rate_inter DECIMAL(6,5) DEFAULT NULL ,rate_intra DECIMAL(6,5) DEFAULT NULL ,KEY prefix ( prefix ) ) ENGINE = MYISAM DEFAULT CHARSET = latin1"
mysql -uroot -ppassw0rd asterisk -e "truncate table anveo"
mysqlimport --ignore-lines=1 --fields-terminated-by=, --local -uroot -ppassw0rd asterisk anveo.csv

You could use something like the following script to keep your rates current each night:

cd /root
rm anveo*.csv
wget http://www.anveo.com/anveodirect.value.csv
mv anveodir* anveo.csv
mysql -uroot -ppassw0rd asterisk -e "truncate table anveo"
mysqlimport --ignore-lines=1 --fields-terminated-by=, --local -uroot -ppassw0rd asterisk anveo.csv

 

[wardmundy]

Queries for countries look like this:

mysql -uroot -ppassw0rd asterisk -e "SELECT * FROM anveo WHERE destination LIKE 'Canada%' ORDER BY prefix"
 
mysql -uroot -ppassw0rd asterisk -e "SELECT * FROM anveo WHERE destination LIKE 'USA%' ORDER BY prefix"

Double query required to rule out a wildcard match on area code with new or unknown exchanges. Here we assume we don't want to pay over .3¢ a minute (US dollars!) for Alberta call.

mysql -uroot -ppassw0rd asterisk -e "SELECT * FROM anveo WHERE prefix LIKE '1403132' and rate_inter<=.003 "
mysql -uroot -ppassw0rd asterisk -e "SELECT * FROM anveo WHERE prefix LIKE '1403' and rate_inter<=.003 "

Or we want to call the Hyatt Regency in New Delhi (+91 11 2679 1234) for no more than a penny a minute:

mysql -uroot -ppassw0rd asterisk -e "SELECT * FROM anveo WHERE prefix LIKE '9111%' and rate_inter<=.010"

IMPORTANT: Don't use rate_intra field for computing International calls. Field is usually 0.00000. That field is for intrastate calls in the USA.

 

[jroper]

Hi

Most rating engines work in the following way, given a rate table that consists of a dial-code, price, and optionally a destination name.

1. Take the dialled digits, and compare against a list of dial-codes and see if there is an exact match.
2. If there is no match, then strip the last digit, and check again.
3. Keep stripping the last digit until you get a precise match, you then have the rate for the destination.

That works for identifying the rate, but there is no LCR in the above process.

To determine the best rate for the destination across a number of trunks, you need to do as above, but this time you select the longest precise match from each single rate table, and then compare the prices to get the cheapest route.

Clearly you need one rate-table per trunk, although physically, they can all exist in the same database table.

Where the rate is identical between carriers, you need some logic to select the best rate, this can be done by giving the trunk a weighting or a preference order.

I'd suggest that whatever information is got from the carrier, whether it by an API or from a text file download, that the data be massaged outside of FreePBX into a simple 3 column CSV consisting of Trunk ID, Dial-code, Cost, Therefore the sanitised data is what is uploaded at intervals as carrier rates change.

The other issue to consider is the format of the dial-code, e.g. when dialling within the USA, I understand that you could potentially dial 1NXXNXXXXXX, NXXNXXXXXX, or even NXXXXXX, there are also other anomalies when dialling internationally, in that you could dial +ZX. 011ZX. and 00ZX. so you have to settle on a format for the dial codes, which I believe should be international format, e.g. 1202 for Washington DC, rather than just 202., which could be misidentified as Egypt (Dial-code +20)

This suggests that you would need to sanitise the dialled digits, e.g. removing the 9, where 9 is dialled for an outside line, and adding the country code where the call is dialled nationally, e.g. 202-NXXXXXX becomes 1202NXXXXXX or 0117 (Bristol UK) becomes 44117

There is an interesting open source project from Google used in Android at https://code.google.com/p/libphonenumber/ which may be leveraged to help provide both the sanitised dialled digits, and to provide an accurate destination name as well, without relying on the carrier's (often inaccurate) destination names. There are ports to other languages, including PHP.

Joe

 

[wardmundy]

Thanks, jroper. I worry about doing searches below 4 digits. Are we going to run into situations where a 2-digit or 3-digit prefix might point to a different country than a 4-digit number??

 

[wardmundy]

lgaetz: Agreed. I think for our purposes what we're talking about is a set of outbound routes that have already been ordered in the preferred order by price, quality, or whatever. Then we will test each trunk in order to see if it exceeds the maximum per minute cost threshold. If it does, we throw it out and move down the list to the next trunk and repeat.

 

[jroper]

Hi

Provided that all dial-prefixes are in international format and the dialled digits are also converted to international format, then you'd always get the country correct, also with the proviso that the rate table is sufficiently granular, e.g. +1 can be USA or Canada or a number of other countries.

I suspect that the biggest problem to solve is actually identifying the rate is going to be for a particular set of dialled digits, and that problem is the same whatever the logic for choosing / discarding a trunk.

The method I described in the three steps in my earlier post should get you to the point of determining the rate for the dialled digits irrespective of the number of digits in the dial-codes, although you do have to account for people dialling the same number in different ways.

Joe

 

[krzykat]

I have (and always have) used A2Billing for LCR. It's about the only way to truly do LCR. I also use it as a mechanism to put some fraud limits in place. All my servers use my A2B server as their trunk. In the A2B not only can you set the LCR settings, but you can also set the values and set a balance for each server that connects to it. Then if somehow your PBX that connects to the A2B box gets hacked (I know won't happen- we all use Whitelist, right?) you are only subject to the balance you have set on your A2B box.

 

[atsak]

Also, don't forget that Canada and the US aren't the only countries using +1. For example, the Bahamas, which is extortionate to call . . .

 

[jroper]

Hi

A2Billing is probably overkill for this particular application.

Joe