It seems like your browser didn't download the required fonts. Please revise your security settings and try again.
Barracuda Phone System

Advanced Topic - Understanding CSV and CDR Records

  • Last updated on

This article is for intermediate and advanced users who understand databases and spreadsheets, parsing records and fields, and have some experience with PBX systems and CDRs. The provided information can increase system administrator or analyst understanding of Call Detail Records (CDRs) for Barracuda Phone System.

Terms and Definitions

The following terms are used to describe call records:

  • Call Leg – A connection between an endpoint and the Barracuda Phone System. An endpoint is usually a user telephone, but it can also be a connection to a service provider. 
  • Call – In the strictest sense, a call connects two call legs. However, a call may sometimes just mean a call leg, depending upon the context. In this document, any reference to a call means a connection between two call legs. 
  • A Leg – The calling party's call leg. In most cases, the direction field for an A leg is inbound. (Call transfers can be an exception.) 
  • B Leg – The called party's call leg. In most cases, the direction field for a B leg is outbound. (Call transfers can be an exception.) 
  • UUID – Universally Unique Identifier. This is analogous to a serial number – each call leg has a uuid to distinguish it from all other call legs. 
  • A Leg UUID – Every call has a value in the UUID field, which is the A leg's UUID. A legs are also key fields, so a specific UUID never appears in multiple records. (Note: B leg UUIDs do not have this constraint.) 
  • B Leg UUID – Every answered call between two endpoints yields a second record, usually called the B leg record. A B leg UUID always corresponds to another record A leg UUID. 
  • One-legged Call – A call between an endpoint and a service on the Barracuda Phone System, for example, a user checking  voicemail messages. The call, between a telephone and the Barracuda Phone System, has no second endpoint. A one-legged call never has a bleg_uuid value, or a corresponding CDR. 
  • Attended Transfer – A standard transfer where the user transfers the caller to a new destination, waits for the destination phone to answer, announces the call to the destination and then completes the transfer. 
  • Blind Transfer – A transfer where the user sends a call to another phone without attending the transfer. Some phones do not support blind transfers. CDRs for blind transfers are notoriously problematic. 
  • Cancelled Transfer – A transfer where the user executes an attended transfer, then cancels the operation before the called party can answer. 
  • Hangup Cause – The reason that a call ended. The most common hangup causes are:
    • NORMAL_CLEARING – One party simply hung up the phone.
    • ORIGINATOR_CANCEL – The calling party hung up before the called party answered.
    • CALL_REJECTED – The called party rejected or otherwise did not answer.
  • Call Direction – The direction field of each CDR contains inbound or outbound depending on the call leg direction. The direction field assumes the perspective of the Barracuda Phone System, not the endpoints. The call leg of the originator shows inbound and the call leg of the called party shows outbound.

Sample CDR Spreadsheet

Refer to the sample spreadsheet below for example call records with accompanying explanations. In most cases, a single call example has multiple CDRs. Note that some columns in the sample spreadsheet have been hidden to focus on the relevant data. 

Sample Spreadsheet PDF file

After exporting the CSV file, you can import it into a spreadsheet or database program. Depending upon the amount of traffic on your system, you may need to sort or filter by caller ID number or destination number to more easily isolate specific call records. At the end of this article, see the SQL CREATE TABLE command example you can use with your DBMS, and a Perl script that demonstrates converting a CSV file into a set of SQL INSERT statements. 

Call Types

Different types of calls occur on a PBX. The most common call type is a simple call between two endpoints, for example, internal calls from phone to phone, outbound calls to an external phone number, or inbound calls to a DID number that rings directly to a phone. There are also one-legged calls, for example, a user dials *98 to retrieve his or her voicemail messages. Some calls have many call legs, for example, a call comes into the system and is transferred from phone to phone. 

The Barracuda Phone System creates a CDR for  each call leg . The following table provides examples of the number of CDRs created for typical call types.

Call Type

Example

CDRs

One-legged call

User calls voicemail

1

Two one-legged calls

UserA calls UserB, hangs up before answer

2

Two-party call

UserA calls UserB, UserB answers

2

Two-party call

UserA calls external number, called party answers

2

Transferred call - attended

UserA calls UserB, UserB answers then transfers UserA to UserC

4

One-legged Calls 

Example 1: A user calls voicemail

Below is a partial list of the fields from call #2 in the Sample Spreadsheet above

Field

Value

Start_stamp

6/9/2015 17:03:46

Answer_stamp

6/9/2015 17:03:46

End_stamp

6/9/2015 17:04:04

Caller_id_number

2017

Destination_number

*98

Duration

18

Hangup_cause

NORMAL_CLEARING

Uuid

aea08dad-aa6f-4fba-8c5b-a41e57196e2e

Bleg_uuid

 

Direction

Inbound

The table shows that the user at x2017 dialed *98 at 5:03:46 PM and was connected for 18 seconds. The hangup_cause of NORMAL_CLEARING means that the user hung up the phone after checking messages. The bleg_uuid field is empty, because there is no B leg on this call; it was a one-legged connection between the user and the Barracuda Phone System. 

Example 2: Caller hangs up before call is answered
In call #6 from the Sample Spreadsheet, the user at x2017 called x2001, but hung up before x2001 could answer. Notice that there are two different, but somewhat related call records:

  • a call leg from x2017 to the Barracuda Phone System (inbound)
  • a call leg from Barracuda Phone System to x2001 (outbound)

These two records are shown in the table below.

Field

Caller

Callee

Start_stamp

6/9/2015 18:17:04

6/9/2015 18:17:04

Answer_stamp

 

 

End_stamp

6/9/2015 18:17:10

6/9/2015 18:17:10

Caller_id_number

2017

2017

Destination_number

2001

2001

Duration

0

0

Hangup_cause

ORIGINATOR_CANCEL

ORIGINATOR_CANCEL

Uuid

5e71880f-3628-4194-ab54-e163711ce857

bb7e3ea8-4486-4133-8f8d-70a44d34a0a2

Bleg_uuid

 

 

Direction

Inbound

Outbound

Every field is identical except the UUID and direction fields. Each call leg is distinct and therefore has its own UUID field. The direction of each call leg differs, based on the perspective of the Barracuda Phone System. The direction of the caller is inbound to the Barracuda Phone System and the direction of the callee is outbound to the Barracuda Phone System. Since the UUID values do not correlate, the only way to associate these two records is by noting what they have in common:

  • The start and end times are identical, AND
  • There is no answer time, AND
  • The duration is 0, AND
  • The caller_id_number fields match, AND
  • The destination_number fields match, AND
  • The hangup_cause values for each record are ORIGINATOR_CANCEL

Any pair of records matching the above criteria can be assumed to be a call from party A (caller_id_number) to party B (destination_number) where party A hung up before party B answered. 

Two-party Calls 

Example 3: A call between two end points

Call #3 in the Sample Spreadsheet shows a call with two end points, the UUID from one leg is the bleg_uuid in the other.  

Field

Caller

Callee

Start_stamp

6/9/2015 16:46:51

6/9/2015 16:46:51

Answer_stamp

6/9/2015 16:46:52

6/9/2015 16:46:52

End_stamp

6/9/2015 16:47:03

6/9/2015 16:47:03

Caller_id_number

2017

2007

Destination_number

2017

2007

Billsec

11

11

Duration

12

12

Hangup_cause

NORMAL_CLEARING

NORMAL_CLEARING

Uuid

cb1465b3-5c28-43b5-bcfa-a7a4a6ed2c79

32ca2613-428e-4b41-bd3c-f58cd59f9a97

Bleg_uuid

32ca2613-428e-4b41-bd3c-f58cd59f9a97

cb1465b3-5c28-43b5-bcfa-a7a4a6ed2c79

Direction

Inbound

Outbound

The table shows that the user at x2017 called x2007. The call rang for one second and then the user at x2007 answered. They conversed for 11 seconds and then hung up. Notice the UUID pair for this two-legged call:

  • Caller UUID appears as bleg_uuid in exactly one callee's CDR, AND
  • Callee UUID appears as bleg_uuid in exactly one caller's CDR

If the caller's UUID  appears in more than one record as a bleg_uuid, then this is likely a blind or attended transfer. 

Transferred Calls 

Locating call transfers can be challenging. The SIP protocol is not explicit about call transfer attempts. Successfully executed attended transfers are the easiest to locate. Blind transfers are difficult and require assumptions (see call #8 in the Sample Spreadsheet). Cancelled attended transfers are also difficult (see call #9 in the Sample Spreadsheet ).

Example 4: Attended call transfer

Call #1 in the Sample Spreadsheet shows an attended transfer, where the user at x1001 calls x2007. The user at x2007 answers, and then does an attended transfer to x2015. The user at x1001 speaks to the user at x2015, then the parties hang up. 

Step 1: User at x1001 calls x2007

Field

Caller

Callee

Start_stamp

6/9/2015 17:11:47

6/9/2015 17:11:47

Answer_stamp

6/9/2015 17:11:49

6/9/2015 17:11:49

End_stamp

6/9/2015 17:12:09

6/9/2015 17:12:03

Caller_id_number

1001

2007

Destination_number

1001

2007

Billsec

20

14

Duration

22

16

Hangup_cause

NORMAL_CLEARING

NORMAL_CLEARING

Uuid

472f54fb-b118-446f-8f57-ea462a5879ea

fc98263c-c5e3-4f96-89a7-eca08330d8e8

Bleg_uuid

dcdee45b-921e-4dbf-806e-bee6e2ea5cc0

472f54fb-b118-446f-8f57-ea462a5879ea

Direction

Inbound

Outbound


Step 2: User at x2007 transfers the call to x2015

Field

Caller

Callee

Start_stamp

6/9/2015 17:11:59

6/9/2015 17:11:59

Answer_stamp

6/9/2015 17:12:02

6/9/2015 17:12:02

End_stamp

6/9/2015 17:12:03

6/9/2015 17:12:09

Caller_id_number

2007

2015

Destination_number

2015

2015

Billsec

1

7

Duration

4

10

Hangup_cause

NORMAL_CLEARING

NORMAL_CLEARING

Uuid

dd76d0b2-3404-430d-8325-829b05d450ae

fc98263c-c5e3-4f96-89a7-eca08330d8e8

Bleg_uuid

fc98263c-c5e3-4f96-89a7-eca08330d8e8

472f54fb-b118-446f-8f57-ea462a5879ea

Direction

Inbound

Outbound


The key to understanding the records of an attended transfer is identifying a single record as the master leg record and correlating it to a set of related records. The master leg always has the longest duration, because it represents the party transferred from one phone to the next. The transferred party call leg stays constant throughout the entire call. However, the person executing the transfer, and the recipient of the transfer, only speak to the transferred party for a portion of the call. This means that all CDRs related to the master CDR have a shorter duration. Note that the master CDR has a direction of inbound if the transferred party is a caller, but has a direction of outbound if the transferred party is a callee.

  • Master CDR is Outbound – User A calls User B. User B (callee) transfers call to User C.
  • Master CDR is Inbound User A calls User B. User A (caller) transfers call to User C .

The master record in the example above is the caller with UUID of 472f54fb-b118-446f-8f57-ea462a5879ea. The total duration of the call is 22 seconds, 20 spent talking to 2007 and 2015, and a few seconds 1001 was on hold while 2007 executed the transfer. You can tell that 2007 transferred the call to 2015 because it is the only other inbound call in the set of CDRs. In an attended transfer, any inbound CDR other than the master CDR indicates an attended transfer took place.

The outbound CDRs that are not the master record represent parties with whom the master leg spoke.

In Example 4, 1001 is the master because 1001 was transferred from one user to another.

  • The first step of the call was 1001 speaking to 2007. The CDRs indicate that 2007 rang for two seconds and that 2007 spoke to 1001 for an additional 14 seconds. 
    • (Note CDR with uuid of dcdee45b-921e-4dbf-806e-bee6e2ea5cc0.) 
  • 2007 then sent the call to 2015. 
    • (Note CDR with uuid of dd76d0b2-3404-430d-8325-829b05d450ae.) 
  • The call from 2007 to 2015 rang for three seconds, and after 2015 answered, it took one second for 2007 to complete the transfer. 
  • From there, 1001 spoke to 2015 for seven seconds. 
    • (Note CDR with uuid of fc98263c-c5e3-4f96-89a7-eca08330d8e8.) 

 

Rules of thumb for calculating duration:

  • MasterDuration = (sum of inbound durations) – (sum of outbound durations)
  • MasterBillsec = (sum of inbound billsecs) – (sum of outbound billsecs) 

In the example above:

  • MasterDuration = (16+10) – (4) = 22 seconds
  • MasterBillsec = (14+7) – (1) = 20 seconds 

To programatically find the master record along with its related records:

  1. Identify the master record, using the following criteria:
    • A master record UUID must appear in 2 or more other CDRs as the bleg_uuid
    • In most cases, the master record CDR also has a UUID  that appears in multiple CDRs as the bleg_uuid
    • The master record is the record with the longest duration. 
  2. Select any record whose bleg_uuid is the master UUID. This includes the paired CDR. This yields a list of CDRs with unique UUID s
  3. For each uuid in the list of uuids from the previous step, look for any CDRs whose bleg_uuid is the same as the UUID . This yields another set of UUID s

Combine the lists and sort them ascending by start_stamp and then ascending by direction and you have a complete group of related CDRs in an attended transfer. 

SQL Information 

Following is an SQL CREATE TABLE statement suitable for a PostgreSQL database that  can be used as a reference for MySQL, SQLite, MS Access and others. 

CREATE TABLE cdrs

(

  id bigserial       NOT NULL,

  start_timestamp    timestamp without time zone,

  answer_timestamp   timestamp without time zone,

  end_timestamp      timestamp without time zone,

  caller_id_name     character varying(255) NOT NULL DEFAULT ''::character varying,

  caller_id_number   character varying(255) NOT NULL DEFAULT ''::character varying,

  destination_name   character varying(255),

  destination_number character varying(255) NOT NULL DEFAULT ''::character varying,

  accountcode        character varying(255) NOT NULL DEFAULT ''::character varying,

  billsec bigint     NOT NULL DEFAULT 0,

  duration bigint    NOT NULL DEFAULT 0,

  hangup_cause       character varying(255) NOT NULL DEFAULT ''::character varying,

  gateway_name       character varying(255),

  outbound_route     character varying(255),

  network_addr       character varying(32),

  read_codec         character varying(16),

  read_rate          character varying(16),

  write_codec        character varying(16),

  write_rate         character varying(16),

  context            character varying(255) NOT NULL DEFAULT ''::character varying,

  uuid               character varying(255) NOT NULL DEFAULT ''::character varying,

  bleg_uuid          character varying(255) NOT NULL DEFAULT ''::character varying,

  direction          character varying(16)  NOT NULL,

  record_file_name   character varying(255),

  "between"          character varying(255),

 

  CONSTRAINT pk_cdr PRIMARY KEY (id),

  CONSTRAINT unique_uuid UNIQUE (uuid)

)

WITH (

  OIDS=FALSE

);

ALTER TABLE cdrs OWNER TO postgres;

 

CSV2SQL Script

The following pure Perl script converts a downloaded CSV file into a set of SQL INSERT statements. These are suitable for any SQL-compliant RDBMS. 

#!/usr/bin/perl                                                                                                                                                

#                                                                                                                                                               

# csv2sql.pl                                                                                                                                                    

#                                                                                                                                                              

# Creates SQL INSERT statements from CSV data using header row as field names                                                                                  

# Requires 2 cmd line args: filename.csv and table name                                                                                                        

# Example:                                                                                                                                                     

#  ./csv2sql.pl myfile.csv my_table                                                                                                                            

#                                                                                                                                                              

# produces discrete insert lines that can be piped into your sql client:                                                                                       

#                                                                                                                                                              

#  INSERT INTO my_table (col1,col2,col3) VALUES('value1','value2','value3');                                                                                   

#                                                                                                                                                              

# Pure Perl, no CPAN modules necessary                                                                                                                         

#                                                                                                                                                              

                                                                                                                                                                

use strict;                                                                                                                                                     

use warnings;                                                                                                                                                  

use Text::ParseWords;                                                                                                                                           

 

## Perl Cookbook, Recipe 1.20                                                                                                                                  

sub parse_csv {                                                                                                                                                

    return quotewords("," => 0, $_[0]);                                                                                                                         

  }                                                                                                                                                            

                                                                                                                                                                

sub usage {                                                                                                                                                     

  die "\nUsage: $0 CSV_Filename Table_name\n";                                                                                                                 

}                                                                                                                                                                                                                                                                                                                      

## Basic error checking on cmd line args                                                                                                                       

my $csvfile = shift;                                                                                                                                            

if ( ! $csvfile ) {                                                                                                                                            

  usage;                                                                                                                                                        

}                                                                                                                                                               

if ( ! -f $csvfile ) {                                                                                                                                         

  die "Could not locate $csvfile\n";                                                                                                                            

}                                                                                                                                                               

my $table_name = shift;                                                                                                                                         

if ( ! $table_name ) {                                                                                                                                         

  die "Both dbname and table name are required\n";                                                                                                             

}                                                                                                                                                                                                                                                                                                                             

open (CSV,'<',$csvfile) or die "Could not open $csvfile: $!\n";                                                                                                

my $header_row = ;                                                                                                                                         

chomp($header_row);                                                                                                                                            

my @hdr = parse_csv($header_row);                                                                                                                               

                                                                                                                                                               

## This is the same for each SQL statement we build...                                                                                                         

my $sql = "INSERT INTO $table_name (";                                                                                                                                                                                                                                                                                  

while() {                                                                                                                                                  

  chomp;                                                                                                                                                        

  my @columns;                                                                                                                                                 

  my @dataout;                                                                                                                                                  

  my @data = parse_csv($_);                                                                                                                                     

  @data = map { "'$_'"; } @data; ## Wrap each field in single quotes                                                                                           

                                                                                                                                                                

  ## Some DBMS's do not respond well to inserting a NULL field in a field that allows NULL data...                                    

  foreach my $i (0..$#hdr) {                                                                                                                                   

      if ( $data[$i] ne "''" ) {                                                                                                                                

          ## This field seems to have data, so let's add it                                                                                                    

          push @columns,$hdr[$i];                                                                                                                               

          push @dataout,$data[$i];                                                                                                                              

      }                                                                                                                                                        

  }                                                                                                                                                             

  my $sqlout = $sql;                                                                                                                                           

  $sqlout .= join ',',@columns;                                                                                                                                

  $sqlout .= ') VALUES(';                                                                                                                                       

  $sqlout .= join ',',@dataout;                                                                                                                                

  $sqlout .= ');';                                                                                                                                              

  print $sqlout . "\n";                                                                                                                                         

}                                                                                                                                                              

close(CSV);