This shows you the differences between two versions of the page.
— | siremis:install:accounting [2010/10/31 13:59] (current) – created - external edit 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
+ | ====== Siremis v1.x - Setup Accounting Services ====== | ||
+ | ===== Accounting Panel ===== | ||
+ | |||
+ | Kamailio does event-based accounting, storing the START and STOP events for each call. By default the SIP server stores a very limited number of details for each event. Therefore you have to extend the tables to store the information you need. | ||
+ | |||
+ | ===== Config file ===== | ||
+ | |||
+ | Changes in file kamailio.cfg | ||
+ | * make sure acc is configured with MySQL support | ||
+ | * set db_extra parameter | ||
+ | < | ||
+ | modparam(" | ||
+ | " | ||
+ | </ | ||
+ | |||
+ | ===== Database ===== | ||
+ | |||
+ | Delete acc and missed_calls tables created by default and replace them using following definitions: | ||
+ | |||
+ | <code sql> | ||
+ | | ||
+ | `id` int(10) unsigned NOT NULL auto_increment, | ||
+ | `method` varchar(16) NOT NULL default '', | ||
+ | `from_tag` varchar(64) NOT NULL default '', | ||
+ | `to_tag` varchar(64) NOT NULL default '', | ||
+ | `callid` varchar(128) NOT NULL default '', | ||
+ | `sip_code` char(3) NOT NULL default '', | ||
+ | `sip_reason` varchar(32) NOT NULL default '', | ||
+ | `time` datetime NOT NULL default ' | ||
+ | `src_ip` varchar(64) NOT NULL default '', | ||
+ | `dst_user` varchar(64) NOT NULL default '', | ||
+ | `dst_domain` varchar(128) NOT NULL default '', | ||
+ | `src_user` varchar(64) NOT NULL default '', | ||
+ | `src_domain` varchar(128) NOT NULL default '', | ||
+ | `cdr_id` integer NOT NULL default ' | ||
+ | INDEX acc_callid (`callid`), | ||
+ | PRIMARY KEY (`id`) | ||
+ | ); | ||
+ | |||
+ | | ||
+ | `id` int(10) unsigned NOT NULL auto_increment, | ||
+ | `method` varchar(16) NOT NULL default '', | ||
+ | `from_tag` varchar(64) NOT NULL default '', | ||
+ | `to_tag` varchar(64) NOT NULL default '', | ||
+ | `callid` varchar(128) NOT NULL default '', | ||
+ | `sip_code` char(3) NOT NULL default '', | ||
+ | `sip_reason` varchar(32) NOT NULL default '', | ||
+ | `time` datetime NOT NULL default ' | ||
+ | `src_ip` varchar(64) NOT NULL default '', | ||
+ | `dst_user` varchar(64) NOT NULL default '', | ||
+ | `dst_domain` varchar(128) NOT NULL default '', | ||
+ | `src_user` varchar(64) NOT NULL default '', | ||
+ | `src_domain` varchar(128) NOT NULL default '', | ||
+ | `cdr_id` integer NOT NULL default ' | ||
+ | INDEX mc_callid (`callid`), | ||
+ | PRIMARY KEY (`id`) | ||
+ | ); | ||
+ | |||
+ | </ | ||
+ | |||
+ | Create CDRS table: | ||
+ | <code sql> | ||
+ | | ||
+ | `cdr_id` bigint(20) NOT NULL auto_increment, | ||
+ | `src_username` varchar(64) NOT NULL default '', | ||
+ | `src_domain` varchar(128) NOT NULL default '', | ||
+ | `dst_username` varchar(64) NOT NULL default '', | ||
+ | `dst_domain` varchar(128) NOT NULL default '', | ||
+ | `call_start_time` datetime NOT NULL default ' | ||
+ | `duration` int(10) unsigned NOT NULL default ' | ||
+ | `sip_call_id` varchar(128) NOT NULL default '', | ||
+ | `sip_from_tag` varchar(128) NOT NULL default '', | ||
+ | `sip_to_tag` varchar(128) NOT NULL default '', | ||
+ | `src_ip` varchar(64) NOT NULL default '', | ||
+ | `created` datetime NOT NULL, | ||
+ | PRIMARY KEY (`cdr_id`), | ||
+ | UNIQUE KEY `uk_cft` (`sip_call_id`, | ||
+ | ); | ||
+ | </ | ||
+ | |||
+ | ==== Stored procedure ==== | ||
+ | |||
+ | Create the stored procedure to be used to aggregate START and STOP accounting events: | ||
+ | <code sql> | ||
+ | DELIMITER // | ||
+ | CREATE PROCEDURE `kamailio_cdrs`() | ||
+ | BEGIN | ||
+ | DECLARE done INT DEFAULT 0; | ||
+ | DECLARE bye_record INT DEFAULT 0; | ||
+ | DECLARE v_src_user, | ||
+ | | ||
+ | DECLARE v_inv_time, v_bye_time DATETIME; | ||
+ | DECLARE inv_cursor CURSOR FOR SELECT src_user, src_domain, dst_user, | ||
+ | | ||
+ | where method=' | ||
+ | DECLARE CONTINUE HANDLER FOR SQLSTATE ' | ||
+ | OPEN inv_cursor; | ||
+ | REPEAT | ||
+ | FETCH inv_cursor INTO v_src_user, v_src_domain, | ||
+ | v_inv_time, v_callid, v_from_tag, v_to_tag, v_src_ip; | ||
+ | IF NOT done THEN | ||
+ | SET bye_record = 0; | ||
+ | SELECT 1, time INTO bye_record, v_bye_time FROM openser.acc WHERE | ||
+ | | ||
+ | AND to_tag=v_to_tag) | ||
+ | OR (from_tag=v_to_tag AND to_tag=v_from_tag)) | ||
+ | ORDER BY time ASC LIMIT 1; | ||
+ | IF bye_record = 1 THEN | ||
+ | INSERT INTO openser.cdrs (src_username, | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid | ||
+ | AND from_tag=v_from_tag AND to_tag=v_to_tag; | ||
+ | END IF; | ||
+ | SET done = 0; | ||
+ | END IF; | ||
+ | UNTIL done END REPEAT; | ||
+ | END | ||
+ | // | ||
+ | DELIMITER ; | ||
+ | </ | ||
+ | |||
+ | You should schedule a job in cron.d to run the stored procedure every minute. | ||
+ | |||
+ | Do not forget to restart Kamailio SIP server. | ||
+ | |||
+ | {{tag> |