[Spce-user] Cdr-exporter is not writting CDRs to files

Fri Sep 12 23:13:16 EDT 2014

Based upon the query, I do believe accounting.cdr would be where you want
to look.

Try:
select * from accounting.cdr limit 10;

I tried this on my SPCE test environment, and the table does have a column
called cdr.id
Let me know if this is what you're looking for.

On Fri, Sep 12, 2014 at 6:21 PM, Joel S. | VOZELIA <joel at vozelia.com> wrote:

> Hi Jeremy,
>
> This is the query that cdr-exporter runs:
>
> select cdr.id, update_time,
> source_user_id, source_provider_id,
> source_external_subscriber_id, source_bvs.id AS source_subscriber_id,
> source_external_contract_id, source_account_id,
> source_user, source_domain,
> source_cli, source_clir, source_ip,
> destination_user_id, destination_provider_id,
> destination_external_subscriber_id, destination_bvs.id AS
> destination_subscriber_id,
> destination_external_contract_id, destination_account_id,
> destination_user, destination_domain,
> destination_user_in, destination_domain_in, destination_user_dialed,
> peer_auth_user, peer_auth_realm,
> call_type, call_status,
> call_code, CONCAT(FROM_UNIXTIME(start_time), '.', SUBSTRING_INDEstart_time,
> '.', -1)) AS start_time,
> CONCAT(FROM_UNIXTIME(init_time), '.', SUBSTRING_INDEinit_time, '.', -1))
> AS init_time,
> duration, call_id,
> rating_status, rated_at,
> source_carrier_cost, source_reseller_cost, source_customer_cost,
> source_carrier_free_time, source_reseller_free_time,
> source_customer_free_time,
> source_carrier_bbz.zone AS source_carrier_zone, source_reseller_bbz.zone
> AS source_reseller_zone,
> source_customer_bbz.zone AS source_customer_zone,
> source_carrier_bbz.detail AS source_carrier_destination,
> source_reseller_bbz.detail AS source_reseller_destination,
> source_customer_bbz.detail AS source_customer_destination,
> destination_carrier_cost, destination_reseller_cost,
> destination_customer_cost,
> destination_carrier_free_time, destination_reseller_free_time,
> destination_customer_free_time,
> destination_carrier_bbz.zone AS destination_carrier_zone,
> destination_reseller_bbz.zone AS destination_reseller_zone,
> destination_customer_bbz.zone AS destination_customer_zone,
> destination_carrier_bbz.detail AS destination_carrier_destination,
> destination_reseller_bbz.detail AS destination_reseller_destination,
> destination_customer_bbz.detail AS destination_customer_destination
> from accounting.cdr
> LEFT JOIN billing.billing_zones_history source_carrier_bbz ON
> cdr.source_carrier_billing_zone_id = source_carrier_bbz.id
> LEFT JOIN billing.billing_zones_history source_reseller_bbz ON
> cdr.source_reseller_billing_zone_id = source_reseller_bbz.id
> LEFT JOIN billing.billing_zones_history source_customer_bbz ON
> cdr.source_customer_billing_zone_id = source_customer_bbz.id
> LEFT JOIN billing.billing_zones_history destination_carrier_bbz ON
> cdr.destination_carrier_billing_zone_id = destination_carrier_bbz.id
> LEFT JOIN billing.billing_zones_history destination_reseller_bbz ON
> cdr.destination_reseller_billing_zone_id = destination_reseller_bbz.id
> LEFT JOIN billing.billing_zones_history destination_customer_bbz ON
> cdr.destination_customer_billing_zone_id = destination_customer_bbz.id
> LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id =
> source_bvs.uuid
> LEFT JOIN billing.voip_subscribers destination_bvs ON
> cdr.destination_user_id = destination_bvs.uuid
> where cdr.export_status = 'unexported' AND cdr.id > ?
> ". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ."
> ". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ."
> order by
> cdr.id
> limit 5000;
>
>
>
>
> I checked the table you mentioned earlier but no clues:
>
>
> mysql> select * from billing.billing_zones_history;
> +----+-------+--------------------+-------------------+------------------+
> | id | bz_id | billing_profile_id | zone | detail |
> +----+-------+--------------------+-------------------+------------------+
> | 1 | 1 | 1 | Free Default Zone | All Destinations |
> +----+-------+--------------------+-------------------+------------------+
> 1 row in set (0.00 sec)
>
> mysql>
>
>
>
> Thank you for your help!
>
>
> Best regards,
>
> Joel.
>
> ------------------------------
>
> *From: *"Jeremy Ward" <jward01 at gmail.com>
> *To: *"Joel S. | VOZELIA" <joel at vozelia.com>
> *Cc: *"Spce-user" <spce-user at lists.sipwise.com>
> *Sent: *Friday, September 12, 2014 10:29:24 PM
> *Subject: *Re: [Spce-user] Cdr-exporter is not writting CDRs to files
>
> Joel,
>
> Can you post the entire loop-join query that you have at the end of your
> last message?
>
> Maybe I can help.
>
> Thanks.
>
> Jeremy D. Ward, CWNE
> (954) 661-4965
> On Sep 12, 2014 3:59 PM, "Joel S. | VOZELIA" <joel at vozelia.com> wrote:
>
>> Hi everybody,
>>
>> First of all, this is a followup on my previous post: [Spce-user]
>> Rate-o-mat can't rate CDRs after terminating subscriber on SPCE mr3.2.1
>>
>> I have 4000k CDRs to rate and export. To speed things up, last night I
>> tried to modify the 5000 limit set in the query cdr-exporter script uses to
>> obtain its results:
>>
>> Before:
>>
>> my $limit = 5000;
>>
>> After:
>>
>> my $limit = 10000;
>>
>>
>> Apparently it worked ok (notice the size and length of the last two
>> files):
>>
>> -rw-r--r-- 1 cdrexport cdrexport 2502925 Sep 12 00:25
>> sipwise_007_20140912002501_0000007321.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2503036 Sep 12 00:25
>> sipwise_007_20140912002501_0000007322.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 1414621 Sep 12 00:25
>> sipwise_007_20140912002501_0000007323.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2502650 Sep 12 00:55
>> sipwise_007_20140912005501_0000007324.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2503513 Sep 12 00:55
>> sipwise_007_20140912005501_0000007325.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 1651536 Sep 12 00:55
>> sipwise_007_20140912005501_0000007326.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2504604 Sep 12 01:25
>> sipwise_007_20140912012501_0000007327.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2505834 Sep 12 01:25
>> sipwise_007_20140912012501_0000007328.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2333383 Sep 12 01:25
>> sipwise_007_20140912012501_0000007329.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2505978 Sep 12 01:55
>> sipwise_007_20140912015501_0000007330.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2504757 Sep 12 01:55
>> sipwise_007_20140912015501_0000007331.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2400073 Sep 12 01:55
>> sipwise_007_20140912015501_0000007332.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2505388 Sep 12 02:25
>> sipwise_007_20140912022501_0000007333.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2504018 Sep 12 02:25
>> sipwise_007_20140912022501_0000007334.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2361539 Sep 12 02:25
>> sipwise_007_20140912022501_0000007335.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2504660 Sep 12 02:55
>> sipwise_007_20140912025501_0000007336.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2503450 Sep 12 02:55
>> sipwise_007_20140912025501_0000007337.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 2380460 Sep 12 02:55
>> sipwise_007_20140912025501_0000007338.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 5006355 Sep 12 03:25
>> sipwise_007_20140912032501_0000007339.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 1853260 Sep 12 03:25
>> sipwise_007_20140912032501_0000007340.cdr
>>
>> 5002 sipwise_007_20140912002501_0000007321.cdr
>> 5002 sipwise_007_20140912002501_0000007322.cdr
>> 2828 sipwise_007_20140912002501_0000007323.cdr
>> 5002 sipwise_007_20140912005501_0000007324.cdr
>> 5002 sipwise_007_20140912005501_0000007325.cdr
>> 3298 sipwise_007_20140912005501_0000007326.cdr
>> 5002 sipwise_007_20140912012501_0000007327.cdr
>> 5002 sipwise_007_20140912012501_0000007328.cdr
>> 4658 sipwise_007_20140912012501_0000007329.cdr
>> 5002 sipwise_007_20140912015501_0000007330.cdr
>> 5002 sipwise_007_20140912015501_0000007331.cdr
>> 4797 sipwise_007_20140912015501_0000007332.cdr
>> 5002 sipwise_007_20140912022501_0000007333.cdr
>> 5002 sipwise_007_20140912022501_0000007334.cdr
>> 4720 sipwise_007_20140912022501_0000007335.cdr
>> 5002 sipwise_007_20140912025501_0000007336.cdr
>> 5002 sipwise_007_20140912025501_0000007337.cdr
>> 4757 sipwise_007_20140912025501_0000007338.cdr
>> 10002 sipwise_007_20140912032501_0000007339.cdr
>> 3702 sipwise_007_20140912032501_0000007340.cdr
>>
>> After that one run, I set it back to 5000 and since then, every file is
>> empty: (notice the size):
>>
>> -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 03:55
>> sipwise_007_20140912035501_0000007341.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:25
>> sipwise_007_20140912042502_0000007342.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 04:55
>> sipwise_007_20140912045501_0000007343.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:25
>> sipwise_007_20140912052501_0000007344.cdr
>> -rw-r--r-- 1 cdrexport cdrexport 42 Sep 12 05:55
>> sipwise_007_20140912055501_0000007345.cdr
>>
>> 2 sipwise_007_20140912035501_0000007341.cdr
>> 2 sipwise_007_20140912042502_0000007342.cdr
>> 2 sipwise_007_20140912045501_0000007343.cdr
>> 2 sipwise_007_20140912052501_0000007344.cdr
>> 2 sipwise_007_20140912055501_0000007345.cdr
>>
>>
>> I don't know how but I've messed things up :(
>>
>>
>> I can't get cdr-exporter to actually export any CDRs, it always writes 0
>> lines:
>>
>> # cdr-exporter
>> +++ Start run with DB exporter at accounting to sipwise
>> --- Starting CDR export
>> ### 0 data lines written to
>> /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr.24010,
>> checksum is 9b8bd11538a55b017aab6b2ce9d7374f
>> ### successfully moved
>> /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr.24010
>> to
>> /home/jail/home/cdrexport/system/201409/12/sipwise_007_20140912211517_0000007382.cdr
>> ::: Updated DB marks, all done.
>>
>>
>> Database:
>>
>>
>> mysql> select count(*) from accounting.cdr where export_status =
>> "unexported";
>> +----------+
>> | count(*) |
>> +----------+
>> | 4333587  |
>> +----------+
>> 1 row in set (1.22 sec)
>>
>> mysql>
>>
>>
>>
>> mysql> select count(*) from accounting.cdr where rating_status = "ok" and
>> export_status = "unexported" ;
>> +----------+
>> | count(*) |
>> +----------+
>> | 1222721  |
>> +----------+
>> 1 row in set (2.53 sec)
>>
>>
>> mysql>
>>
>>
>> mysql> select max(id) from cdr;
>> +---------+
>> | max(id) |
>> +---------+
>> | 8939578 |
>> +---------+
>> 1 row in set (0.00 sec)
>>
>> mysql>
>>
>>
>> My last correctly exported CDR id is: 5560563
>>
>>
>>
>>
>> Since that one last correct execution (where I modified the limit from
>> 5000 to 10000), every single CDR file is empty.
>>
>>
>> I am again lost, I've tried to understand how cdr-exporter works but I
>> don't know perl.
>>
>>
>>
>> The test I thought I could start with is try run the query that
>> cdr-exporter runs, but I don't know from where It gets the cdr.id it
>> filters with:
>>
>>
>> [...]
>>
>> LEFT JOIN billing.billing_zones_history destination_customer_bbz ON
>> cdr.destination_customer_billing_zone_id = destination_customer_bbz.id
>> LEFT JOIN billing.voip_subscribers source_bvs ON cdr.source_user_id =
>> source_bvs.uuid
>> LEFT JOIN billing.voip_subscribers destination_bvs ON
>> cdr.destination_user_id = destination_bvs.uuid
>> where cdr.export_status = 'unexported' AND cdr.id > ?
>> ". ($EXPORT_INCOMING eq 'yes' ? '' : "and source_user_id != '0'") ."
>> ". ($EXPORT_FAILED eq 'yes' ? '' : "and call_status = 'ok'") ."
>> order by
>> cdr.id
>>
>> [...]
>>
>>
>>
>> How can I start troubleshooting this?
>>
>>
>>
>> Thanks in advanced.
>>
>>
>> Best regards,
>>
>> Joel.
>>
>>
>>
>>
>> _______________________________________________
>> Spce-user mailing list
>> Spce-user at lists.sipwise.com
>> https://lists.sipwise.com/listinfo/spce-user
>>
>>
>

-- 
Jeremy D. Ward
Consulting Network Engineer
(954) 661-4965
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20140912/64d0add5/attachment-0001.html>