no way to compare when less than two revisions

Differences

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

@@ 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}}

Asipto - SIP and VoIP Knowledge Base Site

User Tools

Site Tools

Differences

Page Tools