ONES Blog

Blogs on System Administration, Scripting, System Monitoring, Cloud Computing & Virtualizaton, VoIP Systems, SMS Systems and Web Applications Development.

Add Custom Fields in Asterisk’s MySQL CDRs

  August 07, 2018    

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.

   / / / /

  1 Comment

ONES Phone Number API

  August 07, 2018    

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



4532160398

Phonenumber’s Type



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

Phonenumber’s Country Code



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

Phonenumber’s National Significant Number



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

Phonenumber’s Country’s ISO Code


ISO Code in 2 Alphabets



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

ISO Code in 3 Alphabets



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

Phonenumber’s Regional Operator



{"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



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

Phonenumber’s Location



{"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



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

All details in JSON



{
 "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



+4532160398

Without E164 Format



4532160398

National Format



32 16 03 98

International Format



+45 32 16 03 98

Verifying Phonenumber



{"valid-flag": 1}

   / / / /

  1 Comment

Troubleshooting ODBC Module in Asterisk

  August 07, 2018    

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]

   / / /

  No Comments

How to Install OpenSIPS?

  August 07, 2018    

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 &amp;amp;amp;amp;lt;E_CORE_THRESHOLD(0)&amp;amp;amp;amp;gt;
Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event &amp;amp;amp;amp;lt;E_CORE_SHM_THRESHOLD(1)&amp;amp;amp;amp;gt;
Jan 27 17:44:41 OSIP /usr/local/sbin/opensips[9929]: INFO:core:evi_publish_event: Registered event &amp;amp;amp;amp;lt;E_CORE_PKG_THRESHOLD(2)&amp;amp;amp;amp;gt;
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 &amp;amp;amp;amp;lt;E_MYSQL_CONNECTION(3)&amp;amp;amp;amp;gt;
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

   / /

  No Comments

Configuring OpenSIPS database

  August 07, 2018    

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")

   / / /

  No Comments