siremis:install:accounting

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

siremis:install:accounting [2010/10/31 13:59] (current)
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
 +<​code>​
 +      modparam("​acc",​ "​db_extra",​
 +        "​src_user=$fU;​src_domain=$fd;​dst_user=$rU;​dst_domain=$rd;​src_ip=$si"​)
 +</​code>​
 +
 +===== Database =====
 +
 +Delete acc and missed_calls tables created by default and replace them using following definitions:​
 +
 +<code sql>
 +     ​CREATE TABLE `acc` (
 +        `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 '​0000-00-00 00:​00:​00',​
 +        `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 '​0',​
 +        INDEX acc_callid (`callid`),
 +        PRIMARY KEY  (`id`)
 +     );
 +     
 +     ​CREATE TABLE `missed_calls` (
 +        `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 '​0000-00-00 00:​00:​00',​
 +        `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 '​0',​
 +        INDEX mc_callid (`callid`),
 +        PRIMARY KEY  (`id`)
 +     );
 +     
 +</​code>​
 +
 +Create CDRS table:
 +<code sql>
 +     ​CREATE TABLE `cdrs` (
 +        `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 '​0000-00-00 00:​00:​00',​
 +        `duration` int(10) unsigned NOT NULL default '​0',​
 +        `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`,​`sip_from_tag`,​`sip_to_tag`)
 +     );
 +</​code>​
 +
 +==== 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,​v_src_domain,​v_dst_user,​v_dst_domain,​v_callid,​v_from_tag,​
 +     ​v_to_tag,​v_src_ip VARCHAR(64);​
 +  DECLARE v_inv_time, v_bye_time DATETIME;
 +  DECLARE inv_cursor CURSOR FOR SELECT src_user, src_domain, dst_user,
 +     ​dst_domain,​ time, callid,​from_tag,​ to_tag, src_ip FROM openser.acc
 +     where method='​INVITE'​ and cdr_id='​0';​
 +  DECLARE CONTINUE HANDLER FOR SQLSTATE '​02000'​ SET done = 1;
 +  OPEN inv_cursor;
 +  REPEAT
 +    FETCH inv_cursor INTO v_src_user, v_src_domain,​ v_dst_user, v_dst_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
 +                 ​method='​BYE'​ AND callid=v_callid AND ((from_tag=v_from_tag
 +                 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,​src_domain,​dst_username,​
 +                 ​dst_domain,​call_start_time,​duration,​sip_call_id,​sip_from_tag,​
 +                 ​sip_to_tag,​src_ip,​created) VALUES (v_src_user,​v_src_domain,​
 +                 ​v_dst_user,​v_dst_domain,​v_inv_time,​
 +                 ​UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),​
 +                 ​v_callid,​v_from_tag,​v_to_tag,​v_src_ip,​NOW());​
 +        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 ;
 +</​code>​
 +
 +You should schedule a job in cron.d to run the stored procedure every minute.
 +
 +Do not forget to restart Kamailio SIP server.
 +
 +{{tag>​siremis}}


Copyright 2010-2020 Asipto.com