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