[Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1
Joel S. | VOZELIA
joel at vozelia.com
Wed Sep 10 17:53:49 EDT 2014
Hi Daniel,
Done:
mysql> select * from billing.contract_balances where contract_id = '60813';
+-------+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
| id | contract_id | cash_balance | cash_balance_interval | free_time_balance | free_time_balance_interval | start | end | invoice_id |
+-------+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
| 65427 | 60813 | 0 | 0 | 0 | 0 | 2014-08-01 00:00:00 | 2014-08-31 23:59:59 | NULL |
| 68213 | 60813 | 0 | 0 | 0 | 0 | 2014-09-01 00:00:00 | 2014-09-30 23:59:59 | NULL |
+-------+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
2 rows in set (0.00 sec)
mysql>
It's there.
We have an external system that manages the customers and subscribers via API.
Could it be that for some reason maybe the UUID has changed for the subscriber and the one it had the day of that CDR is not the same as today and that breaks the rating process? If that would be the case, there should be traces in oss.log right?
I don't know what else It could be.
How can I delete this CDR so I can restart rate-o-mat?
Thanks.
BR,
Joel.
----- Original Message -----
> From: "Daniel Grotti" <dgrotti at sipwise.com>
> To: "Joel S. | VOZELIA" <joel at vozelia.com>
> Cc: spce-user at lists.sipwise.com
> Sent: Tuesday, September 9, 2014 7:32:16 PM
> Subject: Re: [Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1
>
> Hi,
> Just use contract_id as billing.contracts.id.
>
> Daniel
>
> On 9 Sep 2014 19:07, "Joel S. | VOZELIA" <joel at vozelia.com> wrote:
> >
> > Hi,
> >
> > If I search for the subscriber in the Admin Panel, I see it belongs to
> > Customer #ID: 60813, then:
> >
> > mysql> select * from billing.contracts where id = '60813';
> > +-------+-------------+------------+----------+--------+-------------+---------------------+---------------------+---------------------+---------------------+-----------------+
> > | id | customer_id | contact_id | order_id | status | external_id |
> > | modify_timestamp | create_timestamp | activate_timestamp |
> > | terminate_timestamp | max_subscribers |
> > +-------+-------------+------------+----------+--------+-------------+---------------------+---------------------+---------------------+---------------------+-----------------+
> > | 60813 | NULL | 60812 | NULL | active | 61982 |
> > | 2014-08-11 00:07:13 | 2014-08-11 00:07:13 | 2014-08-11 00:07:13 | NULL
> > | | NULL |
> > +-------+-------------+------------+----------+--------+-------------+---------------------+---------------------+---------------------+---------------------+-----------------+
> > 1 row in set (0.00 sec)
> >
> > mysql>
> >
> >
> > I don't know how to search for the UUID in billing.contract_balances table:
> >
> > mysql> desc billing.contract_balances;
> > +----------------------------+------------------+------+-----+---------+----------------+
> > | Field | Type | Null | Key | Default |
> > | Extra |
> > +----------------------------+------------------+------+-----+---------+----------------+
> > | id | int(11) unsigned | NO | PRI | NULL |
> > | auto_increment |
> > | contract_id | int(11) unsigned | NO | MUL | NULL |
> > | |
> > | cash_balance | double | YES | | NULL |
> > | |
> > | cash_balance_interval | double | NO | | 0 |
> > | |
> > | free_time_balance | int(11) | YES | | NULL |
> > | |
> > | free_time_balance_interval | int(11) | NO | | 0 |
> > | |
> > | start | datetime | NO | | NULL |
> > | |
> > | end | datetime | NO | | NULL |
> > | |
> > | invoice_id | int(11) unsigned | YES | MUL | NULL |
> > | |
> > +----------------------------+------------------+------+-----+---------+----------------+
> > 9 rows in set (0.00 sec)
> >
> > mysql> select * from billing.contract_balances limit 1;
> > +----+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
> > | id | contract_id | cash_balance | cash_balance_interval |
> > | free_time_balance | free_time_balance_interval | start |
> > | end | invoice_id |
> > +----+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
> > | 1 | 2 | 0 | 0 |
> > | 0 | 0 | 2014-04-01 00:00:00 |
> > | 2014-04-30 23:59:59 | NULL |
> > +----+-------------+--------------+-----------------------+-------------------+----------------------------+---------------------+---------------------+------------+
> > 1 row in set (0.00 sec)
> >
> > mysql>
> >
> >
> > How can I find the ID for UUID 1d679354-184b-489d-af9c-d5b188023c94 so I
> > can then check billing.contract_balances table?
> >
> > How can I safely delete (or set as rated/missed/ignore/etc.) this specific
> > CDR so I can restart rate-o-mat? (This way I can continue looking for the
> > root cause of this issue with much less presure).
> >
> >
> > Thank you Daniel.
> >
> > Best regards,
> > Joel.
> >
> >
> >
> >
> >
> >
> > ----- Original Message -----
> > > From: "Daniel Grotti" <dgrotti at sipwise.com>
> > > To: spce-user at lists.sipwise.com
> > > Sent: Tuesday, September 9, 2014 4:06:43 PM
> > > Subject: Re: [Spce-user] Rate-o-mat can't rate CDRs after terminating
> > > subscriber on SPCE mr3.2.1
> > >
> > > 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
> > >
> > > _______________________________________________
> > > 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