Open in app

Sign in

Write

Sign in

Low Cost Routing on Asterisk with Postgres and Prefix range module

Christopher Mendes
cmendes0101
Published in
7 min readAug 1, 2018

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:

Asterisk
Voip
Postgres
Lcr
Cost

--

--

Christopher Mendes
cmendes0101

Written by Christopher Mendes

24 Followers
Editor for

Help

Status

About

Careers

Press

Blog

Privacy

Terms

Text to speech

Teams