Low Cost Routing on Asterisk with Postgres and Prefix range module
Asterisk doesn’t have a good setup for low cost routing (LCR) so years ago I borrowed Freeswitch’s mod_lcr with MySQL setup to get multiple vendors in place. This helped to lower cost by picking the lower cost route from the different vendors and also failover if one vendor can’t terminate the call.
Eventually running MySQL started to get pretty heavy with a large call volume due to the query required. We were running about 6 different vendor rate decks (US and international) so row count started getting into the few hundred thousand and the IN performed in the query was noticeably getting slower.
Random Bits of Info
The LCR should be running with your vendor rate decks which break down the route. For example a phone number like +18585550000 you would want to match the longest prefix. Take for example these mockup prefixes in a rate deck:
VendorA:
+18585555, $0.005VendorB:
+18585555, $0.004VendorC:
+1858, $0.006
None of the vendors match your phone number completely so you want to get the closest match as possible which is Vendor B for lowest cost. Vendor A matches the same but cost a little more. Vendor C probably can terminate the call fine but a more granular match usually has better internal routing and cost.
Using the Freeswitch mod_lcr MySQL method this would require a query that looks at an expanded phone number which kind of looks like this:
SELECT * FROM lcr WHERE digits IN (
'18585550000',
'1858555000',
'185855500',
'18585550',
'1858555',
'185855',
'18585',
'1858',
'185',
'18',
'1'
);
This is a stripped down bad example just to show the expanded phone number. You can check out the full MySQL setup and query here.
Digging around found that mod_lcr mentions contrib prefix for Postgres. It’s available on github here: https://github.com/dimitri/prefix. After manually setting up and testing queries by hand it reduced the MySQL query which was going over 1000–2000ms down to <100ms for the same results.
Since I’m running Asterisk I needed to mesh all this together. For NANP we do not use intralata/interstate right now so I don’t have that implemented here.
Prerequisites
Ubuntu 16
Asterisk 12+
Vendor rate deck in CSV
Install Postgres and Prefix
For future steps. Assuming this installed Postgres 9.5.
sudo apt-get -y install postgresql postgresql-contrib postgresql-server-dev-all
cd /usr/src
git clone https://github.com/dimitri/prefix.git
cd prefix
sudo make
sudo make install
Configure Postgres
I’ll try to comment to break down each command
Setup asterisk postgres user and lcr database
# Change to postgres linux user
su postgres# Create asterisk postgres user. You'll create the password here.
createuser asterisk -P# Create LCR database
psql> create database lcr;# Switch to LCR database
psql> \c lcr# Setup LCR to use prefix extensios
psql> CREATE EXTENSION prefix;# Exit postgres
psql> \q# Exit postgres linux user
exit
Modify postgres configuration
Do not follow this part blindly. Make sure these changes to config match how you want postgres to run related to access on the network and permissions.
# Allow postgres to be reached from external network
vim /etc/postgresql/9.5/main/postgresql.conflisten_addresses='*'# Change permission to allow login by password
vim /etc/postgresql/9.5/main/pg_hba.conf# Change local all all from peer to md5
ipv4 set IP to all
For those changes to take affect restart postgres
/etc/init.d/postgresql restart
Create tables and indexes
Not much explanation. Just creates the needed tables. Login with asterisk user w/ password from user creation step.
psql -U asterisk -d lcr -W
Run the below after logging into postgres:
Importing Rate Decks
Every VOIP vendor usually has their own CSV for rate deck. You just be able to get this from their dashboard or the usually send starting the account or when the rate deck has been updated.
For this example, I’m using the Voxbeam rate deck which looks like this:
Prefix,ISO,”Named Route Name”,”New Rate”,”Connection Unit”,”Bill Unit”,”Existing Rate”,Status,Currency,”Effective Date”,”Effective Time”,”Expiry Date”,”Expiry Time”
1661,US,"United States - OnNet - CA - 661",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1669,US,"United States - OnNet - CA - 669",0.006600,30,6,0.006600,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1707,US,"United States - OnNet - CA - 707",0.033400,30,6,0.032400,Increase,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1714,US,"United States - OnNet - CA - 714",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1747,US,"United States - OnNet - CA - 747",0.006600,30,6,0.006600,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1760,US,"United States - OnNet - CA - 760",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1805,US,"United States - OnNet - CA - 805",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1818,US,"United States - OnNet - CA - 818",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1831,US,"United States - OnNet - CA - 831",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1858,US,"United States - OnNet - CA - 858",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1909,US,"United States - OnNet - CA - 909",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1916,US,"United States - OnNet - CA - 916",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1925,US,"United States - OnNet - CA - 925",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1949,US,"United States - OnNet - CA - 949",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1951,US,"United States - OnNet - CA - 951",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1303,US,"United States - OnNet - CO - 303",0.011500,30,6,0.011500,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1719,US,"United States - OnNet - CO - 719",0.033400,30,6,0.032400,Increase,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
1,US,"North American Region",0.500000,30,6,0.500000,Unchanged,USD,11-Feb-2018,20:00:00,18-Feb-2018,20:00:00
Add First Vendor/Carrier
Since we are adding Voxbeam we need to add these carrier records into Postgres. You can do this easily by running these:
INSERT INTO carriers (id, carrier_name, enabled) VALUES (1, voxbeam, true);INSERT INTO carrier_gateway (id, carrier_id, prefix, suffix, enabled) VALUES (1, 1, 0011101, '@voxbeam', true);
Importing Script
This is more sudo code that I mocked up for this post in PHP. Make sure to install apt-get install php7.0-pgsql
. This should be cleaned up and add sql escape string but just wanted to add as a start.
It is expecting the rate deck to be located at /tmp/voxbeam-silver.csv
and that Voxbeam to be carrier_id=1
. Make sure to swap out the Postgres credentials before running and just do php importLcr.php
Above it is performing an upsert so it just updates any matching record or creates if its a new route. An import part is the clear()
, this removes any old records incase a route is no longer supported by the vendor so it is no longer used.
Testing Imported Data
Just into postgres CLI and try running the a query for +18585550000
:
SELECT l.digits, c.carrier_name, l.rate, l.iso3166 AS iso_3166,
l.route_name, cg.prefix, cg.suffix, l.bill_init, l.bill_incr
FROM lcr l
JOIN carriers c ON l.carrier_id=c.id
JOIN carrier_gateway cg ON c.id=cg.carrier_id
WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1'
AND digits_prefix @> '18585550000'
AND CURRENT_TIMESTAMP BETWEEN date_start AND date_end
ORDER BY digits DESC, rate asc;
If you’re using the sample date provided above, this should return 2 records:
"digits","carrier_name","rate","iso_3166","route_name","prefix","suffix","bill_init","bill_incr"
1858.0,"voxbeam",0.0328,"US","United States - OnNet - CA - 858","0011101","@voxbeam",30,6
1.0,"voxbeam",0.5,"US","North American Region","0011101","@voxbeam",30,6
The first record is the better match of 1858
and has a cost of then the second match is of just 1
. If you had the additional vendors, lets say Magictelecom matching the same 1858
but cheaper, it would list that first then Voxbeam second based on rate
.
Using the LCR
Everything is setup and loaded up. Last part is to actually use it when terminating calls.
The LCR data is used a few places with PHP along with the outbound termination in AGI so I have this sitting in a helper class. Create this in classes/lcr.php
:
Asterisk Configuration
We just need to setup a few things in Asterisk to get this going
Extensions.conf
The extension below is set for NANPA but expand the wildcard for you calling area
[lcr]
exten => _1XXXXXXXXXX,1,AGI(${AGI_PATH}/lcr.php,${EXTEN})
same => n,Hangup()
PJSIP.conf
In the setup above. It is expecting a SIP peer or user to be setup as [voxbeam]. Make sure to have termination working before continuing.
AGI Script
The script below is AGI using PHP. I’m using a package PAGI so make sure to add into composer or change around the script for how you need. Create this as just lcr.php
What’s happening here is that it hits the LCR to grab the list of routes with the best match. With the prefix and suffix from Postgres records so dialing +18585550000
will product a dial string of 001110118585550000@voxbeam
. This could fail to dial because the vendor rejected which could be due to a number of reasons like route no longer active, insufficient funds, general 5XX’s. In that case it will go to the next route until it’s successful or runs out of routes to try. We break out of this look if the call had been answered or times out as a no answer.
All Set!
That’s should be everything needed to get going. Load up the LCR with as many vendors as needed and start passing calls through the lcr context in extensions.conf.
I recommend adding in libphonenumber to validate the phone numbers. I didn’t add it into the examples just for simplicity.
Bonus: Slack Slash Command
To make checking the LCR routes easier I had setup a slack slash command so anyone can hit the LCR to get the results.
Web Server
You’ll need to run a web server with PHP(apache, nginx, whatever). Example below is going to use libphonenumber with Geocoder so check out that link and make sure to install PHP intl.
Let’s assume the above is saved as index.php. Test out the script with curl changing localhost to your web server:
curl -X POST \
http://localhost/index.php \
-H 'Cache-Control: no-cache' \
-H 'content-type: multipart/form-data' \
-F token=SLACK_LCR_TOKEN \
-F 'text=+18585550000'
The output should look something like this:
{
"phone_number":"18585550000",
"available":true,
"rate":0.0032,
"iso_3166":"US",
"routes":[
{
"digits":"1858555",
"carrier_name":"voxbeam",
"rate":"0.0032",
"iso_3166":"US",
"route_name":""
},
{
"digits":"1",
"carrier_name":"voxbeam",
"rate":"0.05",
"iso_3166":"US",
"route_name":""
}
]
}
Slack Slash Command Setup
To learn more about slash command check out here: https://api.slack.com/slash-commands
For the integration, this is what I have setup:
command=/lcr
url=https://localhost/index.php (Change to your web server)
method=POST
token=(Save this into the index.php)
After you do this you should be all set. Jump into slack and type in /lcr +18585550000
If all worked out you should get a return like this: