Panasonic TD1232 SMDR Log Parser

Modified to parse the SMDR output from the Panasonic TD1232 Digital Super Hybrid Phone System, it requires a computer attached via null-modem to the serial port on the main system unit.

Note that SMDR and TAPI are apparently mutually exclusive. (Although apparently most of the SMDR information can be retrieved via TAPI as well.)

Download the phone_logger.pl perl script.


Sample Call Log:

  Date     Time   Ext CO     Dial Number       ANS  Duration Cost:$     Acc  CD

--------------------------------------------------------------------------------

01/11/04 04:09PM  940 32 <I>0883333333        0'00  00:05'11    0.000        TR

01/11/04 04:10PM  201 31 141483333333               00:00'36    0.000

Source code for phone_logger.pl (Note that due to re-formatting for the web this may be broken)

#!/usr/bin/perl
# Phone Logger for KX-TD1232, parses input from the serial port and inserts into a 
# mysql database. Munged together from the call logger code made by Glenn Powers 
# for the XRMS project.
# Andrew Keynes <ak@saint.net.au>
#
# Original Purpose
# -----------------------------------------------------------------------------------
# Phone Logger for XRMS -  interfaces XRMS with the Call Detail Records from a
# Panasonic KX-TD816 Digital Super Hybrid Phone System. It will probably also
# work with the KX-TD1232.
# -----------------------------------------------------------------------------------
# Original script can be found in the /plugins/cti/ directory of the xrms project.

# copyright 2004 Glenn Powers <glenn@net127.com>
# Licensed Under the Open Software License v. 2.0

# default area code (prepended to 8-digit numbers)
my $area_code = "08" ;
my $dial_prefix = "1414" ;

use DBI;

# open the serial port
open(SERIAL,"</dev/ttyS1");

while($line=<SERIAL>) {

# open the log file
open(LOG,">>/var/log/phone.log");

# print line to log
print LOG "$line";

#close the log file
close(LOG);

# remove trailing newline
chop $line;

# skip headers
if ($line =~ /^\-+$/) {
    next;
};

# Columns as follows:
#	Date (DD/MM/YY)
#	Time (HH:mm:AM/PM)
#	Ext (918, 964 etc) - Internal Phone Extension
#	CO (Call Out line?) -
#	Dial Number (<I> indicates an incoming call,
#	All outgoing calls have the 1414 prefix)
#	ANS (Time taken to answer the call)
#	Duration (Duration of the call)
#	Cost:$ (If system setup for phone accounting,
#	the cost of the call)
#	Acc (6 digit accounting code?) Accounting Code
#	CD (TR = Call was tranferred,
#	FW = Extension number was setup to fowrard to
#	an external number, RM = Call accessed the
#	remote maintenance modem, NA = No Answer?)
if ($line =~ /^  Date     Time   Ext CO     Dial Number       ANS  Duration Cost\:\$     Acc  CD/) {
    next
};

if ($line =~ /^AT/) {
    next
};

if ($line =~ /^$/) {
    next
};

# split up and re-arrange the input line
#	Group 1, 2 & 3 - Rearrange to form a standard YYYY-MM-DD
#	Group 4 - HH
#	Group 5 - MM
#	Group 6 - AM/PM
#	Group 7 - Extension
#	Group 8 - CO
#	Group 9 - Phone Number
#	Group 10 & 11 - Rearrange to form HHMMSS suitable for a mysql TIME column
#	Group 12, 13 & 14 - Rearrange to form HHMMSS suitable for a mysql TIME column
#	Group 15 - Call cost
#	Group 16 - Accounting Code
#	Group 17 - Call Code?
$line=~s/^(\d\d)\/(\d\d)\/(\d\d) (\d\d)\:(\d\d)(\w\w)  (...) (\d\d)
	(....................) (.)(.)(..)  (..).(..).(..) (........) (......) (..)
	/20$3-$2-$1,$4,$5,$6,$7,$8,$9,$10$12,$13$14$15,$16,$17,$18/;

($date,$hh,$mm,$apm,$ext,$co,$dial,$answer,$duration,$cost,$acc,$cd)=split(",",$line);

# Convert to 24-hr time
if ($apm =~/PM/) {
    if ($hh < 12) {
        $hh=$hh+12;
    };
};

$time = "$hh:$mm";

# determine activity_type_id
if ($dial =~ /(\<I\>)/) {
    $dial =~ s/(\<I\>)//g ;
    $activity_type_id = 2; # "Call From"
} elsif ($dial =~ /^($dial_prefix)(................)$/) {
    $dial =~ s/^($dial_prefix)(................)$/$2/g ;
    $activity_type_id = 1; # "Call To"
} else {
    $activity_type_id = 1; # "Call To"
};

# remove spaces from dialed number, answer time, cost, account code and cd
$dial =~ s/ +//g ;
$answer =~ s/ +//g ;
$cost =~ s/ +//g ;
$acc =~ s/ +//g ;
$cd =~ s/ +//g ;

# format answer time as HHMMSS
if($answer =~ /(\d\d\d)/) {
    $answer = "000" . $answer ;
}

# add the area code to phone number if required
if($dial =~ /^(\d{8})$/) {
    $dial = $area_code . $dial ;
}

# make insert statment
$insert = "INSERT INTO `phone_system_log` ( 
	`CallID`, 
	`Date`, 
	`Extension`, 
	`CO`, 
	`Phone_Number`, 
	`Answer_Time`, 
	`Duration`, 
	`Cost`, 
	`Account_Code`, 
	`CD`, 
	`Activity_Type` ) VALUES ( 
	'', 
	'" . $date_time . "', 
	'" . $ext . "', 
	'" . $co . "', 
	'" . $dial . "', 
	'" . $answer . "', 
	'" . $duration . "', 
	'" . $cost . "', 
	'" . $acc . "', 
	'" . $cd . "', 
	'" . $activity_type_id . "' );" ;

# open database connection
$dbh = DBI->connect('DBI:mysql:database:127.0.0.1', 'username','password') 
	or die $DBI::errstr;

# prepare the insert statement
$cursor = $dbh->prepare($insert);

# do the insert
$cursor->execute;

# close the database connection
$dbh->disconnect;

# close the loop
};
close(SERIAL);

MySQL table structure

--
-- Table structure for table `phone_system_log`
--

CREATE TABLE phone_system_log (
  CallID int(11) NOT NULL auto_increment,
  Date datetime NOT NULL default '0000-00-00 00:00:00',
  Extension char(3) NOT NULL default '',
  CO char(2) NOT NULL default '',
  Phone_Number varchar(20) default NULL,
  Answer_Time time default NULL,
  Duration time NOT NULL default '00:00:00',
  Cost varchar(8) default NULL,
  Account_Code varchar(6) default NULL,
  CD char(2) default NULL,
  Activity_Type char(1) default NULL,
  PRIMARY KEY  (CallID)
) TYPE=MyISAM;