[Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1

Daniel Grotti dgrotti at sipwise.com
Tue Sep 9 10:06:43 EDT 2014 

Hi,
looks like the problem here is that the uuid
1d679354-184b-489d-af9c-d5b188023c94 has no contract id associated.
Please check the following tables:

billing.contracts (you need entry here where id=customer id of your
subscriber)
billing.contract_balances (you need entry there for your uuid for the
current month)


Daniel



On 09/09/2014 12:43 PM, Joel S. | VOZELIA wrote:
> Hi, 
>
> I can't find Id "1d679354-184b-489d-af9c-d5b188023c94" anywhere in the logs (I've searched in the logs of the past 3 months).
>
> I've also searched in the database backup and there are references of Id "1d679354-184b-489d-af9c-d5b188023c94" in accounting.cdr and kamailio.acc_backup.
>
> In accounting.cdr backup I can see the following row:
>
> (5396702,'2014-08-10 09:31:38','0','5','','',0,'00346515433XX','10.0.99.XX','346515433XX',0,'10.0.99.XX','1d679354-184b-489d-af9c-d5b188023c94','0','NULL','NULL',60740,'9900346761268XX','sip.domain.com','99003467
> 61268XX','9900346761268XX','sip.domain.com','','','call','ok','200',1407663049.983,1407663065.410,31.590,'443fcfe7369c5c695f387bb51c366bfd at 10.0.99.XX:5050',0.00,0.00,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL
> ,NULL,NULL,0.00,0.00,0.00,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,NULL,'unrated',NULL,'unexported')
>
> (numbers, domains and IPs have been truncated for privacy matters)
>
> Since rate-o-mat says:
>
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396776
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396702
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: Missing destination_provider_id for destination_user_id 1d679354-184b-489d-af9c-d5b188023c94 in cdr #5396702
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: missing destination profile, so we can't calculate destination_reseller_cost for destination_provider_info $VAR1 = {};
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: No contract id found for uuid '1d679354-184b-489d-af9c-d5b188023c94'
> Sep  1 09:31:55 spce1 rate-o-mat[16667]: Error rating CDR batch: No contract id found for uuid '1d679354-184b-489d-af9c-d5b188023c94'
>
>
> I have looked up those parameters in db:
>
> mysql> select destination_provider_id,destination_user_id from accounting.cdr where id = 5396702;
> +-------------------------+--------------------------------------+
> | destination_provider_id | destination_user_id                  |
> +-------------------------+--------------------------------------+
> | 0                       | 1d679354-184b-489d-af9c-d5b188023c94 |
> +-------------------------+--------------------------------------+
> 1 row in set (0.00 sec)
>
> If I check a correctly rated CDR of a similar call:
>
> mysql> select destination_provider_id,destination_user_id from accounting.cdr where id = 5396589;
> +-------------------------+--------------------------------------+
> | destination_provider_id | destination_user_id                  |
> +-------------------------+--------------------------------------+
> | 1                       | d735a894-380d-4642-8fa5-7e85382bce76 |
> +-------------------------+--------------------------------------+
> 1 row in set (0.00 sec)
>
> mysql> 
>
>
> If I verify the UUID of the CDR 5396589 I see the info:
>
> mysql> select * from billing.voip_subscribers where uuid = 'd735a894-380d-4642-8fa5-7e85382bce76';
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> | id    | contract_id | uuid                                 | username        | domain_id | status | primary_number_id | external_id |
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> | 30124 |       30129 | d735a894-380d-4642-8fa5-7e85382bce76 | 9900346220987XX |         2 | active |              NULL | 30312       |
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> 1 row in set (0.00 sec)
>
>
> If I do the same with the UUID from CDR 5396702 I find nothing:
>
> mysql> select * from billing.voip_subscribers where uuid = '1d679354-184b-489d-af9c-d5b188023c94';
> Empty set (0.00 sec)
>
> If I search using the destination It has a different UUID (strange):
>
> mysql> select * from billing.voip_subscribers where username = '9900346761268XX';
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> | id    | contract_id | uuid                                 | username        | domain_id | status | primary_number_id | external_id |
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> | 62359 |       60813 | f701ed09-7fd8-47a7-8ba2-6b10761c11ba | 9900346761268XX |         2 | active |              NULL | 61982       |
> +-------+-------------+--------------------------------------+-----------------+-----------+--------+-------------------+-------------+
> 1 row in set (0.00 sec)
>
>
> I remember there was some place (not sure if it was in CSC or where) where you can set a CDR as rated, etc. Any clues?
>
> If not, how can I safely delete this CDR so I can restart rate-o-mat?  (Maybe running a delete query directly on accounting.cdr table?)
>
>
> Thanks again.
>
> Best regards, 
> Joel.
>
>
> ----- Original Message -----
>> From: "Gavin Sweet" <gavin.sweet at skyracktelecom.com>
>> To: "Joel S. | VOZELIA" <joel at vozelia.com>, "Spce-user" <spce-user at lists.sipwise.com>
>> Sent: Friday, September 5, 2014 12:25:33 PM
>> Subject: RE: [Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1
>>
>> Hi Joel -
>> Was that the Id of the user, does his record still exist in the billing db
>> subs table?
>>
>> If its only a single CDR holding things up, just delete it so that you can
>> re-start rate-o-mat
>>
>> Cheers
>> Gavin
>>
>>
>>
>>
>>> -----Original Message-----
>>> From: Spce-user [mailto:spce-user-bounces at lists.sipwise.com] On Behalf
>>> Of Joel S. | VOZELIA
>>> Sent: 05 September 2014 10:46
>>> To: Spce-user
>>> Subject: Re: [Spce-user] Rate-o-mat can't rate CDRs after terminating
>>> subscriber on SPCE mr3.2.1
>>>
>>> Hi,
>>>
>>> Can anyone tell me where I can start troubleshooting rate-o-mat issues?
>>>
>>> For some strange reason after terminating a subscriber, rating CDRs has
>>> stopped because Id "1d679354-184b-489d-af9c-d5b188023c94" is not found.
>>>
>>> Any help is well appreciated, at the moment we have our billing system
>>> stopped :-(
>>>
>>>
>>> Best regards,
>>> Joel.
>>>
>>>
>>> ----- Mensaje original -----
>>>> De: "Joel S. | VOZELIA" <joel at vozelia.com>
>>>> Para: "Spce-user" <spce-user at lists.sipwise.com>
>>>> Enviados: Martes, 2 de Septiembre 2014 9:41:07
>>>> Asunto: Re: Rate-o-mat can't rate CDRs after terminating subscriber
>>> on SPCE mr3.2.1
>>>> Hi,
>>>>
>>>> Has anyone had a similar problem?
>>>>
>>>> BR,
>>>> Joel.
>>>>
>>>> ----- Original Message -----
>>>>> From: "Joel S. | VOZELIA" <joel at vozelia.com>
>>>>> To: "Spce-user" <spce-user at lists.sipwise.com>
>>>>> Sent: Monday, September 1, 2014 10:18:20 AM
>>>>> Subject: Rate-o-mat can't rate CDRs after terminating subscriber on
>>> SPCE
>>>>> mr3.2.1
>>>>>
>>>>> Hi,
>>>>>
>>>>> We are experiencing problems to rate CDRs after terminating a
>>> subscriber.
>>>>> Our NGCP version:
>>>>>
>>>>> root at za-spce1-eu:/var/log/ngcp# cat /etc/ngcp_version
>>>>> mr3.2.1
>>>>> root at za-spce1-eu:/var/log/ngcp#
>>>>>
>>>>>
>>>>> We noticed that rate-o-mat was stopped, and after starting it we
>>> see the
>>>>> following in the logs:
>>>>>
>>>>> root at za-spce1-eu:/var/log/ngcp# cat rate-o-mat.log
>>>>> Sep  1 09:31:45 spce1 rate-o-mat[16667]: Trying to connect to
>>> billing db...
>>>>> Sep  1 09:31:45 spce1 rate-o-mat[16667]: Successfully connected to
>>> billing
>>>>> db...
>>>>> Sep  1 09:31:45 spce1 rate-o-mat[16667]: Trying to connect to
>>> accounting
>>>>> db...
>>>>> Sep  1 09:31:45 spce1 rate-o-mat[16667]: Successfully connected to
>>>>> accounting
>>>>> db...
>>>>> Sep  1 09:31:45 spce1 rate-o-mat[16667]: Up and running.
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396691
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396716
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396690
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396724
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396695
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396701
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396718
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396725
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396733
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396779
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396698
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396710
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396723
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396732
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396692
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396770
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396713
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396762
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396711
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396726
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396764
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396709
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: missing destination
>>> profile, so we
>>>>> can't calculate destination_reseller_cost for
>>> destination_provider_info
>>>>> $VAR1 = {#012          'contract_id' => '1',#012
>>>  'profile_id' =>
>>>>> undef,#012          'class' => 'reseller'#012        };
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396759
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396816
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396717
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396712
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396765
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396696
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396754
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396776
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: rate cdr #5396702
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: Missing
>>> destination_provider_id
>>>>> for
>>>>> destination_user_id 1d679354-184b-489d-af9c-d5b188023c94 in cdr
>>> #5396702
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: missing destination
>>> profile, so we
>>>>> can't calculate destination_reseller_cost for
>>> destination_provider_info
>>>>> $VAR1 = {};
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: No contract id found for
>>> uuid
>>>>> '1d679354-184b-489d-af9c-d5b188023c94'
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: Error rating CDR batch: No
>>>>> contract
>>>>> id found for uuid '1d679354-184b-489d-af9c-d5b188023c94'
>>>>> root at za-spce1-eu:/var/log/ngcp#
>>>>>
>>>>>
>>>>> Regarding the UUID not found, why is it failing if we did a normal
>>>>> "terminate" subscriber through the admin panel?
>>>>>
>>>>>
>>>>> Also, what does this error mean? (or is it just a warning?)
>>>>>
>>>>> Sep  1 09:31:55 spce1 rate-o-mat[16667]: missing destination
>>> profile, so we
>>>>> can't calculate destination_reseller_cost for
>>> destination_provider_info
>>>>> $VAR1 = {#012          'contract_id' => '1',#012
>>>  'profile_id' =>
>>>>> undef,#012          'class' => 'reseller'#012        };
>>>>>
>>>>>
>>>>> Is there a quick way to get over this?
>>>>>
>>>>>
>>>>> Thanks in advanced.
>>>>>
>>>>>
>>>>> Best regards,
>>>>> Joel.
>>>>>
>>>>>
>>> _______________________________________________
>>> Spce-user mailing list
>>> Spce-user at lists.sipwise.com
>>> https://lists.sipwise.com/listinfo/spce-user
>>>
>>> -----
>>> No virus found in this message.
>>> Checked by AVG - www.avg.com
>>> Version: 2014.0.4745 / Virus Database: 4007/8102 - Release Date:
>>> 08/26/14
>>> Internal Virus Database is out of date.
>>
> _______________________________________________
> Spce-user mailing list
> Spce-user at lists.sipwise.com
> https://lists.sipwise.com/listinfo/spce-user

More information about the Spce-user mailing list