Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 5.3

...

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)

...

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

...

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:

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

...

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

 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)

...

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

...