That perl script gives me calls with negative duration :), is there any way to differenciate different calls from the acc table? I noticed that the sip_callid is NOT unique to 1 call, a concat(from_tag, to_tag) isnt't unique for 1 call either, this realy makes normalising them a lot harder.
Kind regards,
Erik
Andres wrote:
We wrote a little perl script just to do that. Takes all calls from ACC table and outputs them to CDRS table. Its simple and it works. Its free so use it whatever way you want.
Andres.
Frank Fischer wrote:
Hi all
i'm looking for a free/opensource application that generates CDRs (not XDRs, must not be rated) from the accounting data/events generated by SER (i think this is usually called normalization). I think this would have to be an application that is able to match INVITEs and BYEs and form CDRs with call start timestamp, call duration and so on.
I had a look at the list of accounting software on the iptel.org website, but most of there applications are highly integrated accounting/billing solutions, far more than what i need (and also most of them seem to be commercial products). In the mailinglist archive i only found some references to CDRTool which seems also to be commercial. Obv. there must be a free version of CDRTool but neither i'm sure whether it implements exactly what i need so i could "grab it" for my own purposes nor could i find a place to download the free version).
Does anyone know if there is such a kind of application available? Or any other idea how to deal with my requirements?
Thanks a lot for your help Frank
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers
#!/usr/bin/perl
#Script used to generate SER Billing Records, it will look for the first INVITE Message and match it to a BYE Message #that has the same CALLID #Input is taken from the ACC table #Output is witten to MySQL 'cdrs' table and file '$file_res' #MySQL Perl Module Available at: http://search.cpan.org/author/OYAMA/Net-MySQL-0.08/MySQL.pm
#You can create the cdrs table by putting the following in a file and then feeding it to mysql like this: #mysql -p test < "filename" #-----------cut here-------------------- #CREATE TABLE cdrs ( #timestamp TIMESTAMP, #start_date VARCHAR(18), #end_date VARCHAR(18), #caller VARCHAR(50), #destination VARCHAR(50), #callid VARCHAR(50), #duration INT #); #--------------cut here------------------ #Written by: Ricardo Villa (ricvil @ telesip.net) use Net::MySQL; use Time::Local;
my $mysql = Net::MySQL->new( hostname => 'db.xxxx.net', # Default use UNIX socket database => 'ser', user => 'xxxxxx', password => 'xxxxxx' );
my $mysql2 = Net::MySQL->new( hostname => 'db.xxxx.net', # Default use UNIX socket database => 'xxxxxxx', user => 'xxxxxxx', password => 'xxxxxxx' );
$mysql->query(q{SELECT * FROM acc}); #Get Records from Accounting Table my $record_set = $mysql->create_record_iterator; while (my $record = $record_set->each) { $sip_method = $record->[3]; $username = $record->[9]; $to_uri = $record->[7]; $sip_method = $record->[3]; $sip_callid = $record->[8]; $enddate = $record->[13]; $timestamp = $record->[14]; if ($sip_method eq "BYE") {&bye_match();}; };
&cleanup_acc();
$mysql->close; $mysql2->close; exit; ###---------------------------------------------------------------------------------------------------- ### Subroutines sub bye_match { $bye_query = "SELECT * FROM acc where sip_callid = '$sip_callid';";
$mysql->query( $bye_query );my $record_set2 = $mysql->create_record_iterator; while (my $record2 = $record_set2->each) { $sip_method2 = $record2->[3]; $username2 = $record2->[9]; $to_uri2 = $record2->[7]; $sip_method2 = $record2->[3]; $sip_callid2 = $record2->[8]; $startdate = $record2->[13]; $timestamp2 = $record2->[14]; if (($sip_callid eq $sip_callid2) & ($sip_method2 eq "INVITE")) { &input_processing();
#Convert MySQL Timestamp to Variables $startstamp_year = substr($timestamp2,0,4); $startstamp_month_o = substr($timestamp2,4,2); $startstamp_month = substr($timestamp2,4,2)-1; $startstamp_day = substr($timestamp2,6,2); $startstamp_hour = substr($timestamp2,8,2); $startstamp_minute = substr($timestamp2,10,2); $startstamp_second = substr($timestamp2,12,2); $endstamp_year = substr($timestamp,0,4); $endstamp_month_o = substr($timestamp,4,2); $endstamp_month = substr($timestamp,4,2)-1; $endstamp_day = substr($timestamp,6,2); $endstamp_hour = substr($timestamp,8,2); $endstamp_minute = substr($timestamp,10,2); $endstamp_second = substr($timestamp,12,2); #End Conversion #Convert start and end times to seconds since 1970 $startstamp = timelocal($startstamp_second,$startstamp_minute,$startstamp_hour,$startstamp_day,$startstamp_month,$startstamp_year); $startdate2 = "$startstamp_year-$startstamp_month_o-$startstamp_day $startstamp_hour:$startstamp_minute:$startstamp_second"; $endstamp = timelocal($endstamp_second,$endstamp_minute,$endstamp_hour,$endstamp_day,$endstamp_month,$endstamp_year); $enddate2 = "$endstamp_year-$endstamp_month_o-$endstamp_day $endstamp_hour:$endstamp_minute:$endstamp_second"; $call_duration = $endstamp-$startstamp; #print "Start = $startstamp_year $startstamp_month $startstamp_day $startstamp_hour $startstamp_minute $startstamp_second\n"; &update_table; }; }; };
sub input_processing { ($a_dest, $b_dest) = split(/sip:/,$to_uri2); ($a_dest) = split(/@/,$b_dest); #Isolate the Destination of the Call $to_uri2=$a_dest; }
sub update_table { #Now Insert the Call Detail record into the Database $database_insert = "INSERT INTO cdrs (start_date, end_date, caller, destination, callid, duration) VALUES ('$startdate2', '$enddate2', '$username2', '$to_uri2', '$sip_callid2', $call_duration);"; $mysql2->query( $database_insert ); #and delete from ACC Table $database_delete = "DELETE FROM acc where sip_callid = '$sip_callid2';"; $mysql->query( $database_delete ); }
sub cleanup_acc { #Delete all other garbage from ACC Table $database_delete = "DELETE FROM acc "; $mysql->query( $database_delete ); }
Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers