[Serusers] Creating CDRs from SERs accouning records

[Erik]

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 at 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 at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers