[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 13:32:16 EDT 2014
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