[SR-Users] Simple Call Accounting

Hi there, I wanted to share with the folks on this list a simple way to get some simple call accounting. My goal was to have a simple, one line in a DB entry, depicting when the call started (epoch and date/time), when the call ended (epoch and date/time), the call-to number and the calling-from, and finally the direction of the call. I found that ACC was too buggy with this - meaning, that about 1/2 of the time the acc_extra and acc_to would actually be correct & it had a bunch of extra stuff that I really didn't need or care about. Maybe the buggy-ness is because I am running kamailio 1.4.2 - ? I used CDRTool and Radius a while back, on my first production machine, but after 2 years of service I had to fail over to my secondary and could never get that combination going again. I didn't keep good notes when I struggled to get it working the first time. So, with that - for the folks out there that want a simple, no thrills call accounting, from which you can add and tailor to your environment - here is what I did: First, create a table in your openser database. For this example, I called it "rawlines". Here is the mySQL schema: +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | timenow | datetime | NO | | NULL | | | epoch | varchar(10) | NO | | NULL | | | endtimenow | datetime | NO | | NULL | | | endepoch | varchar(10) | NO | | NULL | | | callid | varchar(64) | NO | MUL | NULL | | | thisside | varchar(28) | NO | | NULL | | | otherside | varchar(28) | NO | | NULL | | | direction | varchar(8) | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+ Then, the placement the SQL calls. Of course, everyone's cfg file is different, as well as you may have a better place inside ... Here is what I am doing: route{ . . if (is_method("BYE")) { . . avp_db_query("UPDATE rawlines set endtimenow=NOW(), endepoch='$Ts' WHERE callid='$ci'"); . . } . . if (!lookup("location")) { . . avp_db_query("INSERT INTO rawlines (timenow,epoch,callid,thisside,otherside,direction) VALUES(NOW(),'$Ts','$ci','$fU','$rU','outbound')"); . . } else { avp_db_query("INSERT INTO rawlines (timenow,epoch,callid,thisside,otherside,direction) VALUES(NOW(),'$Ts','$ci','$tU','$Au','inbound')"); . . } That's pretty much it. Your results will be a single, readable line for each call. Now, is this perfect - heck no. For instance, for calls that don't complete (like just ring/hangup with no BYE), you will get 0000-00-00 for "endtimenow" which could be misleading thinking the call is still happening. I like depicting both epoch and readable date/time. I use the epoch to calculate billing time and use the readable for quick reference for users. My main goal was to get something quick and friendly out there to start expanding on. Fits my needs currently and maybe it will help benefit someone else out there - or better yet, come with a better way to achieve this approach. -graham