<div dir="ltr"><div>I have install Kamailio 4.1 with MySQL database so all users stored
in MySQL (authentication), Later I have installed FreeRadius for
accounting only, Just accounting (Invite/Bye). In short MySQL for
authentication and FreeRadius for accounting ( CDRTool). <br>
<br></div><div>Problems: If i register user using SIP phone and call
other SIP extension then freeradius start accounting and insert entry in
MySQL/radaccts tables if i end call then it doesn't update END
accounting ( Bye ). I have google it everywhere but didn't get single
clue so i hope i get something here from you guys<br>
<br></div><div>-- kamailio.cfg ( default file i just add following radius section) <br><br>loadmodule "acc.so"<br>loadmodule "acc_radius.so"<br>...<br>...<br># ------- FreeRadius Accounting -------<br>
modparam("acc_radius", "radius_flag", 1)<br>modparam("acc_radius", "radius_missed_flag", 2)<br>modparam("acc", "log_flag", 1)<br>modparam("acc", "log_missed_flag", 1)<br>
modparam("acc_radius", "service_type", 15)<br>modparam("acc_radius", "radius_config", "/etc/radiusclient-ng/radiusclient.conf")<br>modparam("acc_radius", "radius_extra", "User-Name=$Au")<br>
<br></div><div><br></div><div>-- /etc/freeradius/sql.conf<br><br>sql {<br> driver = "rlm_sql_mysql"<br> server = "localhost"<br> login = "radius"<br>
password = "radius"<br> radius_db = "radius"<br> sqltrace = no<br> sqltracefile = ${logdir}/sqltrace-%Y%m%d.log<br> num_sql_socks = 25<br>
connect_failure_retry_delay = 60<br><br> accounting_start_query = "\<br> CALL insert_radacct_record( \<br> 'radius', \<br> '%{Acct-Session-Id}', \<br>
'%{Acct-Unique-Session-Id}', \<br> '%{Billing-Party}', \<br> '%{Billing-Party}', \<br> '%{SIP-Proxy-IP}', \<br> '%{NAS-Port}', \<br>
FROM_UNIXTIME('%S'), \<br> '0', \<br> '0', \<br> '0', \<br> '0', \<br> '%{Called-Station-Id}', \<br>
'%{Calling-Station-Id}', \<br> '%{Sip-Response-Code}', \<br> '%{Service-Type}', \<br> '%{ENUM-TLD}', \<br> '%{Framed-IP-Address}', \<br>
UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \<br> UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \<br> '%{Sip-Response-Code}', \<br> '%{Sip-Method}', \<br>
'%{Sip-Translated-Request-URI}', \<br> '%{Sip-To-Tag}', \<br> '%{Sip-From-Tag}', \<br> '%{Sip-RPId}', \<br> '%{Source-IP}', \<br>
'%{Source-Port}', \<br> '%{Canonical-URI}', \<br> '', \<br> '', \<br> '%{Sip-Application-Type}', \<br> '%{User-Agent}', \<br>
'%{From-Header}' \<br> )"<br><br> accounting_stop_query = "\<br> CALL update_radacct_record( \<br> 'radius', \<br> FROM_UNIXTIME('%S'), \<br>
UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', \<br> '', \<br> '%{X-RTP-Stat}', \<br> '%{Acct-Session-Id}', \<br> '%{Sip-To-Tag}', \<br>
'%{Sip-From-Tag}' \<br> )"<br><br><br></div><div>-- Radius logs <br><br>rad_recv: Accounting-Request packet from host 127.0.0.1 port 36379, id=11, length=152<br> Acct-Status-Type = Start<br>
Service-Type = Sip-Session<br> Sip-Response-Code = 200<br> Sip-Method = Invite<br> Event-Timestamp = "Aug 12 2014 22:45:36 EDT"<br> Sip-From-Tag = "59eb1875"<br> Sip-To-Tag = "d443e36e"<br>
Acct-Session-Id = "M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ."<br> User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"<br> NAS-Port = 5060<br> Acct-Delay-Time = 0<br>
NAS-IP-Address = 127.0.0.1<br>Tue Aug 12 22:45:36 2014 : Info: # Executing section preacct from file /etc/freeradius/sites-enabled/default<br>Tue Aug 12 22:45:36 2014 : Info: +- entering group preacct {...}<br>Tue Aug 12 22:45:36 2014 : Info: ++[preprocess] returns ok<br>
Tue Aug 12 22:45:36 2014 : Info: [acct_unique] Hashing 'NAS-Port =
5060,Client-IP-Address = 127.0.0.1,NAS-IP-Address =
127.0.0.1,Acct-Session-Id = "M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ.",User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"'<br>
Tue Aug 12 22:45:36 2014 : Info: [acct_unique] Acct-Unique-Session-ID = "117c7740de62ba6c".<br>Tue Aug 12 22:45:36 2014 : Info: ++[acct_unique] returns ok<br>Tue Aug 12 22:45:36 2014 : Info: [suffix] Looking up realm "192.168.1.5" for User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"<br>
Tue Aug 12 22:45:36 2014 : Info: [suffix] No such realm "192.168.1.5"<br>Tue Aug 12 22:45:36 2014 : Info: ++[suffix] returns noop<br>Tue Aug 12 22:45:36 2014 : Info: ++[files] returns noop<br>Tue Aug 12 22:45:36 2014 : Info: # Executing section accounting from file /etc/freeradius/radiusd.conf<br>
Tue Aug 12 22:45:36 2014 : Info: +- entering group accounting {...}<br>Tue Aug 12 22:45:36 2014 : Info: [detail] expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/freeradius/radacct/<a href="http://127.0.0.1/detail-20140812" target="_blank">127.0.0.1/detail-20140812</a><br>
Tue Aug 12 22:45:36 2014 : Info: [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/freeradius/radacct/<a href="http://127.0.0.1/detail-20140812" target="_blank">127.0.0.1/detail-20140812</a><br>
Tue Aug 12 22:45:36 2014 : Info: [detail] expand: %t -> Tue Aug 12 22:45:36 2014<br>
Tue Aug 12 22:45:36 2014 : Info: ++[detail] returns ok<br>Tue Aug 12 22:45:36 2014 : Info: [sql] expand: CALL insert_radacct_record( 'radius', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{Billing-Party}', '%{Billing-Party}', '%{SIP-Proxy-IP}', '%{NAS-Port}', FROM_UNIXTIME('%S'), '0', '0', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Sip-Response-Code}', '%{Service-Type}', '%{ENUM-TLD}', '%{Framed-IP-Address}', UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', '%{Sip-Response-Code}', '%{Sip-Method}', '%{Sip-Translated-Request-URI}', '%{Sip-To-Tag}', '%{Sip-From-Tag}', '%{Sip-RPId}', '%{Source-IP}'<br>
Tue Aug 12 22:45:36 2014 : Debug: rlm_sql (sql): Reserving sql socket id: 24<br>Tue Aug 12 22:45:36 2014 : Debug: rlm_sql (sql): Released sql socket id: 24<br>Tue Aug 12 22:45:36 2014 : Info: ++[sql] returns ok<br>Sending Accounting-Response of id 11 to 127.0.0.1 port 36379<br>
Tue Aug 12 22:45:36 2014 : Info: Finished request 0.<br>Tue Aug 12 22:45:36 2014 : Info: Cleaning up request 0 ID 11 with timestamp +13<br>Tue Aug 12 22:45:36 2014 : Debug: Going to the next request<br>Tue Aug 12 22:45:36 2014 : Info: Ready to process requests.<br>
<br><br><br><br>rad_recv: Accounting-Request packet from host 127.0.0.1 port 35683, id=149, length=152<br> Acct-Status-Type = Stop<br> Service-Type = Sip-Session<br> Sip-Response-Code = 200<br> Sip-Method = Bye<br>
Event-Timestamp = "Aug 12 2014 22:46:20 EDT"<br> Sip-From-Tag = "59eb1875"<br> Sip-To-Tag = "d443e36e"<br> Acct-Session-Id = "M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ."<br>
User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"<br> NAS-Port = 5060<br> Acct-Delay-Time = 0<br> NAS-IP-Address = 127.0.0.1<br>Tue Aug 12 22:46:20 2014 : Info: # Executing section preacct from file /etc/freeradius/sites-enabled/default<br>
Tue Aug 12 22:46:20 2014 : Info: +- entering group preacct {...}<br>Tue Aug 12 22:46:20 2014 : Info: ++[preprocess] returns ok<br>Tue
Aug 12 22:46:20 2014 : Info: [acct_unique] Hashing 'NAS-Port =
5060,Client-IP-Address = 127.0.0.1,NAS-IP-Address =
127.0.0.1,Acct-Session-Id = "M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ.",User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"'<br>
Tue Aug 12 22:46:20 2014 : Info: [acct_unique] Acct-Unique-Session-ID = "117c7740de62ba6c".<br>Tue Aug 12 22:46:20 2014 : Info: ++[acct_unique] returns ok<br>Tue Aug 12 22:46:20 2014 : Info: [suffix] Looking up realm "192.168.1.5" for User-Name = "<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>"<br>
Tue Aug 12 22:46:20 2014 : Info: [suffix] No such realm "192.168.1.5"<br>Tue Aug 12 22:46:20 2014 : Info: ++[suffix] returns noop<br>Tue Aug 12 22:46:20 2014 : Info: ++[files] returns noop<br>Tue Aug 12 22:46:20 2014 : Info: # Executing section accounting from file /etc/freeradius/radiusd.conf<br>
Tue Aug 12 22:46:20 2014 : Info: +- entering group accounting {...}<br>Tue Aug 12 22:46:20 2014 : Info: [detail] expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/freeradius/radacct/<a href="http://127.0.0.1/detail-20140812" target="_blank">127.0.0.1/detail-20140812</a><br>
Tue Aug 12 22:46:20 2014 : Info: [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/freeradius/radacct/<a href="http://127.0.0.1/detail-20140812" target="_blank">127.0.0.1/detail-20140812</a><br>
Tue Aug 12 22:46:20 2014 : Info: [detail] expand: %t -> Tue Aug 12 22:46:20 2014<br>
Tue Aug 12 22:46:20 2014 : Info: ++[detail] returns ok<br>Tue Aug 12 22:46:20 2014 : Info: [sql] expand: CALL update_radacct_record( 'radius', FROM_UNIXTIME('%S'), UNIX_TIMESTAMP('%S') - '%{Event-Timestamp}', '', '%{X-RTP-Stat}', '%{Acct-Session-Id}', '%{Sip-To-Tag}', '%{Sip-From-Tag}' ) -> CALL update_radacct_record(
'radius', FROM_UNIXTIME('2014-08-12
22:46:20'), UNIX_TIMESTAMP('2014-08-12 22:46:20') - 'Aug
12 2014 22:46:20 EDT', '',
'', 'M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ.', 'd443e36e', '59eb1875' )<br>
Tue Aug 12 22:46:20 2014 : Debug: rlm_sql (sql): Reserving sql socket id: 23<br>Tue
Aug 12 22:46:20 2014 : Info: [sql] expand: stop packet with zero
session length. [user '%{User-Name}', nas '%{NAS-IP-Address}'] ->
stop packet with zero session length. [user '<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>', nas '127.0.0.1']<br>
Tue Aug 12 22:46:20 2014 : Info: [sql] stop packet with zero session length. [user '<a href="mailto:2002@192.168.1.5" target="_blank">2002@192.168.1.5</a>', nas '127.0.0.1']<br>Tue Aug 12 22:46:20 2014 : Debug: rlm_sql (sql): Released sql socket id: 23<br>
Tue Aug 12 22:46:20 2014 : Info: ++[sql] returns noop<br>Sending Accounting-Response of id 149 to 127.0.0.1 port 35683<br>Tue Aug 12 22:46:20 2014 : Info: Finished request 1.<br>Tue Aug 12 22:46:20 2014 : Info: Cleaning up request 1 ID 149 with timestamp +57<br>
Tue Aug 12 22:46:20 2014 : Debug: Going to the next request<br>Tue Aug 12 22:46:20 2014 : Info: Ready to process requests.<br><br><br></div>-- SQL tables ( Time is wired 1969 )<br><br>mysql> select * from radacct196912;<br>
+-----------+-----------------<div>-----------------------------+------------------+----------+-------+--------------+-----------+-------------+---------------------+---------------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-----------------+------------------+--------------------+-------------+---------+-----------------+----------------+---------------+-----------+-----------------+----------+------------+-------------------------+---------------+--------------------+-----------+---------+---------------+----------+------------+--------------+-----------+-----------+---------------+------+-------+------------+-----------+-----------+---------------+------------+-----------+---------+<br>
| RadAcctId | AcctSessionId |
AcctUniqueId | UserName | Realm | NASIPAddress | NASPortId |
NASPortType | AcctStartTime | AcctStopTime |
AcctSessionTime | AcctAuthentic | ConnectInfo_start | ConnectInfo_stop |
AcctInputOctets | AcctOutputOctets | CalledStationId | CallingStationId
| AcctTerminateCause | ServiceType | ENUMtld | FramedIPAddress |
AcctStartDelay | AcctStopDelay | SipMethod | SipResponseCode | SipToTag |
SipFromTag | SipTranslatedRequestURI | SipUserAgents |
SipApplicationType | SipCodecs | SipRPID | SipRPIDHeader | SourceIP |
SourcePort | CanonicalURI | DelayTime | Timestamp | DestinationId | Rate
| Price | Normalized | BillingId | MediaInfo | RTPStatistics |
FromHeader | UserAgent | Contact |<br>
+-----------+----------------------------------------------+------------------+----------+-------+--------------+-----------+-------------+---------------------+---------------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-----------------+------------------+--------------------+-------------+---------+-----------------+----------------+---------------+-----------+-----------------+----------+------------+-------------------------+---------------+--------------------+-----------+---------+---------------+----------+------------+--------------+-----------+-----------+---------------+------+-------+------------+-----------+-----------+---------------+------------+-----------+---------+<br>
| 1 | Y2ExZjhkMTU4ODRmN2MwMGM1N2Q1Y2E5YTA5ZjAzNjg |
e46f9d0439141495 | | | | 5060
| | 1969-12-31 19:00:00 | 1969-12-31 19:00:00
| 0 | NULL | NULL |
| 0 | 0 |
| | 200 | Sip-Session |
| | 1407883391 | 0 | Invite
| 200 | 70cc9216 | e2cb1f0c |
| | | |
| | | | |
| 0 | | | NULL | 0 | |
NULL | | | | |<br>
| 2 | MzYyY2JhM2Q3MzEyN2U1ODJkMDA1NGNiMTMzYmE0OTc |
aa9b8bcb6effa2c7 | | | | 5060
| | 1969-12-31 19:00:00 | 1969-12-31 19:00:00
| 0 | NULL | NULL |
| 0 | 0 |
| | 200 | Sip-Session |
| | 0 | 0 | Invite
| 200 | c6ea6056 | 407f047e |
| | | |
| | | | |
| 0 | | | NULL | 0 | |
NULL | | | | |<br>
| 3 | M2IwZTVkY2Y5YTU2ZjdmOTQ5NWMzYzI1NTU3MWMwYjQ. |
117c7740de62ba6c | | | | 5060
| | 1969-12-31 19:33:34 | 1969-12-31 19:33:34
| 0 | NULL | NULL |
| 0 | 0 |
| | 200 | Sip-Session |
| | 1407897936 | 1407897980 | Invite
| 200 | d443e36e | 59eb1875 |
| | | |
| | | | |
| 0 | | | NULL | 0 | |
NULL | | | | |<br>
+-----------+----------------------------------------------+------------------+----------+-------+--------------+-----------+-------------+---------------------+---------------------+-----------------+---------------+-------------------+------------------+-----------------+------------------+-----------------+------------------+--------------------+-------------+---------+-----------------+----------------+---------------+-----------+-----------------+----------+------------+-------------------------+---------------+--------------------+-----------+---------+---------------+----------+------------+--------------+-----------+-----------+---------------+------+-------+------------+-----------+-----------+---------------+------------+-----------+---------+<br>
3 rows in set (0.00 sec)</div></div>