Goal (short description)
How to get CDR like information from Openser and Mysql
Applicability
OpenSER can acc module is used to account transactions information to different backends like syslog, SQL, RADIUS and DIAMETER (beta version) The goal is creating a simple database accounting with OpenSER and mysql, creating CDR like information using OpenSER acc module. OpenSER can't make CDR directly (account a dialog) it can account only a transaction. Currently AFAIK you can't get CDR in one step because OpenSER accounting only care about transactions and don't know about the a full dialog. In latest SVN version in module dialog i found a $DLG_lifetime Pseudo variable what in future maybe can be used to achieve this goal, but today in current stable version it is not available.
In this example i used OpenSER version 1.1. I tried out version 1.2 and the latest SVN version too. In version 1.2 accounting is changed little bit: not core columns moved from standard log space to optional extra log space to improve the speed of accounting.
I found quoting problem:
Openser db accounting use column names and table names what are containing underscore character like "sip_method".
I experienced an sql expression quoting problem using module mysql.so (OpenSER 1.2++).
Applicability
This is a simple and not a complex example storing accounting information in MySQL database. What can meet the demand to get overview and statics. For more complex carrier grade accounting with radius please download and use this tool:
Prerequisites (OS, dependencies on other software)
Debian (etch) +OpenSER 1.1
Configuration (OS agnostic)
Create mysql database
| No Format |
|---|
openser_mysql create
|
Add these lines to default openser.cfg file to configure and load mysql and acc module.
| No Format |
|---|
loadmodule "/usr/lib/openser/modules/mysql.so"
loadmodule "/usr/lib/openser/modules/acc.so"
modparam("acc", "early_media", 1)
modparam("acc", "report_cancels", 1)
modparam("acc", "db_flag", 2)
modparam("acc", "report_ack", 0)
modparam("acc", "db_missed_flag", 3)
modparam("acc", "failed_transaction_flag", 4)
modparam("acc", "db_url", "mysql://openser:openserrw@localhost/openser")
|
Flag all request. add setflag to routing logic like bottom.
| No Format |
|---|
# ------------------------- request routing logic ------------------- # main routing logic route{ setflag(2); setflag(3); setflag(4); . . . } |
OS specific help
Install packages
| No Format |
|---|
aptitude install openser openser-mysql-module mysql-server
|
change deafult config
Change line RUN_OPENSER=yes in /etc/default/openser
run
| No Format |
|---|
/etc/init.d/openser start
|
Validation, confirmation tests
If everything went fine after you made few calls you should see similar acc table in your mysql database.
| No Format |
|---|
mysql> select * from acc limit 4;
+----------------------------------------------------------------+----------------------------------------------------------------+------------+------------+---------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------+---------------+------------------+------------------+---------------------+---------------------+---------+---------+
| sip_from | sip_to | sip_status | sip_method | i_uri | o_uri | from_uri | to_uri | sip_callid | username | domain | fromtag | totag | time | timestamp | src_leg | dst_leg |
+----------------------------------------------------------------+----------------------------------------------------------------+------------+------------+---------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------+---------------+------------------+------------------+---------------------+---------------------+---------+---------+
| "Fekete" <sip:1@195.111.158.8;user=phone>;tag=bc6e76cb7a5f1f34 | <sip:2@195.111.158.8;user=phone>;tag=4f6f7ed7bfe93398 | 200 | INVITE | sip:2@195.111.158.8;user=phone | sip:2@195.111.158.20 | sip:1@195.111.158.8;user=phone | sip:2@195.111.158.8;user=phone | 53381df64ed9fd4d@195.111.158.13 | 1 | 195.111.158.8 | bc6e76cb7a5f1f34 | 4f6f7ed7bfe93398 | 2007-05-23 10:08:53 | 2007-05-23 12:08:53 | NULL | NULL |
| <sip:2@195.111.158.8;user=phone>;tag=4f6f7ed7bfe93398 | "Fekete" <sip:1@195.111.158.8;user=phone>;tag=bc6e76cb7a5f1f34 | 200 | BYE | sip:1@195.111.158.13;user=phone | sip:1@195.111.158.13;user=phone | sip:2@195.111.158.8;user=phone | sip:1@195.111.158.8;user=phone | 53381df64ed9fd4d@195.111.158.13 | 2 | 195.111.158.8 | 4f6f7ed7bfe93398 | bc6e76cb7a5f1f34 | 2007-05-23 10:08:59 | 2007-05-23 12:08:59 | NULL | NULL |
| "Feh%r" <sip:2@195.111.158.8>;tag=b611c8e0d41149e0 | <sip:1@195.111.158.8>;tag=6ed1648985bb8543 | 200 | INVITE | sip:1@195.111.158.8 | sip:1@195.111.158.13;user=phone | sip:2@195.111.158.8 | sip:1@195.111.158.8 | e91b46fbcf182743@195.111.158.20 | 2 | 195.111.158.8 | b611c8e0d41149e0 | 6ed1648985bb8543 | 2007-05-23 10:09:08 | 2007-05-23 12:09:08 | NULL | NULL |
| <sip:1@195.111.158.8>;tag=6ed1648985bb8543 | "Feh%r" <sip:2@195.111.158.8>;tag=b611c8e0d41149e0 | 200 | BYE | sip:2@195.111.158.20 | sip:2@195.111.158.20 | sip:1@195.111.158.8 | sip:2@195.111.158.8 | e91b46fbcf182743@195.111.158.20 | 1 | 195.111.158.8 | 6ed1648985bb8543 | b611c8e0d41149e0 | 2007-05-23 10:09:11 | 2007-05-23 12:09:11 | NULL | NULL |
+----------------------------------------------------------------+----------------------------------------------------------------+------------+------------+---------------------------------+---------------------------------+--------------------------------+--------------------------------+---------------------------------+----------+---------------+------------------+------------------+---------------------+---------------------+---------+---------+
4 rows in set (0.00 sec)
|
To get CDR like information you can use this select query:
| No Format |
|---|
SELECT a.from_uri,
a.to_uri,
a.sip_callid as sip_callid,
a.timestamp as call_start ,
b.timestamp as call_stop,
TIMESTAMPDIFF(SECOND,a.timestamp,b.timestamp) as call_duration
FROM acc as a,
acc as b
WHERE a.sip_method="INVITE" and b.sip_method="BYE" and a.sip_callid=b.sip_callid
GROUP BY a.sip_callid
ORDER by a.timestamp;
|
After it you should see similar output
| No Format |
|---|
mysql> SELECT a.from_uri,
-> a.to_uri,
-> a.sip_callid as sip_callid,
-> a.timestamp as call_start ,
-> b.timestamp as call_stop,
-> TIMESTAMPDIFF(SECOND,a.timestamp,b.timestamp) as call_duration
-> FROM acc as a,
-> acc as b
-> WHERE a.sip_method="INVITE" and b.sip_method="BYE" and a.sip_callid=b.sip_callid
-> GROUP BY a.sip_callid
-> ORDER by a.timestamp;
+--------------------------------+--------------------------------+---------------------------------+---------------------+---------------------+---------------+
| from_uri | to_uri | sip_callid | call_start | call_stop | call_duration |
+--------------------------------+--------------------------------+---------------------------------+---------------------+---------------------+---------------+
| sip:1@195.111.158.8;user=phone | sip:2@195.111.158.8;user=phone | 53381df64ed9fd4d@195.111.158.13 | 2007-05-23 12:08:53 | 2007-05-23 12:08:59 | 6 |
| sip:2@195.111.158.8 | sip:1@195.111.158.8 | e91b46fbcf182743@195.111.158.20 | 2007-05-23 12:09:08 | 2007-05-23 12:09:11 | 3 |
+--------------------------------+--------------------------------+---------------------------------+---------------------+---------------------+---------------+
2 rows in set (0.01 sec)
|