Billing

This chapter describes the steps necessary to rate calls and export rated CDRs (call detail records) to external systems.

Billing Profiles

Service billing on Sipwise C5 is based on billing profiles, which may be assigned to customers and SIP peerings. The design focuses on a simple, yet flexible approach, to support arbitrary dial-plans without introducing administrative overhead for the system administrators. The billing profiles may define a base fee and free time or free money per billing interval. Unused free time or money automatically expires at the end of the billing interval.

Each profile may have call destinations (usually based on E.164 number prefix matching) with configurable fees attached. Call destination fees each support individual intervals and rates, with a different duration and/or rate for the first interval. (e.g.: charge the first minute when the call is opened, then every 30 seconds, or make it independent of the duration at all) It is also possible to specify different durations and/or rates for peak and off-peak hours. Peak time may be specified based on weekdays, with additional support for manually managed dates based on calendar days. The call destinations can finally be grouped for an overview on user’s invoices by specifying a zone in two detail levels. (E.g.: national landline, national mobile, foreign 1, foreign 2, etc.)

Creating Billing Profiles

The first step when setting up billing data is to create a billing profile, which will be the container for all other billing related data. Go to Settings→Billing and click on Create Billing Profile.

The fields Reseller, Handle and Name are mandatory.

Reseller: The reseller this billing profile belongs to.
Handle: A unique, permanently fixed string which is used to attach the billing profile to a customer or SIP peering contract.
Name: A free form string used to identify the billing profile in the Admin Panel. This may be changed at any time.
Interval charge: A base fee for the billing interval, specifying a monetary amount (represented as a floating point number) in whatever currency you want to use.
Interval free time: If you want to include free calling time in your billing profile, you may specify the number of seconds that are available every billing interval. See Creating Billing Fees below on how to select destinations which may be called using the free time.
Interval free cash: Same as for interval free time above, but specifies a monetary amount which may be spent on outgoing calls. This may be used for example to implement a minimum turnover for a contract, by setting the interval charge and interval free cash to the same values.
Fraud monthly limit: The monthly fraud detection limit (in Cent) for accounts with this billing profile. If the call fees of an account reach this limit within a billing interval, an action can be triggered.
Fraud monthly lock: a choice of none, foreign, outgoing, incoming, global. Specifies a lock level which will be used to lock the account and his subscribers when fraud monthly limit is exceeded.
Fraud monthly notify: An email address or comma-separated list of email addresses that will receive notifications when fraud monthly limit is exceeded.
Fraud daily limit: The fraud detection limit (in Cent) for accounts with this billing profile. If the call fees of an account reach this limit within a calendar day, an action can be triggered.
Fraud daily lock: a choice of none, foreign, outgoing, incoming, global. Specifies a lock level which will be used to lock the account and his subscribers when fraud daily limit is exceeded.
Fraud daily notify: An email address or comma-separated list of email addresses that will receive notifications when fraud daily limit is exceeded.
Currency: The currency symbol for your currency. Any UTF-8 character may be used and will be printed in web interfaces.
VAT rate: The percentage of value added tax for all fees in the billing profile. Currently for informational purpose only and not used further.
VAT included: Whether VAT is included in the fees entered in web forms or uploaded to the platform. Currently for informational purpose only and not used further.

Creating Billing Fees

Each Billing Profile holds multiple Billing Fees.

To set up billing fees, click on the Fees button of the billing profile you want to configure. Billing fees may be uploaded using a configurable CSV file format, or entered directly via the web interface by clicking Create Fee Entry. To configure the CSV field order for the file upload, rearrange the entries in the www_admin→fees_csv→element_order array in /etc/ngcp-config/config.yml and execute the command ngcpcfg apply 'changed fees element order'. The following is an example of working CSV file to upload (pay attention to double quotes):

".","^1",out,"EU","ZONE EU",5.37,60,5.37,60,5.37,60,5.37,60,0,0,regex_longest_pattern
"^01.+$","^02145.+$",out,"AT","ZONE Test",0.06250,1,0.06250,1,0.01755,1,0.01733,1,0,regex_longest_pattern,30,0.01,30,0.01

For input via the web interface, fill in the text fields accordingly.

A billing fee record essentially defines the rate per interval to charge the customer when calling a particular destination number. The properties below outline supported options in detail:

Zone: A zone for a group of fees. May be used to group fees for simplified display, e.g. on invoices. (e.g. foreign zone 1)
Match Mode: The mode for matching a fee’s source and destination patterns against a CDR’s source fields (the caller given by <source_cli>@<source_domain> or <source_cli> only) and destination fields (the callee given by <destination_user_in>@<destination_domain> or <destination_user_in> only). Each of the currently supported modes below provide different flexibility and speed:
1. Exact string (destination): The destination string has to match the destination from the CDR exactly. Fastest, O(log(#fees)). In csv files, this match mode is specified by exact_destination.
2. Prefix string: The fee‘s source/destination represent strings which both the source/destination from the CDR have to start with. The fee with the longest destination prefix is picked. If there are multiple, the one with the longest source prefix is picked. In contrast to regular-expression based match modes, this algorithm uses database index lookups instead of SQL REGEXP table scans. The performance boundary is O(length(cdr src) * length(cdr dest) * log(#fees)), hence this will be the preferred mode for tens of thousands of fees in place or high throughput (LCR, rating peer-to-peer calls). In csv files, this match mode is specified by prefix.
3. Regular expression - longest match: The fee‘s source/destination patterns represent PCREs which both have to match the source/destination from the CDR. The fee with the longest match within the destination string is picked. If there are multiple, the one with the longest match within the source string is picked. In csv files, this match mode is specified by regex_longest_match.
4. Regular expression - longest pattern: The fee‘s source/destination represent PCREs which both have to match the source/destination from the CDR. The fee with the longest (most distinctive) destination pattern is picked. If there are multiple, the one with the longest (most distinctive) source pattern is picked. In csv files, this match mode is specified by regex_longest_pattern.
If fees with different match mode are in place and matching, the precedence is given by above order. When omitted in file uploads, the legacy default regex_longest_pattern is used.
Source: The source pattern (prefix ie. 123 or regular expression ^123someone@sip\.sipwise\.com$). The legacy default "." regular expression (matching everything) will be set implicitly.
Destination: The destination pattern (string ie. 456somebody@sip.sipwise.com, prefix ie. 456 or regular expression ^456somebody@sip\.sipwise\.com$). This field must be set.
- To specify a special fixed rate for any ported number in the local LNP tables belonging to an LNP provider, a fee with exact_destination match mode and destination lnp:<lnp provider ID> can be set up.
- To specify an FCI (Furnished Charging Info) destination for cases when the FCI data is retrieved from the LNP lookup, use a format fci=10050 where "10050" is the FCI data.
Direction: Outbound for standard origination fees (applies to callers placing a call and getting billed for that) or Inbound for termination fees (applies to callees if you want to charge them for receiving various calls, e.g. for 800-numbers). If in doubt, use Outbound. If you upload fees via CSV files, use out or in, respectively.

The {match mode, source, destination, direction} combination needs to be unique for a billing profile. The system will return an error if such a set is specified twice via web interface/ or /api, or skipped when processing the file upload. When uploading fees, the Purge Existing checkbox allows to drop all existing fees before creating the records.

There are several internal services (vsc, conference, voicebox) which will need a specific destination entry with a domain-based destination. If you don’t want to charge the same (or nothing) for those services, add a fee for destination \.local$ there. If you want to charge different amounts for those services, break it down into separate fee entries for @vsc\.local$, @conference\.local$ and @voicebox\.local$ with the according fees. NOT CREATING EITHER THE CATCH-ALL FEE OR THE SEPARATE FEES FOR THE .local DOMAIN WILL BREAK YOUR RATING PROCESS!

Onpeak init rate: The rate for the first rating interval in cent (of whatever currency, represented as a floating point number) per second. Applicable to calls during onpeak hours.
Onpeak init interval: The duration of the first billing interval, in seconds. Applicable to calls during onpeak hours.
Onpeak follow rate: The rate for subsequent rating intervals in cent (of whatever currency, represented as a floating point number) per second. Applicable to calls during onpeak hours. Defaults to onpeak init rate.
Onpeak follow interval: The duration of subsequent billing intervals, in seconds. Applicable to calls during onpeak hours. Defaults to onpeak init interval.
Offpeak init rate: The rate for the first rating interval in cent (of whatever currency, represented as a floating point number) per second. Applicable to calls during off-peak hours. Defaults to onpeak init rate.
Offpeak init interval: The duration of the first billing interval, in seconds. Applicable to calls during off-peak hours. Defaults to onpeak init interval.
Offpeak follow rate: The rate for subsequent rating intervals in cent (of whatever currency, represented as a floating point number) per second. Applicable to calls during off-peak hours. Defaults to offpeak init rate if that one is specified, or to onpeak follow rate otherwise.
Offpeak follow interval: The duration of subsequent billing intervals, in seconds. Applicable to calls during off-peak hours. Defaults to offpeak init interval if that one is specified, or to onpeak follow interval otherwise.
Onpeak use free time: Specifies whether free time minutes may be used when calling this destination during onpeak hours. Specified in the file upload as 0, n[o], f[alse] and 1, y[es], t[rue] respectively.
Offpeak use free time: Specifies whether free time minutes may be used when calling this destination during off-peak. Specified in the file upload as 0, n[o], f[alse] and 1, y[es], t[rue] respectively.
Onpeak extra second: If defined, an extra rate will be charged at the given second of call time for post-paid calls. Applicable to calls started during onpeak hours.
Onpeak extra rate: The rate to charge if the call time exceeds extra second in cent (of whatever currency, represented as a floating point number). Applicable to calls started during onpeak hours.
Offpeak extra second: See onpeak extra second. Applicable to calls started during offpeak hours.
Offpeak extra rate: See onpeak extra rate. Applicable to calls started during offpeak hours.

Creating Off-Peak Times

To be able to differentiate between on-peak and off-peak calls, the platform stores off-peak times for every billing profile based on weekdays and/or calendar days. To edit the settings for a billing profile, go to Settings→Billing and press the Off-Peaktimes button on the billing profile you want to configure.

To set off-peak times for a weekday, click on Edit next to the according weekday. You will be presented with two input fields which both receive a timestamp in the form of hh:mm:ss specifying a time of day for the start and end of the off-peak period. If any of the fields is left empty, the system will automatically insert 00:00:00 (start field) or 23:59:59 (end field). Click on Add to store the setting in the database. You may create more than one off-peak period per weekday. To delete a range, click Delete next to the entry. Click the close icon when done.

To specify off-peak ranges based on calendar dates, click on Create Special Off-Peak Date. Enter a date in the form of YYYY-MM-DD hh:mm:ss into the Start Date/Time input field and End Date/Time input field to define a range for the off-peak period.

Fraud Detection and Locking

The Sipwise C5 supports a fraud detection feature, which is designed to detect accounts causing unusually high customer costs, and then to perform one of several actions upon those accounts. This feature can be enabled and configured through two sets of billing profile options described in Creating Billing Profiles, namely the monthly (fraud monthly limit, fraud monthly lock and fraud monthly notify) and daily limits (fraud daily limit, fraud daily lock and fraud daily notify). Either monthly/daily limits or both of them can be active at the same time.

Monthly fraud limit check runs once a day, shortly after midnight local time and daily fraud limit check runs every 30min. A background script (managed by cron daemon) automatically checks all accounts which are linked to a billing profile enabled for fraud detection, and selects those which have caused a higher cost than the fraud monthly limit configured in the billing profile, within the currently active billing interval (e.g. in the current month), or a higher cost than the fraud daily limit configured in the billing profile, within the calendar day. It then proceeds to perform at least one of the following actions on those accounts:

If fraud lock is set to anything other than none, it will lock the account accordingly (e.g. if fraud lock is set to outgoing, the account will be locked for all outgoing calls).
If anything is listed in fraud notify, an email will be sent to the email addresses configured. The email will contain information about which account is affected, which subscribers within that account are affected, the current account balance and the configured fraud limit, and also whether or not the account was locked in accordance with the fraud lock setting. It should be noted that this email is meant for the administrators or accountants etc., and not for the customer.

Fraud Lock Levels

Fraud lock levels are various protection (and notification) settings that are applied to subscribers of a Customer, if fraud detection is enabled in the currently active billing profile and the Customer’s daily or monthly fraud limit has been exceeded.

The following lock levels are available:

none: no account locking will happen
foreign calls: only calls within the subscriber’s own domain, and emergency calls, are allowed
all outgoing calls: subscribers of the customer cannot place any calls, except calls to free and emergency destinations
incoming and outgoing: subscribers of the customer cannot place and receive any calls, except calls to free and emergency destinations
global: same restrictions as at incoming and outgoing level, additionally subscribers are not allowed to access the Customer Self Care (CSC) interface
ported: only automatic call forwarding, due to number porting, is allowed

You can override fraud detection and locking settings of a billing profile on a per-account basis via REST API or the Admin interface.

Accounts that were automatically locked by the fraud detection feature will not be automatically unlocked when the next billing interval starts. This has to be done manually through the administration panel or through the provisioning interface.

If fraud detection is configured to only send an email and not lock the affected accounts, it will continue to do so for over-limit accounts every day. The accounts must either be locked in order to stop the emails (only currently active accounts are considered when the script looks for over-limit accounts) or some other action to resolve the conflict must be taken, such as disabling fraud detection for those accounts.

It is possible to fetch the list of fraud events and thus get fraud status of Customers by using the REST API and referring to the resource: /api/customerfraudevents.

Notes on Billing and Call Rating

Cash balance with post-paid billing profile

Customers with a post-paid billing profile may have a positive account cash balance value. This is the regular case when using a post-paid billing profile showing a free cash greater than '0'.

You can set the free cash (and the free time) in the billing profile. The account balance will be set and managed (i.e. refilled or carried over) automatically for subsequent balance intervals.

In case the account has a positive cash balance, the cost of the call will be deducted from that balance and not considered as additional cost of that particular call for the customer.

The rating engine (ngcp-rate-o-mat) in Sipwise C5 will write '0' instead of the real cost of a call in the CDR, if the source customer’s (who initiated the call) account has a positive cash balance! The purpose of this is to reflect the usage of free cash in the CDR for the particular call.

It might happen, for instance, that a customer’s billing profile is changed from pre-paid to post-paid, and the customer already had a positive cash balance on his account. In that case the same call rating mechanism is involved as for the free cash.

Billing Data Export

Regular billing data export is done using CSV (comma separated values) files which may be downloaded from the platform using the cdrexport user which has been created during the installation.

There are two types of exports. One is CDR (Call Detail Records) used to charge for calls made by subscribers, and the other is EDR (Event Detail Records) used to charge for provisioning events like enabling certain features.

Glossary of Terms

Billing records contain fields that hold data of various entities that play a role in the phone service offered by Sipwise C5. For a better understanding of billing data please refer to the glossary provided here:

Account: the customer’s account that is charged for calls of its subscriber(s)
Carrier: a SIP peer that sends incoming calls to, or receives outgoing calls from NGCP. A carrier may charge fees for the outgoing calls from Sipwise C5 (outbound billing fee), or for the incoming calls to Sipwise C5 (inbound billing fee).
Contract: the service contract that represents a customer, a reseller or a SIP peer; a contract on Sipwise C5 contains the billing profile (billing fees) too
Customer: the legal entity that represents any number of subscribers; this entity receives the bills for calls of its subscriber(s)
Provider: either the reseller that holds a subscriber who is registered on NGCP, or the SIP peer that handles calls between an external subscriber and NGCP
Reseller: the entity who is the direct, administrative service provider of a group of customers and subscribers registered on NGCP; Sipwise C5 operator may also charge a reseller for the calls initiated or received by its subscribers
User: the subscriber who either is registered on NGCP, or is an external call party

File Name Format

In order to be able to easily identify billing files, the file names are constructed by the following fixed-length fields:

<prefix><separator><version><separator><timestamp><separator><sequence number><suffix>

The definition of the specific fields is as follows:

Table 1. CDR/EDR export file name format
File name element	Length	Description
`<prefix>`	7	A fixed string. Always sipwise.
`<separator>`	1	A fixed character. Always `_`.
`<version>`	3	The format version, a three digit number. Currently 007.
`<timestamp>`	14	The file creation timestamp in the format YYYYMMDDhhmmss.
`<sequence number>`	10	A unique 10-digit zero-padded sequence number for quick identification.
`<suffix>`	4	A fixed string. Always .cdr or .edr.

A valid example filename for a CDR billing file created at 2012-03-10 14:30:00 and being the 42nd file exported by the system, is:

sipwise_007_20130310143000_0000000042.cdr

File Format

Each billing file consists of three parts: one header line, zero to 5000 body lines and one trailer line.

File Header Format

The billing file header is one single line, which is constructed by the following fields:

<version>,<number of records>

The definition of the specific fields is as follows:

Table 2. CDR/EDR export file header line format
Body Element	Length	Type	Description
`<version>`	3	zero-padded uint	The format version. Currently 007.
`<number of records>`	4	zero-padded uint	The number of body lines contained in the file.

A valid example for a Header is:

007,0738

File Body Format for Call Detail Records (CDR)

The body of a CDR consists of a minimum of zero and a default maximum of 5000 lines. The platform operator can configure the maximum number of lines kept in a file by updating the cdrexport.max_rows_per_file parameter in /etc/ngcp-config/config.yml file. Each line holds one call detail record in CSV format and is constructed by a configurable set of fields, all of them enclosed in single quotes.

The following table defines the default set of fields that are inserted into the CDR file, for exports related to system scope. The list of fields is defined in /etc/ngcp-config/config.yml file, cdrexport.admin_export_fields parameter.

Table 3. Default set of system CDR fields
Body Element	Length	Type	Description
`CDR_ID`	1-10	uint	Internal CDR ID.
`UPDATE_TIME`	19	timestamp	Timestamp of last modification, including date and time (with seconds precision).
`SOURCE_USER_ID`	36	string	Internal UUID of calling party subscriber. Value is `0` if calling party is external.
`SOURCE_PROVIDER_ID`	0-255	string	Internal ID of the contract of calling party provider (i.e. reseller or peer).
`SOURCE_EXTERNAL_SUBSCRIBER_ID`	0-255	string	External, arbitrary ID of calling party subscriber. (A string value shown as "External ID" property of an Sipwise C5 subscriber.)
`SOURCE_SUBSCRIBER_ID`	1-11	uint	Internal ID of calling party subscriber. Value is `0` if calling party is external.
`SOURCE_EXTERNAL_CONTRACT_ID`	0-255	string	External, arbitrary ID of calling party customer. (A string value shown as "External ID" property of an Sipwise C5 customer/peer.)
`SOURCE_ACCOUNT_ID`	1-11	uint	Internal ID of calling party customer.
`SOURCE_USER`	0-255	string	SIP username of calling party.
`SOURCE_DOMAIN`	0-255	string	SIP domain of calling party.
`SOURCE_CLI`	0-64	string	CLI of calling party in E.164 format.
`SOURCE_CLIR`	1	uint	`1` for calls with CLIR, `0` otherwise.
`SOURCE_IP`	0-64	string	IP Address of the calling party.
`DESTINATION_USER_ID`	36	string	Internal UUID of called party subscriber. Value is `0` if called party is external.
`DESTINATION_PROVIDER_ID`	0-255	string	Internal ID of the contract of called party provider (i.e. reseller or peer).
`DESTINATION_EXTERNAL_SUBSCRIBER_ID`	0-255	string	External, arbitrary ID of called party subscriber. (A string value shown as "External ID" property of an Sipwise C5 subscriber.)
`DESTINATION_SUBSCRIBER_ID`	1-11	uint	Internal ID of called party subscriber. Value is `0` if calling party is external.
`DESTINATION_EXTERNAL_CONTRACT_ID`	0-255	string	External, arbitrary ID of called party customer. (A string value shown as "External ID" property of an Sipwise C5 customer/peer.)
`DESTINATION_ACCOUNT_ID`	1-11	uint	Internal ID of called party customer.
`DESTINATION_USER`	0-255	string	Final SIP username of called party.
`DESTINATION_DOMAIN`	0-255	string	Final SIP domain of called party.
`DESTINATION_USER_IN`	0-255	string	Incoming SIP username of called party, after applying inbound rewrite rules.
`DESTINATION_DOMAIN_IN`	0-255	string	Incoming SIP domain of called party, after applying inbound rewrite rules.
`DESTINATION_USER_DIALED`	0-255	string	The user-part of the SIP Request URI as received by NGCP.
`PEER_AUTH_USER`	0-255	string	Username used to authenticate towards peer.
`PEER_AUTH_REALM`	0-255	string	Realm used to authenticate towards peer.
`CALL_TYPE`	3-4	string	The type of the call - one of: call: normal call cfu: call forward unconditional cfb: call forward busy cft: call forward timeout cfna: call forward not available cfs: call forward for SMS cfr: call forward on response cfo: call forward on overflow
`CALL_STATUS`	2-8	string	The final call status - one of: ok: successful call busy: called party busy noanswer: no answer from called party cancel: cancel from caller offline called party offline timeout: no reply from called party other: unspecified, see `CALL_CODE` field for details
`CALL_CODE`	3	string	The final SIP status code.
`INIT_TIME`	23	timestamp	Timestamp of call initiation (SIP 'INVITE' received from calling party). Includes date, time with milliseconds (3 decimals).
`START_TIME`	23	timestamp	Timestamp of call establishment (final SIP response received from called party). Includes date, time with milliseconds (3 decimals).
`DURATION`	4-13	fixed precision (3 decimals)	Length of call (calculated from `START_TIME`) including milliseconds (3 decimals).
`END_TIME`	23	timestamp	`START_TIME` plus `DURATION`.
`INIT_TIME_TRUNCATED`	19	timestamp	`INIT_TIME` without milliseconds.
`START_TIME_TRUNCATED`	19	timestamp	`START_TIME` without milliseconds.
`END_TIME_TRUNCATED`	19	timestamp	`END_TIME` without milliseconds.
`TIMEZONE`	100	string	The name of the local subscriber’s active timezone, defined by the contact of the subscriber/contract/reseller. The caller’s timezone is used for outgoing calls. For calls from external susbcribers, the callee’s timezone is used. System timezone (defined in config.yml) is used for transit calls.
`INIT_TIME_LOCALIZED`	23	timestamp	`INIT_TIME`, converted to `TIMEZONE`.
`START_TIME_LOCALIZED`	23	timestamp	`START_TIME`, converted to `TIMEZONE`.
`END_TIME_LOCALIZED`	23	timestamp	`END_TIME`, converted to `TIMEZONE`.
`INIT_TIME_LOCALIZED_TRUNCATED`	19	timestamp	`INIT_TIME_LOCALIZED` without milliseconds.
`START_TIME_LOCALIZED_TRUNCATED`	19	timestamp	`START_TIME_LOCALIZED` without milliseconds.
`END_TIME_LOCALIZED_TRUNCATED`	19	timestamp	`END_TIME_LOCALIZED` without milliseconds.
`CALL_ID`	0-255	string	The SIP Call-ID.
`RATING_STATUS`	2-7	string	The internal rating status of the CDR - one of: unrated: not rated ok: successfully rated failed: error while rating Currently always ok or unrated, depending on whether rating is enabled or not.
`RATED_AT`	0-19	datetime	Time of rating, including date and time (with seconds precision). Empty if CDR is not rated.
`SOURCE_CARRIER_COST`	7-14	fixed precision (6 decimals)	The originating carrier cost that the carrier (i.e. SIP peer) charges for the calls routed to his network, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_CUSTOMER_COST`	7-14	fixed precision (6 decimals)	The originating customer cost, or empty if CDR is not rated.
`SOURCE_CARRIER_ZONE`	0-127	string	Name of the originating carrier billing zone, or `onnet` if data is not available. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_CUSTOMER_ZONE`	0-127	string	Name of the originating customer billing zone, or empty if CDR is not rated.
`SOURCE_CARRIER_DETAIL`	0-127	string	Description of the originating carrier billing zone, or `platform internal` if data is not available. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_CUSTOMER_DETAIL`	0-127	string	Description of the originating customer billing zone, or empty if CDR is not rated.
`SOURCE_CARRIER_FREE_TIME`	1-10	uint	The number of free time seconds used on originating carrier side, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_CUSTOMER_FREE_TIME`	1-10	uint	The number of free time seconds used from the originating customer’s account balance, or empty if CDR is not rated.
`DESTINATION_CARRIER_COST`	7-14	fixed precision (6 decimals)	The terminating carrier cost, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_CUSTOMER_COST`	7-14	fixed precision (6 decimals)	The terminating customer cost, or empty if CDR is not rated.
`DESTINATION_CARRIER_ZONE`	0-127	string	Name of the terminating carrier billing zone, or `onnet` if data is not available. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_CUSTOMER_ZONE`	0-127	string	Name of the terminating customer billing zone, or empty if CDR is not rated.
`DESTINATION_CARRIER_DETAIL`	0-127	string	Description of the terminating carrier billing zone, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_CUSTOMER_DETAIL`	0-127	string	Description of the terminating customer billing zone, or empty if CDR is not rated.
`DESTINATION_CARRIER_FREE_TIME`	1-10	uint	The number of free time seconds used on terminating carrier side, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_CUSTOMER_FREE_TIME`	1-10	uint	The number of free time seconds used from the terminating customer’s account balance, or empty if CDR is not rated.
`SOURCE_RESELLER_COST`	7-14	fixed precision (6 decimals)	The originating reseller cost, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_RESELLER_ZONE`	0-127	string	Name of the originating reseller billing zone, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_RESELLER_DETAIL`	0-127	string	Description of the originating reseller billing zone, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`SOURCE_RESELLER_FREE_TIME`	1-10	uint	The number of free time seconds used from the originating reseller’s account balance, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_RESELLER_COST`	7-14	fixed precision (6 decimals)	The terminating reseller cost, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_RESELLER_ZONE`	0-127	string	Name of the terminating reseller billing zone, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_RESELLER_DETAIL`	0-127	string	Description of the terminating reseller billing zone, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`DESTINATION_RESELLER_FREE_TIME`	1-10	uint	The number of free time seconds used from the terminating reseller’s account balance, or empty if CDR is not rated. PLEASE NOTE: Only available in system exports, not for resellers.
`<line_terminator>`	1	string	Always `\n` (special char LF - ASCII `0x0A`).

A valid example of one body line of a rated CDR is (line breaks added for clarity):

'15','2013-03-26 22:09:11','a84508a8-d256-4c80-a84e-820099a827b0','1','','1','',
'2','testuser1','192.168.51.133','4311001','0','192.168.51.1',
'94d85b63-8f4b-43f0-b3b0-221c9e3373f2','1','','3','','4','testuser3',
'192.168.51.133','testuser3','192.168.51.133','testuser3','','','call','ok','200',
'2013-03-25 20:24:50.890','2013-03-25 20:24:51.460','10.880','44449842',
'ok','2013-03-25 20:25:27','0.00','24.00','onnet','testzone','platform internal',
'testzone','0','0','0.00','200.00','','foo','','foo','0','0',
'0.00','','','0','0.00','','','0'

The format of the CDR export files generated for resellers (as opposed to the complete system-wide export) is identical except for a few missing fields.

Please check the description of fields in the table above, in order to see which fields are omitted for reseller related CDR exports.

The list of fields for reseller CDR export is defined in /etc/ngcp-config/config.yml file, cdrexport.reseller_export_fields parameter.

Extra fields that can be exported to CDRs

Supplementary Data

There are fields in CDR database that contain supplementary data related to subscribers. This data is not used by Sipwise C5 for CDR processing but rather provides the system administrator with a possibility to include supplementary information in CDRs.

This informational section is meant for problem solving / debugging purpose: The supplementary data listed in following table is stored in provisioning.voip_preferences database table.

Table 4. Supplementary data in CDR fields
Body Element	Length	Type	Description
`SOURCE_GPP0`	0-255	string	Supplementary data field 0 of calling party.
`SOURCE_GPP1`	0-255	string	Supplementary data field 1 of calling party.
`SOURCE_GPP2`	0-255	string	Supplementary data field 2 of calling party.
`SOURCE_GPP3`	0-255	string	Supplementary data field 3 of calling party.
`SOURCE_GPP4`	0-255	string	Supplementary data field 4 of calling party.
`SOURCE_GPP5`	0-255	string	Supplementary data field 5 of calling party.
`SOURCE_GPP6`	0-255	string	Supplementary data field 6 of calling party.
`SOURCE_GPP7`	0-255	string	Supplementary data field 7 of calling party.
`SOURCE_GPP8`	0-255	string	Supplementary data field 8 of calling party.
`SOURCE_GPP9`	0-255	string	Supplementary data field 9 of calling party.
`DESTINATION_GPP0`	0-255	string	Supplementary data field 0 of called party.
`DESTINATION_GPP1`	0-255	string	Supplementary data field 1 of called party.
`DESTINATION_GPP2`	0-255	string	Supplementary data field 2 of called party.
`DESTINATION_GPP3`	0-255	string	Supplementary data field 3 of called party.
`DESTINATION_GPP4`	0-255	string	Supplementary data field 4 of called party.
`DESTINATION_GPP5`	0-255	string	Supplementary data field 5 of called party.
`DESTINATION_GPP6`	0-255	string	Supplementary data field 6 of called party.
`DESTINATION_GPP7`	0-255	string	Supplementary data field 7 of called party.
`DESTINATION_GPP8`	0-255	string	Supplementary data field 8 of called party.
`DESTINATION_GPP9`	0-255	string	Supplementary data field 9 of called party.

Account balance details (prepaid calls)

There are fields in CDR database that show changes in cash or free time balance. In addition to that, a history of billing packages / profiles may also be present, since Sipwise C5 vouchers, that are used to top-up, may also be set up to cause a transition of profile packages. (Which in turn can result in changing the billing profile/applicable fees). Therefore the billing package and profile valid at the time of the CDR are recorded and exposed as fields for CDR export.

Such fields may also be required to integrate Sipwise C5 with legacy billing systems.

Please be aware that pre-paid billing functionality is only available in Sipwise C5 PRO and Sipwise C5 CARRIER products.

The name of CDR data field consists of the elements listed below:

source|destination: decides if the data refers to calling (source) or called (destination) party
carrier|reseller|customer: the account owner, whose billing data is referred
data type:
1. cash_balance|free_time_balance _ before|after: cash balance or free time balance, before or after the call
2. profile_package_id|contract_balance_id: internal ID of the active pre-paid billing profile or the account balance

Examples:

source_customer_cash_balance_before
destination_customer_profile_package_id

For calls spanning multiple balance intervals, the latter one will be selected, that is the balance interval where the call ended.

Distinguish between on-net and off-net calls CDRs

On-net calls (made only between devices on your network) are sometimes treated differently from off-net calls (terminated to or received from a peer) in external billing systems.

To distinguish between on-net and off-net calls in such a billing systems, check the source_user_id and destination_user_id fields. For on-net calls, both fields will have a different from zero value (actually, a UUID).

File Body Format for Event Detail Records (EDR)

The body of an EDR consists of a minimum of zero and a maximum of 5000 lines. The platform operator can configure the maximum number of lines kept in a file by updating the eventexport.max_rows_per_file parameter in /etc/ngcp-config/config.yml file. Each line holds one call detail record in CSV format and is constructed by the fields as per the subsequent table.

The following table defines the default set of fields that are inserted into the EDR file, for exports related to system scope. The list of fields is defined in /etc/ngcp-config/config.yml file, eventexport.admin_export_fields parameter.

Table 5. Default set of system EDR fields
Body Element	Length	Type	Description
`EVENT_ID`	1-11	uint	Internal EDR ID.
`TYPE`	0-255	string	The type of the event - one of: start_profile: A subscriber profile has been newly assigned to a subscriber. end_profile: A subscriber profile has been removed from a subscriber. update_profile: A subscriber profile has been changed for a subscriber. start_huntgroup: A subscriber has been provisioned as PBX / hunting group. end_huntgroup: A subscriber has been deprovisioned as PBX / hunting group. start_ivr: A subscriber has a new call-forward to Auto-Attendant. end_ivr: A subscriber has removed a call-forward to Auto-Attendant.
`CONTRACT_EXTERNAL_ID`	0-255	string	The external ID of the customer. (A string value shown as "External ID" property of an Sipwise C5 customer.)
`COMPANY`	0-127	string	The company name of the customer’s contact.
`SUBSCRIBER_EXTERNAL_ID`	0-255	string	The external ID of the subscriber. (A string value shown as "External ID" property of an Sipwise C5 subscriber.) PLEASE NOTE: This field is empty in case of `start_huntgroup` and `end_huntgroup` events.
`PILOT_PRIMARY_NUMBER`	0-64	string	The pilot subscriber’s primary number (HPBX subscribers). PLEASE NOTE: This is not included in default set of EDR fields from Sipwise C5 version mr5.0 upwards.
`PRIMARY_NUMBER`	0-64	string	The VoIP number of the subscriber with the highest ID (DID or primary number).
`OLD_PROFILE_NAME`	0-255	string	The old status of the event. Depending on the event_type: start_profile: Empty. end_profile: The name of the subscriber profile which got removed from the subscriber. update_profile: The name of the former subscriber profile which got updated. start_huntgroup: Empty. end_huntgroup: Empty. start_ivr: Empty. end_ivr: Empty.
`NEW_PROFILE_NAME`	0-255	string	The new status of the event. Depending on the event_type: start_profile: The name of the subscriber profile which got assigned to the subscriber. end_profile: Empty. update_profile: The name of the new subscriber profile which got applied. start_huntgroup: Empty. end_huntgroup: Empty. start_ivr: Empty. end_ivr: Empty.
`TIMESTAMP`	23	timestamp	Timestamp of event. Includes date, time with milliseconds (3 decimals).
`RESELLER_ID`	1-11	uint	Internal ID of the reseller which the event belongs to. PLEASE NOTE: Only available in system exports, not for resellers.
`<line_terminator>`	1	string	A fixed character. Always `\n` (special char LF - ASCII 0x0A).

A valid example of one body line of an EDR is (line breaks added for clarity):

"1","start_profile","sipwise_ext_customer_id_4","Sipwise GmbH",
"sipwise_ext_subscriber_id_44","436667778","","1","2014-06-19 11:34:31","1"

The format of the EDR export files generated for resellers (as opposed to the complete system-wide export) is identical except for a few missing fields.

Please check the description of fields in the table above, in order to see which fields are omitted for reseller related EDR exports.

The list of fields for reseller EDR export is defined in /etc/ngcp-config/config.yml file, eventexport.reseller_export_fields parameter.

Extra fields that can be exported to EDRs

There are fields in EDR database that contain supplementary data related to subscribers, for example subscriber phone numbers are such data.

Table 6. Supplementary data in EDR fields
Body Element	Length	Type	Description
`SUBSCRIBER_PROFILE_SET_NAME`	0-255	string	The subscriber’s profile set name.
`PILOT_SUBSCRIBER_PROFILE_SET_NAME`	0-255	string	The profile set name of the subscriber’s pilot subscriber.
`PILOT_SUBSCRIBER_PROFILE_NAME`	0-255	string	The profile name of the subscriber’s pilot subscriber.
`FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE`	0-255	string	The subscriber’s non-primary alias with lowest ID, before number updates during the operation.
`FIRST_NON_PRIMARY_ALIAS_USERNAME_AFTER`	0-255	string	The subscriber’s non-primary alias with lowest ID, after number updates during the operation.
`PILOT_FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE`	0-255	string	The non-primary alias with lowest ID of the subscriber’s pilot subscriber, before number updates during the operation.
`PILOT_FIRST_NON_PRIMARY_ALIAS_USERNAME_AFTER`	0-255	string	The non-primary alias with lowest ID of the subscriber’s pilot subscriber, after number updates during the operation.
`NON_PRIMARY_ALIAS_USERNAME`	0-255	string	The non-primary alias of a subscriber affected by an `update_profile`, `start_profile` or `end_profile` event to track number changes.
`PRIMARY_ALIAS_USERNAME_BEFORE`	0-255	string	The subscriber’s primary alias, before number updates during the operation.
`PRIMARY_ALIAS_USERNAME_AFTER`	0-255	string	The subscriber’s primary alias, after number updates during the operation.
`PILOT_PRIMARY_ALIAS_USERNAME_BEFORE`	0-255	string	The primary alias of the subscriber’s pilot subscriber, before number updates during the operation.
`PILOT_PRIMARY_ALIAS_USERNAME_AFTER`	0-255	string	The primary alias of the subscriber’s pilot subscriber, after number updates during the operation.
`FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE_AFTER`	0-255	string	Equals `FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE`, if the value is not NULL, otherwise it’s the same as `FIRST_NON_PRIMARY_ALIAS_USERNAME_AFTER`.
`PILOT_FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE_AFTER`	0-255	string	Equals `PILOT_FIRST_NON_PRIMARY_ALIAS_USERNAME_BEFORE`, if the value is not NULL, otherwise it’s the same as `PILOT_FIRST_NON_PRIMARY_ALIAS_USERNAME_AFTER`.

File Trailer Format

The billing file trailer is one single line, which is constructed by the following fields:

<md5 sum>

The <md5 sum> is a 32 character hexadecimal MD5 hash of the Header and Body.

To validate the billing file, one must remove the Trailer before computing the MD5 sum of the file. The ngcp-cdr-md5 program included in the ngcp-cdr-exporter package can be used to validate the integrity of the file.

Given a CDR-file named as sipwise_001_20071110123000_0000000004.cdr, the output of the integrity check for an intact CDR file would be:

$ ngcp-cdr-md5 sipwise_001_20071110123000_0000000004.cdr
/tmp/ngcp-cdr-md5.sipwise_001_20071110123000_0000000004.cdr.oqkd4P2zXI: OK

If the file has been altered during transmission, the output of the integrity check would be:

$ ngcp-cdr-md5 sipwise_001_20071110123000_0000000004.cdr
/tmp/ngcp-cdr-md5.sipwise_001_20071110123000_0000000004.cdr.hUtuhtKEn1: FAILED
md5sum: WARNING: 1 of 1 computed checksum did NOT match

File Transfer

Billing files are created twice per hour at minutes 25 and 55 and are stored in the home directory of the cdrexport user. If the amount of records within the transmission interval exceeds the threshold of 5000 records per file, multiple billing files are created. If no billing records are found for an interval, a billing file without body data is constructed for detection of lost billing files on the 3rd party side.

CDR and EDR files are fetched by a 3rd party billing system using SFTP or SCP with either public key or password authentication using the username cdrexport.

If public key authentication is chosen, the public key file has to be stored in the file .ssh/authorized_keys below the home directory of the cdrexport user (i.e. /home/jail/home/cdrexport/.ssh/authorized_keys). Otherwise, a password has to be set for the user.

The 3rd party billing system is responsible for deleting CDR files after fetching them.

The cdrexport user is kept in a jailed environment on the system, so it has only access to a very limited set of commandline utilities.