[Spce-user] Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1
Joel S. | VOZELIA
joel at vozelia.com
Tue Sep 9 13:07:52 EDT 2014
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