[SR-Users] Simple Call Accounting

[Graham Wooden]

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