CDRTool installation -------------------- Download -------- You can install CDRTool in the following ways: a. As a debian package from ag-projects repository b. Generating the debian package c. Installing from the tar archive d. From the version control repository a. As a debian package from ag-projects repository (stable version) Add these lines to /etc/apt/sources.list # AG Projects software deb http://ag-projects.com/debian unstable main deb-src http://ag-projects.com/debian unstable main Install the AG Projects debian software signing key: wget http://download.ag-projects.com/agp-debian-gpg.key apt-key add agp-debian-gpg.key After that, run: apt-get update apt-get install cdrtool b. Generating the debian package The software can be downloaded as a tar archive from: http://download.ag-projects.com/CDRTool/ Extract it using tar xzvf cdrtool-version.tar.gz cd CDRTool debuild Assuming you have all the dependencies satisfied (if you are compiling opensips from source you'll have them), it will generate a file called cdrtool_version_all.deb You can install it with dpkg -i cdrtool_version_all.deb c. Installing from the tar archive (stable version) The software can be downloaded as a tar archive from: http://download.ag-projects.com/CDRTool/ Extract it using tar xzvf cdrtool-version.tar.gz and copy the directory under the your web root directory/CDRTool (/var/www/CDRTool on Debian systems). d. Installing from the version control repository (development branch) The source code is managed using darcs version control tool. The darcs repository can be fetched with: darcs get http://devel.ag-projects.com/repositories/cdrtool To obtain the incremental changes after the initial get: cd cdrtool darcs pull -a Configuration ------------- Create CDRTool database ----------------------- You need MySQL root access with GRANT priviledges from the machine where CDRTool runs, otherwise copy the files required by setup/setup_mysql.sh to the MySQL machine and run the script locally. Change the MySQL password of the cdrtool user from file setup/create_users.mysql Then run the database setup script: /var/www/CDRtool/setup/mysql/setup_mysql.sh The script performs the following: 1. It adds to the MySQL server a user for cdrtool software 2. It creates a new MySQL database "cdrtool" 3. It creates a default web user admin/admin, you may use this initial account to login in the web interface 4. It populates the cdrtool database with initial values Apache and PHP setup -------------------- Install PHP apache modele and command line with its dependencies. If you have chosen to install the debian package, the dependencies will be resolved during installation. Configure php both the command line and apache module to support mysql. Configure Apache2 server to support php scripting. Create a virtual host for cdrtool web pages: cp setup/apache2/sites-available/cdrtool.example.com /etc/apache2/sites-available cp setup/apache2/conf.d/cdrtool.conf /etc/apache2/conf-available/ Edit /etc/apache2/sites-available/cdrtool.example.com to suit your environment. Enable the virtual site using: a2ensite cdrtool.example.com a2enconf cdrtool.conf You may enable ssl using "a2enmod ssl" command. If you use ssl you must also generate a site certificate using the instructions provided by apache2 software. Create CDRTool configuration file --------------------------------- cp /var/www/CDRTool/setup/global.inc.simple.sample /etc/cdrtool/global.inc Edit global.inc and setup your variables to match your system. The file global.inc.complex.sample contains a more elaborate example for setting up multiple datasources. You must change at least the following settings: - The RatingEngine IP address - The mysql connection details to cdrtool, radius, opensisps, siptrace and mediaproxy MySQL databases, which are described in the following sections of this document - The mediaDispatcher address Rating engine setup ------------------- The rating engine is used by the prepaid application and as price calculator for postpaid traffic. For prepaid, an external call controll application is required, CDRTool itself provides only the maxsession time and debit balance functions. Call Control can be used to terminate the session based on these functions. For setting up Call Control see PREPAID.txt. Set in global.inc the IP and port where the rating engine listens to: $RatingEngine=array("socketIP" => "IP.address.of.cdrtool.example.com", "socketPort" => "9024", "cdr_source" => "opensips_radius", "allow" => array('10.0.0') ); For non Debian systems you must create /etc/default/cdrtool as follows: RUN_ratingEngine=yes For non Debian systems copy the init script from setup/init.d/cdrtool to /etc/init.d/cdrtool Then restart the rating engine, which you must do every time you made changes to global.inc: /etc/init.d/cdrtool restart To verify that the rating engine has started correctly check syslog for errors. To verify the connection to the rating engine you can telnet to port 9024 and type 'help' command. Insert default sample rates cp /var/www/CDRTool/setup/csv/* /var/spool/cdrtool /var/www/CDRTool/scripts/importRatingTables.php For more information about provisioning of rating tables see doc/RATING.txt. CRON jobs setup --------------- Some operations must be scheduled to run periodically by cron. Such operations are the scripts that block fraudulous accounts in OpenSIPS, normalize the call detail records, purge the old SIP traces. This is done automatically if you install the debian package. For other Linux systems modify and install the cron file from /var/www/CDRTool/setup/crontabs/ Freeradius setup ---------------- CDRTool works with Freeradius server from http://www.freeradius.org OpenSIPS generates START and STOP radius messages when a session is established by INVITE request, 200 OK response combination and respectively terminated by a BYE request, these packets are handled by the Freeradius server, which writes them into the MySQL database. For failed SIP sessions a radius packet type FAILED is generated. A failed SIP session is a session that has been rejected by the Proxy server or by the destination. Unfortunately Freeradius server is not able to cope with FAILED packets. The server must be patched and recompiled if you wish to support accounting for failed SIP sessions. The patches and instructions for how to apply them are found in setup/radius/FreeRadius/ and contrib/freeradius-brandinger/. Install the server from Debian package or your own compiled version after your applied the patches: apt-get install freeradius freeradius-mysql Alternatively you can install the freeradius-xs debian package provided in the AG Projects debian repository that contains all required patches: apt-get install freeradius-xs freeradius-xs-mysql Create the database for radius tables on the MySQL server: mysqladmin -u root create radius Create the RADIUS tables: mysql -u root radius < /var/www/CDRTool/setup/radius/OpenSIPS/radacct.mysql Load the MySQL stored procedures: mysql -u root radius < /var/www/CDRTool/setup/radius/OpenSIPS/radius_accounting.proc Configure Freeradius to use the MySQL procedures: cp /var/www/CDRTool/setup/radius/OpenSIPS/sql.conf /etc/freeradius/sql.conf Add RADIUS clients in clients.conf. Each device sending RADIUS accounting requests must be added to /etc/freeradius/clients.conf. Examples: client 10.0.0.0/8 { secret=DssUET01 nastype=other shortname=localnet } Enable MySQL accounting in FreeRadius server. Edit radius.conf "sql" must be uncommented or added if missing. accounting { detail sql } Copy the radius dictionaries from OpenSIPS and MediaProxy: cp /var/www/CDRTool/setup/radius/OpenSIPS/dictionary.opensips to /etc/freeradius cp /var/www/CDRTool/setup/radius/MediaProxy/dictionary.mediaproxy to /etc/freeradius Edit /etc/freeradius/dictionary as follows: # standard radius dictionary $INCLUDE /usr/share/freeradius/dictionary # OpenSER dictionary $INCLUDE /etc/freeradius/dictionary.opensips # MediaProxy dictionary $INCLUDE /etc/freeradius/dictionary.mediaproxy Grant access to radius user to the radius database, replace PASSWORD and PRIVATE_IP_NETWORK with the real values: GRANT ALL ON radius.* TO radius@'PRIVATE_IP_NETWORK.%' IDENTIFIED by 'PASSWORD'; OpenSIPS setup -------------- Configure OpenSIPS for MySQL storage of subscribers and Radius accounting. For SIP tracing you need to configure the OpenSIPS to enable the siptrace module. You will also need a valid client.conf, you can copy the one in /etc/radiusclient-ng/radiusclient.conf and adapt it to suit your needs To enable quota you must add one extra column in the subscriber table: ALTER TABLE opensips.subscriber ADD COLUMN quota int NOT NULL; You must also create the trusted table if not present in your OpenSIPS setup, the table must have at a minimum the `src_ip` and `reseller_id` columns. Example of a working trusted table: CREATE TABLE `trusted` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `src_ip` varchar(50) NOT NULL, `proto` varchar(4) NOT NULL DEFAULT 'any', `from_pattern` varchar(64) NOT NULL DEFAULT '^sip:.*$', `tag` varchar(32) DEFAULT NULL, `description` varchar(255) NOT NULL DEFAULT '', `change_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `reseller_id` int(10) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `peer_idx` (`src_ip`,`proto`,`from_pattern`,`reseller_id`), KEY `change_date_idx` (`change_date`), KEY `reseller_idx` (`reseller_id`) ); This trusted table must be checked by OpenSIPS when allowing sessions without Digest authentication, for example for SIP trunks from trusted parties. Entries required for CDRTool accounting in opensips.cfg: loadmodule "siptrace.so" loadmodule "acc.so" # global acc parameters modparam("acc", "failed_transaction_flag", 1) modparam("acc", "report_cancels", 0) modparam("acc", "report_ack", 0) modparam("acc", "early_media", 0) modparam("acc", "log_level", 1) modparam("acc", "log_flag", 1) modparam("acc", "log_missed_flag", 1) modparam("acc", "radius_config", "/etc/opensips/radius/client.conf") modparam("acc", "radius_flag", 1) modparam("acc", "radius_missed_flag", 1) modparam("acc", "radius_extra", "User-Name=$Au; \ Calling-Station-Id=$from; \ Called-Station-Id=$to; \ Sip-Translated-Request-URI=$ru; \ Sip-RPid=$avp(s:rpid); \ Source-IP=$avp(s:source_ip); \ Source-Port=$avp(s:source_port); \ SIP-Proxy-IP=$avp(s:sip_proxy_ip); \ Canonical-URI=$avp(s:can_uri); \ Billing-Party=$avp(billing_party); \ Divert-Reason=$avp(s:divert_reason); \ User-Agent=$hdr(user-agent); \ Contact=$hdr(contact); \ Event=$hdr(event); \ ENUM-TLD=$avp(s:enum_tld); \ From-Header=$hdr(from); \ SIP-Application-Type=$avp(s:sip_application_type)") modparam("siptrace", "db_url", "mysql://opensips:password@localhost/opensips") modparam("siptrace", "traced_user_avp", "$avp(s:traced_user)") modparam("siptrace", "trace_on", 1) modparam("siptrace", "trace_flag", 2) Further, you must enable siptrace and accounting in various parts of the routing script by setting the accounting flag and saving the AVP containing the proxy IP address. Copy /var/www/CDRTool/setup/radius/OpenSIPS/dictionary.opensips to /etc/opensips/radius/dictionary Generation of the Canonical-URI It is critical to save $avp(s:can_uri) after the Proxy has performed all possible lookups except DNS. The Canonical-URI will be used for rating the session. Example: route { ... setflag(ACCOUNTING_FLAG); $avp(s:can_uri) = $ru; ... } The Canonical-URI must by synced with the class used to determine the E164 format of the destination. The default behavior of the rating engine is to consider an European dialing plan where a local number starts with a zero and an international number starts with two zeros. See the Chapter 'Determination of the destination id' in RATING.txt for more information. You must also set all the AVPs defined in radius_extra configuration section if you want to have them logged and to appear in CDRTool. MediaProxy setup ---------------- MediaProy can log the IP bandwidth utilization, the codecs and the User Agents. MediaProxy logs this information into the database used by the Freeradius server in radacct table. MediaProxy also corrects the session duration of CDRs for which no BYE messages have been received. See MediaProxy documentation for how to enable Radius and database accounting. MediaProxy logs detailed information about each individual stream (its type, duration, time-out end point ports). The logging is performed in a separate mysql table (default mediaproxy.media_sessions). The MySQL schema is defined in setup/radius/MediaProxy/create_table_media_sessions.mysql. You do not need to create that table, the mediaproxy does it automatically if it has the rights to the configured database. The detailed information about the media streams is displayed in the SIP trace web page, follow the link about media trace. Enable the accounting in MediaProxy /etc/mediaproxy/config.ini: [Dispatcher] accounting = radius,database [Database] dburi = mysql://mediaproxy:password@db/mediaproxy [Radius] config_file = /etc/opensips/radius/client.conf additional_dictionary = radius/dictionary It is possible to retrieve also real-time information about the ongoing sessions and streams, this can be used to plot usage statistics from external applications. The Usage and Sessions web pages display this information. The information is obtained from any mediaproxy dispatcher, which has connections to all configured relays. Create or copy both a media relay certificate and its key to /etc/cdrtool/mediaproxy.hostname.pem: cat /etc/mediaproxy/tls/relay.crt >> /etc/cdrtool/mediaproxy.hostname.pem cat /etc/mediaproxy/tls/relay.key >> /etc/cdrtool/mediaproxy.hostname.pem and set the option: "mediaDispatcher" => "tls:hostname:25061" for each datasource that uses MediaProxy in global.inc. Replace the hostname with the real IP or hostname of the mediaproxy dispatcher. Login accounts -------------- To login to the web interface you must create login accounts. During the initial database the username 'admin' with the password 'admin' has been automatically created. You should change this password after the first time you login. Click on the 'Login' link to add or change existing login accounts. Each account has a set of Permissions that grant the login account with certain right. Also you must specify which datasource may be accessed by a login account. You can experiment with the set of Permissions, their name should be self explanatory. Beware that the 'admin' right simply mean that you can create other login accounts. You still need to select the appropriate permissions for the admin account in order to have access to various functionality controlled by the Permissions system. Asterisk setup -------------- Download from cvs the asterisk-addons and patch asterisk to store CDRs in MySQL. See /var/www/CDRTool/setup/asterisk for more information. Cisco setup ----------- Use the CISCO CLI, enter enable mode and configuration from terminal: >enable config terminal aaa new-model aaa group server radius aaa.router server RADIUS_SERVER_IP auth-port 1812 acct-port 1813 aaa accounting connection h323 start-stop group radius gw-accounting h323 gw-accounting h323 vsa gw-accounting voip Q931 Release codes for Cisco ---------------------------- In CDRTool CISCO hexadecimal release codes are automatically mapped to Q931 release codes. Rating engine ------------- See RATING file. Monitoring ---------- Monit can check if the rating engine stopped working and restart it. A sample configuration file for monit is available in setup/monit/monitrc. Edit the IP address and hostname where the rating engine runs and copy /var/www/CDRTool/setup/monit/monitrc /etc/monit/monit.d/cdrtool Usage statistics ---------------- CDRTool can build usage statistics of the number of online SIP devices from OpenSIPS, simultaneous number of media streams and relayed IP traffic by MediaProxy. To obatin the statistics defined for OpenSIPS datasource the following settings: 'db_registrar' => 'DB_opensips', // must be defined as mysql class 'mediaDispatcher' => 'tls://hostname:25061' The script that gathers the usage information and builds the graphical statistics is /var/www/CDRTool/scripts/buildStatistics.php This script runs as a cron job every 5 minutes. The usage information is cumulated in /tmp/CDRTool-sip-statistics.txt file, which is a plain text file with one line per domain and values separated by tabs. This file can be easily parsed by external scripts to build statistics using other tools like SNMP. To see the graphical statistics, follow the Usage link. Geo-location ------------ CDRs can display geographical location of the source IP that generated the SIP session. To obtain and display this information you must install and configure php5-geoip and geoip-database packages. The debian package contains only country level information. If you want city level information you must download manually the city level database and copy it to /usr/share/GeoIP folder. sudo apt-get install php5-geoip geoip-database wget http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz gunzip GeoLiteCity.dat.gz sudo mv -v GeoLiteCity.dat /usr/share/GeoIP/GeoIPCity.dat Customizing CDRTool ------------------- Most of the functionality of CDRTool can be customized by properly sub-classing the standard provided classes. For example you can modify the way the OpenSIPS data-source works by: 1. Creating in /etc/cdrtool/global.inc a new class that inherits CDRS_opensips, and optionaly CDR_opensips: class CDRS_opensips_custom extends CDRS_opensips { // specify a different CDR class var $CDR_class = 'CDR_opensips_custom'; function x () { } } class CDR_opensips_custom extends CDR_opensips { function y () { } } 2. Adding functions within this new class, with the same name of the functions available in their parent class. For a list of available functions see cdr_generic.php and cdr_opensips.php. By doing this you are overwriting the default behaviour with your own custom functionality. For example you can chage the way the geolocation information is displayed for each CDR line by overwriting the function CDR->lookupGeoLocation(); Datetime columns accuracy ------------------------- By default the session start time, stop time and duration fields have 1 second accuracy. If you require accurancy higher than 1 second see: setup/radius/OpenSIPS/now_usec.readme