Siremis v1.x - Setup Accounting Services

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.

Changes in file kamailio.cfg

• make sure acc is configured with MySQL support
• set db_extra parameter

      modparam("acc", "db_extra",
        "src_user=$fU;src_domain=$fd;dst_user=$rU;dst_domain=$rd;src_ip=$si")

Delete acc and missed_calls tables created by default and replace them using following definitions:

     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`)
     );
 

Create CDRS table:

     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`)
     );

Create the stored procedure to be used to aggregate START and STOP accounting events:

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 ;

You should schedule a job in cron.d to run the stored procedure every minute.

Do not forget to restart Kamailio SIP server.