Add Custom Fields in Asterisk’s MySQL CDRs

Add Custom Fields in Asterisk’s MySQL CDRs

  August 07, 2018    ONES Consultants

---

Introduction

This blog is to introduce some unique fields in Asterisk MySQL CDRs for identifying a call and asterisk instance.

Methodology

Following are the steps required:

Step # 1

Introduce following columns in database table `cdr`:

• ip_address
• call_id
• unique_identifier

ALTER TABLE `cdr` ADD `ip_address` VARCHAR(20) NULL; ALTER TABLE `cdr` ADD `call_id` VARCHAR(200) NULL; ALTER TABLE `cdr` ADD `unique_identifier` VARCHAR(100) NULL;

Step # 2

Add following section in asterisk file i.e. cdr_mysql.conf. It will enable the CDR() function to recognize custom fields included in asterisk MySQL CDRs.

[aliases] ip_address = ip_address call_id = call_id unique_identifier = unique_identifier

Step # 3

Following is the dialplan (in file i.e. extensions.conf) to handle custom Asterisk MySQL CDRs.

[default-context] exten => _X.,1,set(CDR(ip_address)=${SHELL(/sbin/ifconfig | /usr/bin/awk '/inet addr/{print substr($2,6)}' | /bin/grep -v '127.0.0.1' | /usr/bin/head -n1):0:-1}) exten => _X.,n,set(CDR(call_id)=${SIPCALLID}) exten => _X.,n,set(CDR(unique_identifier)=${UNIQUEID})

Step # 4

You need to restart asterisk or following commands to apply such changes.

$ /usr/sbin/asterisk -rx "module reload cdr_mysql.so" $ /usr/sbin/asterisk -rx "dialplan reload"

Conclusion:

It is the simplest way to introduce some of custom fields with custom names in existing mysql cdrs of Asterisk.

Read More

ONES Phone Number API

ONES Phone Number API

  August 07, 2018    ONES Consultants

---

Introduction

• These services are developed get updated details of phonenumbers.
• The result is in JSON and valid-flag signifies whether phonenumber is valid or not i.e. 1 or 0 respectively.
• Number can be provided in any format i.e. with spaces, characters etc.
• Phonenumber’s Length must be within range of 4 to 20.

GitHub Repo: libphonenumber-api

Services

Getting Phonenumber’s Information

Phone Number

curl http://localhost/phonenumber?number=+45(3)216-0398

4532160398

Phonenumber’s Type

curl http://localhost/type?number=4532160398

{"type": "FIXED_LINE_OR_MOBILE", "valid-flag": 1}

Phonenumber’s Country Code

curl http://localhost/countrycode?number=4532160398

{"countrycode": "45", "valid-flag": 1}

Phonenumber’s National Significant Number

curl http://localhost/nsn?number=4532160398

{"nsn": "32160398", "valid-flag": 1}

Phonenumber’s Country’s ISO Code

ISO Code in 2 Alphabets

curl http://localhost/iso2?number=4532160398

{"iso": "DK", "valid-flag": 1}

ISO Code in 3 Alphabets

curl http://localhost/iso3?number=4532160398

{"iso": "DNK", "valid-flag": 1}

Phonenumber’s Regional Operator

curl http://localhost/operator?number=4532160398

{"operator": "Telenor", "valid-flag": 1}

Note: Operator’s Names are available in different languages with param i.e. operator-en, operator-fr, operator-it, operator-de.

Phonenumber’s Country

curl http://localhost/country?number=4532160398

{"country": "Denmark", "valid-flag": 1}

Phonenumber’s Location

curl http://localhost/location?number=4532160398

{"country": "Denmark", "valid-flag": 1}

Note: Location Names are available in different languages with param i.e. location-en, location-fr, location-it, location-de.

Phonenumber’s TimeZone

curl http://localhost/timezone?number=4532160398

{"timezone": ["valid-flag": 1}

All details in JSON

curl http://localhost/details?number=004532160398

{  "phonenumber": "4532160398",  "code": "45",  "nsn": "32160398",  "typeid": "FIXED_LINE_OR_MOBILE",  "operator": "Telenor",  "iso3": "DNK",  "iso2": "DK",  "country": "Denmark",  "timezone": ["Europe/Copenhagen"]("Europe/Copenhagen"],),  "is_validnumber": true,  "is_validnumberforRegion": true,  "is_possiblenumber": true  }

Phone Number FORMATs

E164 Format

curl http://localhost/E164-format?number=4532160398

+4532160398

Without E164 Format

curl http://localhost/without-E164-format?number=004532160398

4532160398

National Format

curl http://localhost/national-format?number=004532160398

32 16 03 98

International Format

curl http://localhost/international-format?number=004532160398

+45 32 16 03 98

Verifying Phonenumber

curl http://localhost/validate?number=4532160398

{"valid-flag": 1}

Read More

Troubleshooting ODBC Module in Asterisk

Troubleshooting ODBC Module in Asterisk

  August 07, 2018    ONES Consultants

---

Introduction

This article is to introduce troubleshooting steps for ODBC malfunction for Asterisk.

Description

We are resolving following error for ODBC Connection.

$ echo "select 1" | isql -v asterisk-connector [01000][unixODBC][Driver Manager]Can't open lib '/usr/lib64/libmyodbc5.so' : file not found [ISQL]ERROR: Could not SQLConnect

Methodology

Step # 1

Create a separate directory odbc/ in /usr/lib or /usr/lib64/ (as available):

$ mkdir /usr/lib64/odbc/

Step # 2

Download and Install latest MySQL Connector for ODBC with suitable for your OS:

$ tar -zxvf mysql-connector-odbc*.tar.gz $ cd mysql-connector-odbc-* $ cp lib/* /usr/lib64/odbc/

Step # 3

Create or Edit file /etc/odbcinst.ini to add following contents:

[MySQL] Description = ODBC Driver for MySQL Driver = /usr/lib64/odbc/libmyodbc5w.so Setup = /usr/lib64/odbc/libmyodbc5S.so FileUsage = 1

Step # 4

Create or Edit file /etc/odbc.ini with following contents:

[asterisk-connector] Description           = MySQL connection to 'asterisk' database Driver                = MySQL Database              = <database> Server                = localhost User                  = <user> Password              = <password> Port                  = 3306 Socket                = /var/lib/mysql/mysql.sock

Step # 5

Add following contents in /etc/asterisk/res_odbc.conf:

[asterisk] enabled => yes dsn => asterisk-connector username => <user> password => <password> pooling => no pre-connect => yes

Step # 6

Add your desired function in /etc/asterisk/func_odbc.conf file:

[FULLNAME] dsn=asterisk readsql=SELECT fullname FROM users WHERE extension=${ARG1}

Step # 7

Reload func_odbc.so module or restart asterisk:

Step # 8

Verifying OBDC Connection:

Terminal

To check odbc Connection in terimal

$ echo "select 1" | isql -v asterisk-connector +---------------------------------------+ | Connected!                            | |                                       | | sql-statement                         | | help [tablename]                      | | quit                                  | |                                       | +---------------------------------------+ SQL> select 1 +---------------------+ | 1                   | +---------------------+ | 1                   | +---------------------+ SQLRowCount returns 1 1 rows fetched

Asterisk CLI

To check ODBC Connection in Asterisk CLI:

CLI> odbc show

ODBC DSN Settings

-----------------

  Name:   asterisk

  DSN:    asterisk-connector

    Last connection attempt: 1970-01-01 05:00:00

  Pooled: No

  Connected: Yes

CLI> odbc read ODBC_FULLNAME "XXXX" exec

fullname               ABC

Returned 1 row.  Query executed on handle 0 [asterisk]

Read More

How to Install OpenSIPS?

How to Install OpenSIPS?

  August 07, 2018    ONES Consultants

---

Introduction

This article is to give an overview to install OpenSIPS with its default settings.

Description

In this article, we are installing OpenSIPS version 2.1.0 in Centos OS. So change your settings as per your OS.

Methodology

Before starting installation Process, Install some of the dependencies of OpenSIPS:

$ sudo yum install gcc gcc-c++ bison flex zlib-devel openssl-devel mysql-devel subversion pcre-devel ncurses-devel ncurses

Following is the step by step guide for installing OpenSIPS.

Step # 1

Download and extract OpenSIPS source:

$ wget http://opensips.org/pub/opensips/2.1.0/src/opensips-2.1.0.tar.gz $ tar -zxvf opensips-2.1.0.tar.gz

Step # 2

Edit Private and Shared Memory as per requirement:

$ vi opensips-2.1.0/config.h

Change Following values as per requirement:

#ifdef HP_MALLOC #define PKG_MEM_SIZE 64                         /* Used only if PKG_MALLOC is defined*/ #else #define PKG_MEM_SIZE 8                          /* Used only if PKG_MALLOC is defined*/ #endif #define SHM_MEM_SIZE 128                                /* Used if SH_MEM is defined*/ #define SHM_MAX_SECONDARY_HASH_SIZE 128 #define DEFAULT_SHM_HASH_SPLIT_PERCENTAGE 2     /* Used if SH_MEM is defined*/ #define DEFAULT_SHM_SECONDARY_HASH_SIZE 32

Step # 3

Install OpenSIPS with MySQL database support:

$ cd opensips-2.1.0/ $ make include_modules="db_mysql" prefix="/" all $ make include_modules="db_mysql" prefix="/" install

Step # 4

Create OpenSIPS user for running OpenSIPS:

$ adduser opensips

Step # 5

Configure init Script for OpenSIPS

$ cp opensips-2.1.0/packaging/fedora/opensips.init /etc/init.d/opensips $ chmod a+x /etc/init.d/opensips

Change OpenSIPS executable path in init script.

$ which opensips   /sbin/opensips $ vi /etc/init.d/opensips

Edit OpenSIPS path from /usr/sbin/opensips to /sbin/opensips as following:

opensips=/sbin/$prog

Step # 6

Starting and Verifying OpenSIPS Installation:

$ service opensips start Starting opensips: [ OK ]   $ netstat -pln | grep opensips udp 0 0 127.0.0.1:5060 0.0.0.0:* 9814/opensips   $ ps aux | grep opensips opensips 9814 0.0 0.2 100348 4524 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9815 0.2 0.0 100348 948 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9816 0.0 0.0 100348 984 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9817 0.0 0.0 100348 1112 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9818 0.0 0.0 100348 1128 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9819 0.0 0.0 100348 1108 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg opensips 9820 0.0 0.0 100348 1128 ? S 17:38 0:00 /usr/local/sbin/opensips -u opensips -g opensips -P /var/run/opensips.pid -f /etc/opensips/opensips.cfg

Step # 7

Checking OpenSIPS logs:

$ tailf /var/log/messages | grep opensips   Jan 27 17:44:41 OSIP opensips: INFO:core:fix_poll_method: using epoll_lt as the IO watch method (auto detected) Jan 27 17:44:41 OSIP opensips: INFO:core:shm_mem_init: allocating SHM block Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: NOTICE:core:main: version: opensips 2.1.1 (x86_64/linux) Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:main: using 32 Mb shared memory Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:main: using 2 Mb private memory per process Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event <E_CORE_THRESHOLD(0)> Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event <E_CORE_SHM_THRESHOLD(1)> Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event <E_CORE_PKG_THRESHOLD(2)> Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:sl:mod_init: Initializing StateLess engine Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:maxfwd:mod_init: initializing... Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event <E_MYSQL_CONNECTION(3)> Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:tm:mod_init: TM - initializing... Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:rr:mod_init: rr - initializing Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:sipmsgops:mod_init: initializing... Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:mod_init: initializing UDP-plain protocol Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:probe_max_sock_buff: using rcv buffer of 448 kb Jan 27 17:44:41 OSIP opensips: INFO:core:daemonize: pre-daemon process exiting with 0

Now you can start Configuring OpenSIPS Database

Read More

Configuring OpenSIPS database

Configuring OpenSIPS database

  August 07, 2018    ONES Consultants

---

Introduction

This article is to provide step by step to configure OpenSIPS database after OpenSIPS Installation.

Description

In this article, we are installing OpenSIPS version 2.1.0 in Centos OS. So change your settings as per your OS.

Methodology

Following is the step by step guide for installing OpenSIPS.

Step # 1

Create User and Database for OpenSIPS:

CREATE DATABASE `opensips`; CREATE USER 'opensips'@'%' IDENTIFIED BY '<password>'; GRANT ALL PRIVILEGES ON `opensips`.* TO 'opensips'@'%'; FLUSH PRIVILEGES; DROP DATABASE `opensips`;

Step # 2

Edit opensipsctlrc file for database information:

$ vi /etc/opensips/opensipsctlrc

Edit following parameters for connection with MySQL Database created in previous step:

DBENGINE = MYSQL DBHOST = localhost DBNAME = opensips DBRWUSER = opensips DBRWPW = "" MYSQL_ENGINE = "MyISAM" DBROOTUSER = "root" USERCOL = "username" ETCDIR = etc/

Step # 3

Create database and tables for OpenSIPS:

$ opensipsdbctl create MySQL password for opensips2:   INFO: test server charset INFO: creating database opensips ... INFO: Using table engine MyISAM. INFO: Core OpenSIPS tables successfully created. Install presence related tables? (y/n): y INFO: creating presence tables into opensips ... INFO: Presence tables successfully created. Install tables for imc cpl siptrace domainpolicy carrierroute userblacklist b2b cachedb_sql registrant call_center fraud_detection? (y/n): y INFO: creating extra tables into opensips ... INFO: Extra tables successfully created.

Step # 4

Verifying OpenSIPS database:

$ mysql -u opensips -p opensips Enter password:   mysql> show tables; +---------------------+ | Tables_in_opensips | +---------------------+ | acc            | | active_watchers    | | address            | | aliases            | | b2b_entities       | | b2b_logic          | | cachedb            | | carrierfailureroute| | carrierroute       | | cc_agents          | | cc_calls           | | cc_cdrs            | | cc_flows           | | cpl                | | dbaliases          | | dialog             | | dialplan           | | dispatcher         | | domain             | | domainpolicy       | | dr_carriers        | | dr_gateways        | | dr_groups          | | dr_partitions      | | dr_rules           | | fraud_detection    | | globalblacklist    | | grp                | | imc_members        | | imc_rooms          | | load_balancer      | | location           | | missed_calls       | | pdt                | | presentity         | | pua                | | re_grp             | | registrant         | | rls_presentity     | | rls_watchers       | | route_tree         | | rtpproxy_sockets   | | silo               | | sip_trace          | | speed_dial         | | subscriber         | | uri                | | userblacklist      | | usr_preferences    | | version            | | watchers           | | xcap               | +---------------------+ 52 rows in set (0.00 sec)

Step # 5

In order to load MySQL module in opensips.cfg file:

loadmodule "db_mysql.so"

In order to add db_url parameter for other Modules:

modparam("", "db_url", "mysql://opensips:@localhost/opensips")

Read More