select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00'
and
(ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '')
MediaInfo (after update from mediaproxy) is NOT null... the rest of the fields are ok.
Look again at the SQL query of above:
MediaInfo is NULL or MediaInfo != ''
It's not needed that MediaInfo is NULL, it can be any value except NULL or empty string.
That's it, it IS an empry string, so it will never rate it
El Tuesday 17 June 2008 13:38:31 David Villasmil escribió:
select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00'
and
(ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '')
MediaInfo (after update from mediaproxy) is NOT null... the rest of the fields are ok.
Look again at the SQL query of above:
MediaInfo is NULL or MediaInfo != ''
It's not needed that MediaInfo is NULL, it can be any value except NULL or empty string.
That's it, it IS an empry string, so it will never rate it
But you are above SQL query is a SELECT, so? Forget it, the important queries are those for START, UPDATE and STOP action of FreeRadius. These SQL's are INSERT or UPDATE.
Looking at mysql's log, this is what I see:
---> BY OPENSER_:
24 Query INSERT INTO radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, SipRPID, SourceIP, SourcePort, CanonicalURI) VALUES (NULL, 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.', 'd986b295f8a55559', '', SUBSTRING_INDEX('', '@',-1), '1.2.3.4', '5060', '2008-06-17 08:27:52', '0', '0', '0', '0', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:0013058883456@1.2.3.4')), trim(leading 'sip:' from trim(leading 'sips:' from 'sip:915568911@1.2.3.4')), '200', 'Sip-Session', '', '', '0', '0', '200', 'Invite', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:13058883456@5.6.7.8:5060=3Btransport=3Dudp')), '17062708081947605004610377', '34794a17', '', '9.8.7.6', '27374', trim(leading 'sip:' from trim(leading 'sips:' from '')) )
-------------------------------------------------------------------------------------------------------------------------------------------------
---> THE CALL DROPS, THE BY MEDIAPROXY:
29 Connect openser@localhost on radius 29 Query SET NAMES latin1 29 Query UPDATE radacct SET AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, 53, 0), AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), AcctInputOctets = AcctInputOctets + 77488, AcctOutputOctets = AcctOutputOctets + 152760, SipUserAgents = 'eyeBeam release 1014c stamp 46090 + unknown-agent', SipCodecs = 'G729', SipApplicationType = 'Audio', MediaInfo = '', <------------------ LOOK AT THIS! ITS AN EMPTY STRING!! Normalized = '0' WHERE AcctSessionId = 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.' AND SipFromTag = '34794a17' AND SipToTag = '17062708081947605004610377'
-------------------------------------------------------------------------------------------------------------------------------------------------
---> THEN BY CDRTOOL, GETTING CDRs TO RATE:
select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '') <------------- CDRTOOL DOESN'T WANT AN EMPTY STRING!!
-------------------------------------------------------------------------------------------------------------------------------------------------
this of course will never work, I wonder whether it is a configuration problem or a cdrtool/mediaproxy bug...
Any thoughts?
David
----- Original Message ----- From: David Villasmil To: openser users Sent: Tuesday, June 17, 2008 1:38 PM Subject: Re: [OpenSER-Users] OPenser+mediaproxy
> select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where > (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00' and > (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '') > > > MediaInfo (after update from mediaproxy) is NOT null... the rest of the > fields are ok.
Look again at the SQL query of above:
MediaInfo is NULL or MediaInfo != ''
It's not needed that MediaInfo is NULL, it can be any value except NULL or empty string.
That's it, it IS an empry string, so it will never rate it
El Tuesday 17 June 2008 14:42:29 David Villasmil escribió:
Looking at mysql's log, this is what I see:
---> BY OPENSER_:
24 Query INSERT INTO radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, SipRPID, SourceIP, SourcePort, CanonicalURI) VALUES (NULL, 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.', 'd986b295f8a55559', '', SUBSTRING_INDEX('', '@',-1), '1.2.3.4', '5060', '2008-06-17 08:27:52', '0', '0', '0', '0', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:0013058883456@1.2.3.4')), trim(leading 'sip:' from trim(leading 'sips:' from 'sip:915568911@1.2.3.4')), '200', 'Sip-Session', '', '', '0', '0', '200', 'Invite', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:13058883456@5.6.7.8:5060=3Btransport=3Dudp')), '17062708081947605004610377', '34794a17', '', '9.8.7.6', '27374', trim(leading 'sip:' from trim(leading 'sips:' from '')) )
---> THE CALL DROPS, THE BY MEDIAPROXY:
29 Connect openser@localhost on radius 29 Query SET NAMES latin1 29 Query UPDATE radacct SET AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, 53, 0), AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), AcctInputOctets = AcctInputOctets + 77488, AcctOutputOctets = AcctOutputOctets + 152760, SipUserAgents = 'eyeBeam release 1014c stamp 46090 + unknown-agent', SipCodecs = 'G729', SipApplicationType = 'Audio', MediaInfo = '', <------------------ LOOK AT THIS! ITS AN EMPTY STRING!! Normalized = '0' WHERE AcctSessionId = 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.' AND SipFromTag = '34794a17' AND SipToTag = '17062708081947605004610377'
---> THEN BY CDRTOOL, GETTING CDRs TO RATE:
select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '') <------------- CDRTOOL DOESN'T WANT AN EMPTY STRING!!
this of course will never work, I wonder whether it is a configuration problem or a cdrtool/mediaproxy bug...
Any thoughts?
David
----- Original Message ----- From: David Villasmil To: openser users Sent: Tuesday, June 17, 2008 1:38 PM Subject: Re: [OpenSER-Users] OPenser+mediaproxy
> select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct > where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 > 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL > or MediaInfo != '') > > > MediaInfo (after update from mediaproxy) is NOT null... the rest of > the fields are ok. Look again at the SQL query of above: MediaInfo is NULL or MediaInfo != '' It's not needed that MediaInfo is NULL, it can be any value exceptNULL or empty string.
That's it, it IS an empry string, so it will never rate it
The UPDATE query MUST be:
WHERE \ AcctSessionId = '%{Acct-Session-Id}' \ AND SipToTag = '%{Sip-To-Tag}' AND SipFromTag = '%{Sip-From-Tag}' \ AND (MediaInfo IS NULL OR MediaInfo != 'timeout')"
Look at the MediaProxy changelog, there are two updates in this issue.
I'm sorry to bother you some more... but where can I find the changelog? the only change log I found is /mediaproxy/debian/changelog and there's nothing referring to that there.
On Tue, Jun 17, 2008 at 3:39 PM, Iñaki Baz Castillo ibc@in.ilimit.es wrote:
El Tuesday 17 June 2008 14:42:29 David Villasmil escribió:
Looking at mysql's log, this is what I see:
---> BY OPENSER_:
24 Query INSERT INTO radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, SipRPID, SourceIP, SourcePort, CanonicalURI) VALUES (NULL, 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.', 'd986b295f8a55559', '', SUBSTRING_INDEX('', '@',-1), '1.2.3.4', '5060',
'2008-06-17
08:27:52', '0', '0', '0', '0', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:0013058883456@1.2.3.4sip%3A0013058883456@1.2.3.4
')),
trim(leading 'sip:' from trim(leading 'sips:' from 'sip:915568911@1.2.3.4 sip%3A915568911@1.2.3.4')),
'200', 'Sip-Session', '', '',
'0', '0', '200', 'Invite', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:13058883456@5.6.7.8:5060=3Btransport=3Dudp')), '17062708081947605004610377', '34794a17', '', '9.8.7.6', '27374', trim(leading 'sip:' from trim(leading 'sips:'
from
'')) )
---> THE CALL DROPS, THE BY MEDIAPROXY:
29 Connect openser@localhost on radius 29 Query SET NAMES latin1 29 Query UPDATE radacct SET AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, 53, 0), AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), AcctInputOctets = AcctInputOctets + 77488, AcctOutputOctets = AcctOutputOctets + 152760, SipUserAgents = 'eyeBeam release 1014c stamp 46090 + unknown-agent', SipCodecs = 'G729', SipApplicationType = 'Audio', MediaInfo = '', <------------------ LOOK AT THIS! ITS AN EMPTY STRING!! Normalized = '0' WHERE AcctSessionId =
'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.'
AND SipFromTag = '34794a17' AND SipToTag = '17062708081947605004610377'
---> THEN BY CDRTOOL, GETTING CDRs TO RATE:
select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo !=
'')
<------------- CDRTOOL DOESN'T WANT AN EMPTY STRING!!
this of course will never work, I wonder whether it is a configuration problem or a cdrtool/mediaproxy bug...
Any thoughts?
David
----- Original Message ----- From: David Villasmil To: openser users Sent: Tuesday, June 17, 2008 1:38 PM Subject: Re: [OpenSER-Users] OPenser+mediaproxy
> select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct > where (1=1) and Normalized = '0' and AcctStopTime !='0000-00-00
> 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL > or MediaInfo != '') > > > MediaInfo (after update from mediaproxy) is NOT null... the restof
> the fields are ok. Look again at the SQL query of above: MediaInfo is NULL or MediaInfo != '' It's not needed that MediaInfo is NULL, it can be any value exceptNULL or empty string.
That's it, it IS an empry string, so it will never rate it
The UPDATE query MUST be:
WHERE \ AcctSessionId = '%{Acct-Session-Id}' \ AND SipToTag = '%{Sip-To-Tag}' AND SipFromTag = '%{Sip-From-Tag}' \ AND (MediaInfo IS NULL OR MediaInfo != 'timeout')"
Look at the MediaProxy changelog, there are two updates in this issue.
-- Iñaki Baz Castillo ibc@in.ilimit.es
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
Iñaki,
That did the trick, thought i'd love to read the changelog, I can't find it anywhere.
thanks a lot !
On Tue, Jun 17, 2008 at 6:08 PM, David Villasmil < david.villasmil.work@gmail.com> wrote:
I'm sorry to bother you some more... but where can I find the changelog? the only change log I found is /mediaproxy/debian/changelog and there's nothing referring to that there.
On Tue, Jun 17, 2008 at 3:39 PM, Iñaki Baz Castillo ibc@in.ilimit.es wrote:
El Tuesday 17 June 2008 14:42:29 David Villasmil escribió:
Looking at mysql's log, this is what I see:
---> BY OPENSER_:
24 Query INSERT INTO radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, AcctStartTime, AcctStopTime, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, ENUMtld, FramedIPAddress, AcctStartDelay, AcctStopDelay, SipResponseCode, SipMethod, SipTranslatedRequestURI, SipToTag, SipFromTag, SipRPID, SourceIP, SourcePort, CanonicalURI) VALUES (NULL, 'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.', 'd986b295f8a55559', '', SUBSTRING_INDEX('', '@',-1), '1.2.3.4', '5060',
'2008-06-17
08:27:52', '0', '0', '0', '0', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:0013058883456@1.2.3.4sip%3A0013058883456@1.2.3.4
')),
trim(leading 'sip:' from trim(leading 'sips:' from 'sip:915568911@1.2.3.4 sip%3A915568911@1.2.3.4')),
'200', 'Sip-Session', '', '',
'0', '0', '200', 'Invite', trim(leading 'sip:' from trim(leading 'sips:' from 'sip:13058883456@5.6.7.8:5060=3Btransport=3Dudp')), '17062708081947605004610377', '34794a17', '', '9.8.7.6
',
'27374', trim(leading 'sip:' from trim(leading 'sips:'
from
'')) )
---> THE CALL DROPS, THE BY MEDIAPROXY:
29 Connect openser@localhost on radius 29 Query SET NAMES latin1 29 Query UPDATE radacct SET AcctSessionTime = AcctSessionTime + IF(ConnectInfo_stop IS NULL, 53, 0), AcctStopTime = DATE_ADD(AcctStartTime, INTERVAL AcctSessionTime SECOND), AcctInputOctets = AcctInputOctets + 77488, AcctOutputOctets = AcctOutputOctets + 152760, SipUserAgents = 'eyeBeam release 1014c stamp 46090 + unknown-agent', SipCodecs = 'G729', SipApplicationType = 'Audio', MediaInfo = '', <------------------ LOOK AT THIS! ITS AN EMPTY STRING!! Normalized = '0' WHERE AcctSessionId =
'OWNhNDAwMmIyOTM5NDU3MjQ4MGJjMTk2YWIwZmE0NGE.'
AND SipFromTag = '34794a17' AND SipToTag = '17062708081947605004610377'
---> THEN BY CDRTOOL, GETTING CDRs TO RATE:
select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp from radacct where (1=1) and Normalized = '0' and AcctStopTime != '0000-00-00 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo !=
'')
<------------- CDRTOOL DOESN'T WANT AN EMPTY STRING!!
this of course will never work, I wonder whether it is a configuration problem or a cdrtool/mediaproxy bug...
Any thoughts?
David
----- Original Message ----- From: David Villasmil To: openser users Sent: Tuesday, June 17, 2008 1:38 PM Subject: Re: [OpenSER-Users] OPenser+mediaproxy
> select *, UNIX_TIMESTAMP(AcctStartTime) as timestamp fromradacct
> where (1=1) and Normalized = '0' and AcctStopTime !='0000-00-00
> 00:00:00' and (ConnectInfo_stop is not NULL or MediaInfo isNULL
> or MediaInfo != '') > > > MediaInfo (after update from mediaproxy) is NOT null... the restof
> the fields are ok. Look again at the SQL query of above: MediaInfo is NULL or MediaInfo != '' It's not needed that MediaInfo is NULL, it can be any value exceptNULL or empty string.
That's it, it IS an empry string, so it will never rate it
The UPDATE query MUST be:
WHERE \ AcctSessionId = '%{Acct-Session-Id}' \ AND SipToTag = '%{Sip-To-Tag}' AND SipFromTag = '%{Sip-From-Tag}' \ AND (MediaInfo IS NULL OR MediaInfo != 'timeout')"
Look at the MediaProxy changelog, there are two updates in this issue.
-- Iñaki Baz Castillo ibc@in.ilimit.es
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
El Tuesday 17 June 2008 18:08:23 David Villasmil escribió:
I'm sorry to bother you some more... but where can I find the changelog? the only change log I found is /mediaproxy/debian/changelog and there's nothing referring to that there.
http://download.ag-projects.com/CDRTool/
Also take a look to the last sql.conf version: http://download.ag-projects.com/CDRTool/freeradius/OpenSER/sql.conf
cdrtool (6.2.1) unstable; urgency=low
* Correct update of radius records when mediaproxy field is NULL or !timeout You must reload the sql stored procedures from setup/radius/OpenSER/radius_accounting.proc or update Freeradius with setup/radius/OpenSER/sql.conf when not using the stored procedures * Log add balance to prepaid_history table
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 19:32:04 +0100
cdrtool (6.2.0) unstable; urgency=high
* Fixed corrupted phone_images.php file
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 14:08:18 +0100
cdrtool (6.1.9) unstable; urgency=low
* Fixed memory leak related to calling the same soap function several times * Added separate access numbers for FUNV, FBUS, FNOL, FNOA * Fixed load of customer properties in ngnpro client * Support multiple clusters for mysql replication monitor * Fixed vulnerability related to missing BYE and MediaProxy radius Update action (reported by Inaki Baz Castil) You must reload the sql stored procedures from setup/radius/OpenSER/radius_accounting.proc or update Freeradius with setup/radius/OpenSER/sql.conf when not using the stored procedures * Specify if output of soap functions is html formatted or not
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 03:34:45 +0100
That didn't do it for me.... actually I did see that changelog.. sorry
Anyway, I replaced
/etc/freeradius/sql.sql with /var/www/CDRTool/setup/radius/OpenSER/sql.conf
restarted everything and still it didn't rate the call...
I had to change /var/www/CDRTool/cdrlib.phtml at line 789, I changed:
$this->whereUnnormalized .= " and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != '') ";
to
$this->whereUnnormalized .= " and (ConnectInfo_stop is not NULL or MediaInfo is NULL or MediaInfo != 'timeout') ";
restarted cdrtool, and the it started rating calls ended by rtp timeout
Thanks!
On Tue, Jun 17, 2008 at 6:31 PM, Iñaki Baz Castillo ibc@in.ilimit.es wrote:
El Tuesday 17 June 2008 18:08:23 David Villasmil escribió:
I'm sorry to bother you some more... but where can I find the changelog? the only change log I found is /mediaproxy/debian/changelog and there's nothing referring to that there.
http://download.ag-projects.com/CDRTool/
Also take a look to the last sql.conf version: http://download.ag-projects.com/CDRTool/freeradius/OpenSER/sql.conf
cdrtool (6.2.1) unstable; urgency=low
- Correct update of radius records when mediaproxy field is NULL or
!timeout You must reload the sql stored procedures from setup/radius/OpenSER/radius_accounting.proc or update Freeradius with setup/radius/OpenSER/sql.conf when not using the stored procedures
- Log add balance to prepaid_history table
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 19:32:04 +0100
cdrtool (6.2.0) unstable; urgency=high
- Fixed corrupted phone_images.php file
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 14:08:18 +0100
cdrtool (6.1.9) unstable; urgency=low
- Fixed memory leak related to calling the same soap function several
times
- Added separate access numbers for FUNV, FBUS, FNOL, FNOA
- Fixed load of customer properties in ngnpro client
- Support multiple clusters for mysql replication monitor
- Fixed vulnerability related to missing BYE and MediaProxy
radius Update action (reported by Inaki Baz Castil) You must reload the sql stored procedures from setup/radius/OpenSER/radius_accounting.proc or update Freeradius with setup/radius/OpenSER/sql.conf when not using the stored procedures
- Specify if output of soap functions is html formatted or not
-- Adrian Georgescu ag@ag-projects.com Tue, 12 Feb 2008 03:34:45 +0100
-- Iñaki Baz Castillo ibc@in.ilimit.es
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
-
David Villasmil -
Iñaki Baz Castillo