[Spce-user] [EXTERNAL] CDR Export not working after update to mr12.3.1

[rkrenn at sipwise.com]

Hi,

 

you now can specify a list of reseller names you want to export.

 

We will provide a hotfix to export all if no reseller is provided (to match
the old behaviour).

 

br

 

Von: Alexander Schwarz <schwarz at flow7.de> 
Gesendet: Montag, 22. April 2024 11:22
An: spce-user at lists.sipwise.com
Betreff: [EXTERNAL] [Spce-user] CDR Export not working after update to
mr12.3.1

 

Hello,

 

we did an update from version mr12.2.1 to version mr12.3.1.

Now cdr export is not working:

 

As far as we could narrow down the problem:

*	If there is no cdr to export, file is generated in folder
/home/jail/homes/resellers/%Reseller-Name%/202404/22/sipwise.cdr:

 

Content: 

007,0000

9b8bd11538a55b017aab6b2ce9d7374f

 

*	If there are data to export file is not generated, inside database
the record is marked as exported
*	SQL Statement is cut off inside the logs or is this the problem?

left outer join billing.voi ( at peast "p" is missing at the end )

 

So our questions:

*	Are there any known issues regarding to cdr export in mr12.3.1?
*	How to fix this?

 

Best regards,

 

Alex

 

 

Content /ngcp-data/logs/cdr-exporter.log:

Apr 22 09:00:01 spce (debug) ngcp-event-exporter[252347]: +++ Start event
export with DB exporter@ to /home/jail/home/cdrexport

Apr 22 09:00:02 spce (debug) ngcp-event-exporter[252347]: select
base_table.id, base_table.subscriber_id, base_table.reseller_id,
base_table.type, base_table.old_status, base_table.new_status,
base_table.id, base_table.type, billing.contracts.external_id,
billing.contacts.company, billing.voip_subscribers.external_id,
CONCAT(IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER
JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE
data.event_id = base_table.id AND
dict.type="primary_number_cc"),""),IFNULL((SELECT data.val FROM
accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict
ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND
dict.type="primary_number_ac"),""),IFNULL((SELECT data.val FROM
accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict
ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND
dict.type="primary_number_sn"),"")), old_profile.name, new_profile.name,
from_unixtime(base_table.timestamp), base_table.reseller_id from
accounting.events base_table left outer join billing.voip_subscribers on
billing.voip_subscribers.id = base_table.subscriber_id left outer join
billing.contracts on billing.contracts.id =
billing.voip_subscribers.contract_id left outer join billing.contacts on
billing.contacts.id = billing.contracts.contact_id left outer join
provisioning.voip_subscribers on provisioning.voip_subscribers.uuid =
billing.voip_subscribers.uuid left outer join
provisioning.voip_subscriber_profiles old_profile on old_profile.id =
base_table.old_status left outer join provisioning.voip_subscriber_profiles
new_profile on new_profile.id = base_table.new_status where
base_table.export_status = "unexported" order by base_table.id limit 3000

Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Started execution

Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Finished processing
records

Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Finalizing output
files

Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: Committing changes
to database

Apr 22 09:00:02 spce (info) ngcp-event-exporter[252347]: All done

Apr 22 09:25:02 spce (debug) ngcp-cdr-exporter[254207]: +++ Start cdr export
stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport

Apr 22 09:25:02 spce (debug) ngcp-cdr-exporter[254207]: select
base_table.id, base_table.source_user_id, base_table.destination_user_id,
base_table.source_provider_id, base_table.destination_provider_id,
base_table.id, base_table.update_time, base_table.source_user_id,
base_table.source_provider_id, base_table.source_external_subscriber_id,
source_voip_subscribers.id, base_table.source_external_contract_id,
base_table.source_account_id, base_table.source_user,
base_table.source_domain, base_table.source_cli, base_table.source_clir,
base_table.source_ip, base_table.destination_user_id,
base_table.destination_provider_id,
base_table.destination_external_subscriber_id,
destination_voip_subscribers.id,
base_table.destination_external_contract_id,
base_table.destination_account_id, base_table.destination_user,
base_table.destination_domain, base_table.destination_user_in,
base_table.destination_domain_in, base_table.destination_user_dialed,
base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type,
base_table.call_status, base_table.call_code,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".",
SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".",
SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time,
base_table.duration, base_table.call_id, base_table.rating_status,
base_table.rated_at, base_table.source_carrier_cost,
base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"),
source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform
internal"), source_customer_bbz.detail, base_table.source_carrier_free_time,
base_table.source_customer_free_time, base_table.destination_carrier_cost,
base_table.destination_customer_cost, destination_carrier_bbz.zone,
destination_customer_bbz.zone, destination_carrier_bbz.detail,
destination_customer_bbz.detail, base_table.destination_carrier_free_time,
base_table.destination_customer_free_time, base_table.source_reseller_cost,
source_reseller_bbz.zone, source_reseller_bbz.detail,
base_table.source_reseller_free_time, base_table.destination_reseller_cost,
destination_reseller_bbz.zone, destination_reseller_bbz.detail,
base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"source" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count,
COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN
accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN
accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN
accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id =
base_table.id AND dir.type = "source" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as
destination_concurrent_calls_count, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
destination_concurrent_calls_count_customer from accounting.cdr base_table
left outer join billing.voi

Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Started execution

Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Finished processing
records

Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Finalizing output
files

Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: Committing changes to
database

Apr 22 09:25:02 spce (info) ngcp-cdr-exporter[254207]: All done

Apr 22 09:55:01 spce (debug) ngcp-cdr-exporter[256398]: +++ Start cdr export
stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport

Apr 22 09:55:01 spce (debug) ngcp-cdr-exporter[256398]: select
base_table.id, base_table.source_user_id, base_table.destination_user_id,
base_table.source_provider_id, base_table.destination_provider_id,
base_table.id, base_table.update_time, base_table.source_user_id,
base_table.source_provider_id, base_table.source_external_subscriber_id,
source_voip_subscribers.id, base_table.source_external_contract_id,
base_table.source_account_id, base_table.source_user,
base_table.source_domain, base_table.source_cli, base_table.source_clir,
base_table.source_ip, base_table.destination_user_id,
base_table.destination_provider_id,
base_table.destination_external_subscriber_id,
destination_voip_subscribers.id,
base_table.destination_external_contract_id,
base_table.destination_account_id, base_table.destination_user,
base_table.destination_domain, base_table.destination_user_in,
base_table.destination_domain_in, base_table.destination_user_dialed,
base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type,
base_table.call_status, base_table.call_code,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".",
SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".",
SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time,
base_table.duration, base_table.call_id, base_table.rating_status,
base_table.rated_at, base_table.source_carrier_cost,
base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"),
source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform
internal"), source_customer_bbz.detail, base_table.source_carrier_free_time,
base_table.source_customer_free_time, base_table.destination_carrier_cost,
base_table.destination_customer_cost, destination_carrier_bbz.zone,
destination_customer_bbz.zone, destination_carrier_bbz.detail,
destination_customer_bbz.detail, base_table.destination_carrier_free_time,
base_table.destination_customer_free_time, base_table.source_reseller_cost,
source_reseller_bbz.zone, source_reseller_bbz.detail,
base_table.source_reseller_free_time, base_table.destination_reseller_cost,
destination_reseller_bbz.zone, destination_reseller_bbz.detail,
base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"source" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count,
COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN
accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN
accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN
accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id =
base_table.id AND dir.type = "source" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as
destination_concurrent_calls_count, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
destination_concurrent_calls_count_customer from accounting.cdr base_table
left outer join billing.voi

Apr 22 09:55:01 spce (info) ngcp-cdr-exporter[256398]: Started execution

Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Finished processing
records

Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Finalizing output
files

Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: Committing changes to
database

Apr 22 09:55:02 spce (info) ngcp-cdr-exporter[256398]: All done

Apr 22 10:00:01 spce (debug) ngcp-event-exporter[256759]: +++ Start event
export with DB exporter@ to /home/jail/home/cdrexport

Apr 22 10:00:01 spce (debug) ngcp-event-exporter[256759]: select
base_table.id, base_table.subscriber_id, base_table.reseller_id,
base_table.type, base_table.old_status, base_table.new_status,
base_table.id, base_table.type, billing.contracts.external_id,
billing.contacts.company, billing.voip_subscribers.external_id,
CONCAT(IFNULL((SELECT data.val FROM accounting.events_tag_data AS data INNER
JOIN accounting.events_tag AS dict ON data.tag_id = dict.id WHERE
data.event_id = base_table.id AND
dict.type="primary_number_cc"),""),IFNULL((SELECT data.val FROM
accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict
ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND
dict.type="primary_number_ac"),""),IFNULL((SELECT data.val FROM
accounting.events_tag_data AS data INNER JOIN accounting.events_tag AS dict
ON data.tag_id = dict.id WHERE data.event_id = base_table.id AND
dict.type="primary_number_sn"),"")), old_profile.name, new_profile.name,
from_unixtime(base_table.timestamp), base_table.reseller_id from
accounting.events base_table left outer join billing.voip_subscribers on
billing.voip_subscribers.id = base_table.subscriber_id left outer join
billing.contracts on billing.contracts.id =
billing.voip_subscribers.contract_id left outer join billing.contacts on
billing.contacts.id = billing.contracts.contact_id left outer join
provisioning.voip_subscribers on provisioning.voip_subscribers.uuid =
billing.voip_subscribers.uuid left outer join
provisioning.voip_subscriber_profiles old_profile on old_profile.id =
base_table.old_status left outer join provisioning.voip_subscriber_profiles
new_profile on new_profile.id = base_table.new_status where
base_table.export_status = "unexported" order by base_table.id limit 3000

Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Started execution

Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Finished processing
records

Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Finalizing output
files

Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: Committing changes
to database

Apr 22 10:00:01 spce (info) ngcp-event-exporter[256759]: All done

Apr 22 10:25:01 spce (debug) ngcp-cdr-exporter[259917]: +++ Start cdr export
stream 'default' with DB exporter at accounting to /home/jail/home/cdrexport

Apr 22 10:25:01 spce (debug) ngcp-cdr-exporter[259917]: select
base_table.id, base_table.source_user_id, base_table.destination_user_id,
base_table.source_provider_id, base_table.destination_provider_id,
base_table.id, base_table.update_time, base_table.source_user_id,
base_table.source_provider_id, base_table.source_external_subscriber_id,
source_voip_subscribers.id, base_table.source_external_contract_id,
base_table.source_account_id, base_table.source_user,
base_table.source_domain, base_table.source_cli, base_table.source_clir,
base_table.source_ip, base_table.destination_user_id,
base_table.destination_provider_id,
base_table.destination_external_subscriber_id,
destination_voip_subscribers.id,
base_table.destination_external_contract_id,
base_table.destination_account_id, base_table.destination_user,
base_table.destination_domain, base_table.destination_user_in,
base_table.destination_domain_in, base_table.destination_user_dialed,
base_table.peer_auth_user, base_table.peer_auth_realm, base_table.call_type,
base_table.call_status, base_table.call_code,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.init_time)), ".",
SUBSTRING_INDEX(base_table.init_time, ".", -1)) as cdr_init_time,
CONCAT(FROM_UNIXTIME(FLOOR(base_table.start_time)), ".",
SUBSTRING_INDEX(base_table.start_time, ".", -1)) as cdr_start_time,
base_table.duration, base_table.call_id, base_table.rating_status,
base_table.rated_at, base_table.source_carrier_cost,
base_table.source_customer_cost, COALESCE(source_carrier_bbz.zone, "onnet"),
source_customer_bbz.zone, COALESCE(source_carrier_bbz.detail, "platform
internal"), source_customer_bbz.detail, base_table.source_carrier_free_time,
base_table.source_customer_free_time, base_table.destination_carrier_cost,
base_table.destination_customer_cost, destination_carrier_bbz.zone,
destination_customer_bbz.zone, destination_carrier_bbz.detail,
destination_customer_bbz.detail, base_table.destination_carrier_free_time,
base_table.destination_customer_free_time, base_table.source_reseller_cost,
source_reseller_bbz.zone, source_reseller_bbz.detail,
base_table.source_reseller_free_time, base_table.destination_reseller_cost,
destination_reseller_bbz.zone, destination_reseller_bbz.detail,
base_table.destination_reseller_free_time, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"source" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as source_concurrent_calls_count,
COALESCE((SELECT data.val FROM accounting.cdr_tag_data AS data INNER JOIN
accounting.cdr_direction AS dir ON data.direction_id = dir.id INNER JOIN
accounting.cdr_provider AS prov ON data.provider_id = prov.id INNER JOIN
accounting.cdr_tag AS dict ON data.tag_id = dict.id WHERE data.cdr_id =
base_table.id AND dir.type = "source" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
source_concurrent_calls_count_customer, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count"),"") as
destination_concurrent_calls_count, COALESCE((SELECT data.val FROM
accounting.cdr_tag_data AS data INNER JOIN accounting.cdr_direction AS dir
ON data.direction_id = dir.id INNER JOIN accounting.cdr_provider AS prov ON
data.provider_id = prov.id INNER JOIN accounting.cdr_tag AS dict ON
data.tag_id = dict.id WHERE data.cdr_id = base_table.id AND dir.type =
"destination" AND prov.type="customer" AND
dict.type="concurrent_calls_count_customer"),"") as
destination_concurrent_calls_count_customer from accounting.cdr base_table
left outer join billing.voi

Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Started execution

Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Finished processing
records

Apr 22 10:25:01 spce (info) ngcp-cdr-exporter[259917]: Finalizing output
files

Apr 22 10:25:02 spce (info) ngcp-cdr-exporter[259917]: Committing changes to
database

Apr 22 10:25:02 spce (info) ngcp-cdr-exporter[259917]: All done

 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sipwise.com/pipermail/spce-user_lists.sipwise.com/attachments/20240422/69c0d1bc/attachment-0001.htm>