View source for 3rd party software/Asterisk

[[Category:3rd_party_software]]

Asterisk can be used as <b><em>Soft Switch</em></b> (among many other things).  Freeside can examine recorded CDR data for subscriber billing purposes.

== cdr_pgsql ==

Call detail records are stored by asterisk (by means of the cdr_pgsql extension) into a [[postgres]] database of our choice.  We will need to edit <code>/etc/asterisk/cdr_pgsql.conf</code>, add the asterisk user to postgres, add rights for asterisk to access the cdr table, and verify settings actually work.

=== cdr_pgsql.conf ===

This is our example config file.  You will need to change the password to suit your install.

<pre>; Sample Asterisk config file for CDR logging to PostgresSQL

[global]
hostname=postgres
port=5432
dbname=freeside
password=FIXTHIS
user=asterisk
table=cdr               ;SQL table where CDRs will be inserted
spool=pgsql.spool</pre>

=== pg_hba.conf ===

This <code>pg_hba.conf</code> sample is only a snippet of the ACL to permit asterisk to connect.

<pre># TYPE          DATABASE        USER            CIDR-ADDRESS            METHOD
hostssl         freeside        asterisk        10.14.0.0/24              md5</pre>

'''Reload postgres'''

<pre>freeside:~# /etc/init.d/postgresql-8.1 reload
Reloading PostgreSQL 8.1 database server: main.
freeside:~#</pre>

If you forget these steps, you might see this error message on the asterisk console (or logs) after the call is finished. (eg, at hangup)

<pre>Feb 20 21:39:52 ERROR[4322]: cdr_pgsql.c:89 pgsql_log: cdr_pgsql: Reason: FATAL:  no pg_hba.conf entry for host "10.141.0.5", user "asterisk", database "freeside", SSL off</pre>

See Also: [http://www.postgresql.org/docs/8.1/interactive/client-authentication.html#AUTH-PG-HBA-CONF pg_hba.conf at postgres.org] for more information on Postgres client authentication.

=== Create asterisk pg user ===

As the postgres superuser, create a non-privileged asterisk user, and set the password saved in <code>cdr_pgsql.conf</code>.

<pre>postgres@freeside:~$ createuser -P asterisk
Enter password for user "asterisk": 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE</pre>

=== psql login test ===

We login to the database from the asterisk server to verify our setup is (so far) working okay.  '''pghostname''' is the postgres host where your freeside database is.

<pre>pbx:~$ psql -h pghostname -U asterisk freeside
Password for user asterisk:
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

freeside=>
</pre>

=== GRANT asterisk permissions ===

We are allowing asterisk limited access to the Freeside database.  Freeside isn't expected to have GRANT permissions on the freeside database.  Connect as the [[postgres superuser]].

Code snippet to '''GRANT''' selective permissions to asterisk.

<pre>GRANT INSERT ON cdr TO asterisk;
GRANT UPDATE ON cdr_acctid_seq to asterisk;</pre>

The psql client will respond similar to this:

<pre>freeside:~# su - postgres
postgres@freeside:~$ psql freeside
Welcome to psql 8.1.11, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

freeside=# GRANT INSERT ON cdr TO asterisk;
GRANT
freeside=# GRANT UPDATE ON cdr_acctid_seq to asterisk;
GRANT
freeside=#</pre>

=== Reload cdr_pgsql ===

Changes to the cdr_pgsql.conf only need a reload of the <code>cdr_pgsql.so</code> module for changes to be applied immediately.

<code>asterisk -rx "reload cdr_pgsql.so"</code>

=== Make a test call ===

If your asterisk configuration is able to make calls, place a test call that applies to your needs of freeside and asterisk (eg, a sample call that would be billed).

=== Verify new data records ===

We check for CDR entries to confirm our call progress is being recorded properly.

<pre>freeside=# SELECT acctid,calldate,src,dst,duration,billsec,disposition from cdr;
 acctid |        calldate        |     src    |    dst      | duration | billsec | disposition
--------+------------------------+------------+-------------+----------+---------+-------------
      1 | 2008-02-20 15:40:00+00 | 8005550149 | 18004664411 |       19 |       0 | NO ANSWER
      2 | 2008-02-20 15:48:52+00 | 8005550149 | 18004664411 |       14 |      10 | ANSWERED
(2 rows)

freeside=#</pre>

=== Troubleshooting ===

Trouble may arise due to insufficent permissions, invalid username or password in the config files, failure to reload cdr_pgsql after settings are updated, and a number of other related factors.  The system logs and asterisk console are a good starting point when attempting to isolate underlying causes of your issue.

This sample was due to insufficent '''GRANT''' permissions to asterisk on the cdr_acctid_seq object.

<pre>Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:159 pgsql_log: cdr_pgsql: Failed to insert call detail record into database!
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:160 pgsql_log: cdr_pgsql: Reason: ERROR:  permission denied for sequence cdr_acctid_seq
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:161 pgsql_log: cdr_pgsql: Connection may have been lost... attempting to reconnect.
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:164 pgsql_log: cdr_pgsql: Connection reestablished.
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:170 pgsql_log: cdr_pgsql: HARD ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
Feb 20 15:37:40 ERROR[3404]: cdr_pgsql.c:171 pgsql_log: cdr_pgsql: Reason: ERROR:  permission denied for sequence cdr_acctid_seq</pre>

== Related Topics ==
* [[Freeside:1.7:Documentation:Administration:VoIP]]
[[Category:svc_phone]]

== See Also ==
* [http://en.wikipedia.org/w/index.php?title=Telephone_switch Telephone Switching] on Wikipedia.
* [http://www.voip-info.org/wiki/view/Asterisk+cdr+pgsql voip-info.org asterisk cdr_pgsql howto]
* [http://www.postgresql.org/docs/manuals/ Manuals at postgres.org]

[http://www.prlog.org/11289974-phone-number-lookup-verizon-phone-number-reverse-lookup-to-get-information-you-need-quickly.html reverse lookup]

[http://thetvtopc.com/Reverse_Cell_Phone_Lookup_Number reverse phone lookup]