• Created by Unknown User (dimitris), last modified by Unknown User (misi) on May 23, 2007

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 5 Next »

Goal (short description)

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

openser_mysql create

Add these lines to default openser.cfg file to configure and load mysql and acc module.

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.

# -------------------------  request routing logic -------------------

# main routing logic

route{
        setflag(2);
        setflag(3);
        setflag(4);
.
.
.
}

OS specific help

Install packages

aptitude install openser openser-mysql-module mysql-server

change deafult config

Change line RUN_OPENSER=yes in /etc/default/openser

run

/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.

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

To get CDR like information you can use this select query:

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

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

09721b53dfbedb47@195.111.158.13

2007-05-21 12:53:48

2007-05-21 12:53:50

2

sip:2@195.111.158.8

sip:1@195.111.158.8

6b8a776bff875ff2@195.111.158.20

2007-05-21 12:57:02

2007-05-21 12:57:04

2

sip:1@195.111.158.8;user=phone

sip:2@195.111.158.8;user=phone

a862e163987edf27@195.111.158.13

2007-05-21 13:04:48

2007-05-21 13:04:50

2

sip:2@195.111.158.8

sip:1@195.111.158.8

5b443d05b1e5374b@195.111.158.20

2007-05-21 13:05:11

2007-05-21 13:05:15

4

sip:1@195.111.158.8;user=phone

sip:2@195.111.158.8;user=phone

f2fc2641b218c96c@195.111.158.13

2007-05-21 13:08:15

2007-05-21 13:08:22

7

sip:1@195.111.158.8;user=phone

sip:2@195.111.158.8;user=phone

5dcac6a43d45c5a2@195.111.158.13

2007-05-21 13:16:26

2007-05-21 13:16:37

11

sip:1@195.111.158.8;user=phone

sip:2@195.111.158.8;user=phone

af792f2de25c311d@195.111.158.13

2007-05-21 13:20:26

2007-05-21 13:20:37

11

sip:2@195.111.158.8

sip:1@195.111.158.8

f9284453d7a22251@195.111.158.20

2007-05-21 13:21:23

2007-05-21 13:21:29

6

sip:1@195.111.158.8;user=phone

sip:2@195.111.158.8;user=phone

50897e2d6749d01d@195.111.158.13

2007-05-22 07:39:38

2007-05-22 07:39:51

13

sip:2@195.111.158.8

sip:1@195.111.158.8

95df6a50584de330@195.111.158.20

2007-05-22 07:39:55

2007-05-22 07:40:19

24

  • No labels