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.
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:
- 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.
- 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.
- 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);