Page MenuHomePhabricator
Files F7312257

RATING.txt
No OneTemporary
Actions

RATING.txt
View Options

CDRTool rating engine
---------------------
CDRTool provides on-the-fly rating of CDRs from multiple data-sources like
OpenSER, Asterisk or Cisco gateways based on an easy to build rating plan,
which can be imported from external data sources.
A Call Detail Record (a CDR) is one record from Radius radacct table. The
CDR record contains all information related to a SIP session, like duration,
calling and called party.
The rating engine calculates the Price of the call, which is applied in
real-time to the web output and saved to the database containing the CDRs.
Based on exceptions, different rates may be applied per caller party
identified by the source IP address of the gateway, SIP subscriber or SIP
domain. The rates are linked with profiles corresponding with different time
of the day, day of the week or holidays. Each profile has its own rate
values. For rating calls, which span multiple profiles, the right rate is
selected and applied for the call duration within each profile. Each
customer may be assigned its own dedicated rating plans destination id and
names.
Chained profiles are possible to enable exception based rating. Multiple
customers may share a common rate list, while some destinations may be rated
differently, only the differences must be provisioned.
Multiple time zones are supported for multiple billing parties hosted on the
same platform.
Rating is applied only after the call has ended. CDRTool considers that a
call has ended when there is a stop time. In case of missing BYEs, CDRTool
relies on the fact that MediaProxy will update the CDR with the proper stop
time information based on the last moment the media stream passed through
the media proxy.
Fraud prevention
-----------------
Against fraud, one may enforce limits for post-paid customers, an external
scripts may build statistics based on customer usage, check customer quota
in external data-sources and block customers in the SIP Proxy. This will not
stop in real-time ongoing calls for customers exceeding their quota but
provides in practice a perfect protection against fraudulent usage.
See the script scripts/SER/quotaCheck.php, which is called from cron every 5
minutes. To speed up the quota checking processs, which normally requires a
full scan of the CDR table at each run, from version 4.7-0 onwards the
actual usage per user is stored in a memcache server or a mysql table
depending on the setup. It is preferably to use memcache server in place of
mysql to avoid loading the database with queries. The usage data will be
reused and incrementally updated during normalization process thus ofloading
the server from scanning the whole table at each quota check interval.
To start/stop memcache use /etc/init.d/cdrtool command.
Memcached can be manually downloaded from http://www.danga.com/memcached or
is automatically installed during debian package installation.
To enable the use of memcached define the following setting in global.inc:
$memcache_server = "Private.IP.Address.Of.CDRTool.machine:11212";
Beware that memcached server has no built in security, for this reason is
advisable to configure it to listen only to a private network interface.
PSTN rating
-----------
The following steps are taken to rate the calls terminated to PSTN. The
steps are performed during the normalization process.
1. Lookup the call rate based on time (peak/of-peak, day of week, holiday)
2. Calculate the call Price based on duration and destination id (country codes).
In global.inc there are several variables that affect how the price is
calculated.
These settings are global per CDRTool installation but some can be overwritten
with per customer values in the billing_customers table.
$PRICING=array(
"priceDenominator" => 10000, // Rates units (global setting)
"priceDecimalDigits" => 4, // Decimal information (global setting)
"minimumDurationCharged" => 0, // Rate a minimum of X seconds (per customer)
"minimumDuration" => 0, // Minimum duration to rate, if call duration is shorter the price is zero (per customer)
"durationPeriodRated" => 60 // Rate is per 60 seconds (global setting)
"trafficSizeRated" => 1024, // Default we rate per 1 MB (global setting)
"reportMissingRates" => 0 // Send emails to administrator in case of missing rates
);
Pricing formula
---------------
if min_duration then
minimumDurationCharged = min_duration
else if minimumDurationCharged set in global inc
use minimumDurationCharged from global.inc
else
minimumDurationCharged = call duration
if increment then
durationForRating = round to the next increment
else
durationForRating = call duration
if durationForRating >= minimumDurationCharged then
Price = connectCost/priceDenominator+
durationRate*durationForRating/durationPeriodRated/priceDenominator+
trafficRate/priceDenominator*(inputTraffic+outputTraffic)/8+
applicationRate/priceDenominator
else
Price = 0
3. Save the calculated price and DestinationId for each non zero call
in the CDRs. Having the price stored in database, it is possible to build
statistics to display consolidated revenues per country code, network or subscriber.
Update the normalized caller and destination for each CDR.
Time of the day rating
----------------------
1. Lookup the billing Profile in cdrtool.billing_customers table in the
following order: subscriber,domain,gateway (based on $this->dayofweek):
- profile_name1 matches week days [1-5]
- profile_name1_alt matches week days [1-5] if no rates for profile_name1
are found
- profile_name2 matches week-ends [6-0]
- profile_name2_alt matches week-ends [6-0] if no rates for profile_name2
are found
- profile_name2 matches also holidays from billing_holidays table
Note
----
in CDRTool, the week starts with 0 (Sunday) and ends with 6 (Saturday)
2. Using the profile_name found, lookup the rate_name based
on $this->hourofday in cdrtool.billing_profiles table
If no rate_name is found for the given profiles a second set of profiles
are used, profile_name1_alt and profile_name2_alt.
- the day may be split in maximum 4 periods
- the days starts with hour 0 and ends with hour 24
- rate_name1 defines the first interval after hour 0
- rate_name2 defines the first interval after rate_name1
- rate_name3 defines the first interval after rate_name2
- rate_name4 defines the first interval after rate_name3
When the hour matches an interval use the rate_nameX found to lookup the
rate in billing_rates, if no record is found use the rate called
'default'
3. Lookup in the cdrtool.billing_rates table the record having same name
found at point 2 and having billing_rates.destination = $this->destinationId
- return an array with all the rating values and the duration rated
Note
----
No rate will be returned if no destination Id is found. Make sure each
possible destination has a corresponding Id and name in destinations table.
4. If the duration rated at point 3 is less than total call duration, apply
point 3 again for the remaining call duration in next profile. A maximum
of 10 spans (different rates depending of time of day, day of the week)
can be calculated using this mechanism.
NGN rating flow
---------------
NGN rating support rating of data traffic based on application type.
1. Lookup the billing Profile in cdrtool.billing_customers table in the
following order: subscriber,domain,gateway:
- profileNGN
2. Using the profile found, lookup the rate based
on application type in cdrtool.billing_profilesNGN table
3. Lookup in the cdrtool.billing_ratesNGN table the record having same name
found at point 2.
- return an array with all the rating values and the duration rated
Importing/exporting of rating files
-----------------------------------
There are different data files needed for rating CDRs. The data files are
imported into corresponding MySQL tables. The files must be uploaded to
/var/www/CDRTool/csv directory. To load the files into the database run
the following command:
/var/www/CDRTool/scripts/importRatingTables.php
The import script knows to import the files only once so you may dump
several files there and safely run the import script from cron. The
import script detects whether the files have been imported by building
a unique key out of the filename and the hash of the file content. So
you may use the same filenames as long as the content differs and
viceversa. If the import file has changed any records, the rating
engine is automatically instructed to reload the changes.
Sample csv files are found in the setup directory. The CSV field order
is described in setup/*.csv sample files. The first element on each
line specifies the operation will be performed with the current record.
The operation can be 2 (update/insert), 1 (insert) or 3 (delete).
The updates are performed based on a unique key present in each table:
billing_customers - cust_idx (gateway,domain,subscriber)
destinations - cust_dest_idx (gateway,domain,subscriber,dest_id)
billing_profiles - profile_idx (gateway,domain,subscriber,name)
billing_rates - rate_idx (gateway,domain,subscriber,name,destination)
billing_ratesNGN - rate_idx (name);
billing_profilesNGN - profile_idx (name,application);
The content of the rating tables can be exported in the Rating tables page.
Rating database tables
----------------------
To see the tables structures use describe table_name after connecting to
cdrtool database. Run show tables and describe table_name:
billing_customers
+-------------------+
| Field |
+-------------------+
| id |
| gateway |
| domain |
| subscriber |
| profile_name1 |
| profile_name1_alt |
| profile_name2 |
| profile_name2_alt |
| profileNGN |
| timezone |
| increment |
| min_duration |
| country_code |
+-------------------+
billing_profiles
+------------+
| Field |
+------------+
| id |
| gateway |
| domain |
| subscriber |
| name |
| rate_name1 |
| hour1 |
| rate_name2 |
| hour2 |
| rate_name3 |
| hour3 |
| rate_name4 |
| hour4 |
+------------+
billing_profilesNGN
+-------------+
| Field |
+-------------+
| id |
| gateway |
| domain |
| subscriber |
| name |
| rate |
| application |
+-------------+
billing_rates
+-----------------+
| Field |
+-----------------+
| id |
| gateway |
| domain |
| subscriber |
| name |
| destination |
| durationRate |
| trafficRate |
| applicationRate |
| connectCost |
+-----------------+
billing_ratesNGN
+-------------+
| Field |
+-------------+
| id |
| gateway |
| domain |
| subscriber |
| name |
| TrafficRate |
| ConnectCost |
+-------------+
destinations
+------------+
| Field |
+------------+
| id |
| gateway |
| domain |
| subscriber |
| dest_id |
| dest_name |
| asr |
+------------+
billing_holidays
+-------+
| Field |
+-------+
| day |
+-------+
prepaid
+------------------+
| Field |
+------------------+
| id |
| account |
| disabled |
| balance |
| balance_previous |
| change_date |
| expires |
| call_lock |
| call_in_progress |
| last_call_price |
+------------------+
Rating tables management
------------------------
The rating tables can be edited from the web, click on Rating tables link.
One may insert/update/delete records or apply changes on selections. For
example it is possible to increase with XX units the rate for a specific
destination.
Numeric fields support mathematical operators [+-*/], one may update
using absolute or relative values the fields in the rating tables.
The rates may be copied in bulk and start quickly working with a fresh
new rating table. Select in the Rates PSTN table by filtering on rate
name. A new button appears which allows the copy of all selected rates
into a new set. The rates are copied under the old rate id with _N
suffix where N is the next available number for which same rate id does
not exist.
The content of the rating tables can be exported into comma separated
files. The CSV format has the same structure as the import file, is
fairly easy to modify an exported batch file into an external
application and load it back into CDRTool.
Reloading rating tables
------------------------
From cdrtool version >=3.2 the rating tables are no longer loaded into the
from database into memory during each normalization. The network rating
engine is contacted for rating all calls. The rating engine is a daemon,
which loads all rating tables and remains active in the server memory.
Reload of rating tables is possible without stopping the daemon by
connecting to it and issuing the reload command. The init.d script can alos
be used fro reloading the rating engine with the current values from the
rating database.
There are 3 ways of reloading the rating tables:
a. Each change executed in the WEB interface for rate management may update
the rating tables. If there is a change made to the database the link
'Reload rating tables' appears in red color on web page. Click on the link to
execute the reload.
b. Telnet to the IP address and port number specified for the Rating engine
in global.inc. Type help to see the list of commands available. Locate the
reload rates command and execute it followed by \n. You may see the result
of the command in syslog. The results displayed by syslog will show how many
entries have been reloaded from the rating table.
c. Run /etc/init.d/cdrtool reload command
Troubleshooting
---------------
To examine the rate information for a rated call click on the Id field on
the leftmost column. (Java script support in browser is required). A blue
are will open under the CDR containing more information about the SIP
session.
If you see no price in the CDR or no rating information appears in the call
details it means that either no destination was found in the destinations
table or no rate has been associated with that destination. Make sure that
for each entry in the destinations table there is a corespondent entry in
the rates table. CDRTool rating engine can send warning emails if it finds
missing entries in the rating tables if the system where CDRTool runs is
properly configured to send emails and the e-mail notification addresses are
set in global.inc:
$CDRTool['provider']['toEmail'] = "support@example.com";
$CDRTool['provider']['bccEmail'] = "cdrtool@example.com";
Re-rating calls
---------------
Sometimes is useful the change the rates for calls that have been already
normalized and rated, for example after changing the rating tables you wish
to apply the changes retroactively for the last month. To re-rate the CDRs
based on different tables do the following:
1. Add new files (like rates.csv, profiles.csv or customers.csv in
csv directory) or edit the rating tables using the WEB interface
2. Import the rating csv files using command (use full path of the import script)
/var/www/CDRTool/scripts/importRatingTables.php
3. Re-normalize the calls to be re-rated by either selecting ReNormalize
check-box in the search screen or by changing the Normalized field in the
CDR MySQL table (e.g. radacct):
Examples:
a) Re-rate calls for this month (2004-12) SIP domain example.com:
UPDATE radacct set Normalized = '0' where Realm = 'example.com'
and AcctStartTime >= '2004-12-01'
b) Re-rate calls for SIP subscriber sip01@example.com:
UPDATE radacct set Normalized = '0' where UserName = 'sip01@example.com'
4. Apply rating again using command:
/var/www/CDRTool/scripts/normalize.php
Warning:
Renormalization process can take long time during which your database
(radacct table) will be locked. Perform this operation only during low
traffic periods.
After renormalization, the monthly usage information used by the quota
system will be out of date. At the next run of the quotaCheck script, a full
table scan will be performed.
Holidays must be added as individual days YYYY-MM-DD in table
billing_holidays. The profile applied for holidays is the same as for
week-ends.
Sending custom notifications when quota is exceeded
---------------------------------------------------
Whene quota is exceeded an email notification is sent to the end-user. To
customize the email message subject, body and other header fields you must
create some entries in cdrtool.settings table.
See setup/mysql/custom_notifications.mysql for an example.
Known limitations
------------------
The rating engine cannot calculate rates based on the outbound carriers or
outbound gateways, the rates can be assigned only per calling party and not
per called party.
Price discounts must be applied outside CDRTool, in the billing system that
prints the actual invoices. CDRTool has no possibility to rate only calls
after X minutes per month for subscriber Y, all calls are rated uniformly.
Notes
-----
The fields gateway, domain and subscribers from the tables:
billing_profiles
billing_profilesNGN
billing_rates
billing_ratesNGN
are used only for the purpose of displaying them in the web interface of
CDRTool for login accounts that have restricted access to the corresponding
gateway, domain or subscribers.
Performance
-----------
Rating is part of the normalization process that happens every time a query
is executed in the web interface or when the rating engine is contacted by
the SIP Proxy or User Agent that performs the prepaid application.
The following tests have been performed between two machines with 3 GHz CPU
and 1 GB memory located on the same LAN having a round trip time of 0.2 ms.
The rating tables have been populated durring the tests with:
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 8135 destinations
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 6 profilesPSTN
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 16277 ratesPSTN
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 2 profilesNGN
Aug 11 11:25:43 sip03 CDRTool[4945]: Loaded 4 holidays
Aug 11 11:25:44 sip03 CDRTool[4945]: Loaded 7273 prepaid accounts
Tests results for the postpaid application:
Clients Rating command Execution speed
----------------------------------------------------------------
1 ShowPrice 390/s per client
5 ShowPrice 100/s per client
10 ShowPrice 60/s per client
Tests results for the prepaid application
Clients Rating command Execution speed
----------------------------------------------------------------
1 MaxSessionTime/DebitBalance 250/s per client
5 MaxSessionTime/DebitBalance 80/s per client
10 MaxSessionTime/DebitBalance 40/s per client
Client means either a SIP Proxy entity or a CDRTool server, which performs
the normalization process.
Notes
-----
The pre-calculated maximum duration of prepaid calls may be affected by
rating plans with custom time increments and minimum charged durations. Do
not use minimum durations or custom increments for customers using the
prepaid application.

File Metadata

Mime Type
text/plain
Expires
Sat, Feb 1, 5:33 AM (22 h, 42 m)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
3488761
Default Alt Text
RATING.txt (19 KB)

Event Timeline