How do I log CDR / SMDR Data to SQL for a Definity G3

For this I use SerialLogger v121 ($39.99US from http://www.muconsulting.com/)

1. Set up the definity CDR data to output "customized" data.

2. I chose the following columns to output, and left the data size default:

1: acct-code - 15
2: attd-console - 2
3: bandwidth - 2
4: bcc - 1
5: calling-num - 15
6: clg-num/in-tac - 15
7: code-dial - 4
8: code-used - 4
9: cond-code - 1
10: date - 6
11: dialed-num - 23
12: duration - 4
13: feat-flag - 1
14: frl - 1
15: in-crt-id - 3
16: in-trk-code - 4
17: ins - 3
18: auth-code - 13
19: ixc-code - 4
20: ma-uui - 1
21: node-num - 2
22: out-crt-id - 3
23: ppm - 5
24: res_flag - 1
25: sec-dur - 5
26: time - 4
27: tsc_ct - 4
28: tsc_flag - 1
29: return - 1
30: line-feed - 1

3. Set up a table with the following DDL:

ID decimal(11,0) IDENTITY,
date varchar(6) NULL,
time varchar(4) NULL,
sec_dur varchar(5) NULL,
calling_num varchar(15) NULL,
dialed_num varchar(23) NULL,
clg_num_in_tac varchar(15) NULL,
bandwidth varchar(2) NULL,
duration varchar(4) NULL,
bcc varchar(1) NULL,
frl varchar(1) NULL,
ins varchar(3) NULL,
ppm varchar(5) NULL,
res_flag varchar(1) NULL,
tsc_ct varchar(4) NULL,
tsc_flag varchar(1) NULL,
acct_code varchar(15) NULL,
attd_console varchar(2) NULL,
code_dial varchar(4) NULL,
code_used varchar(4) NULL,
cond_code varchar(1) NULL,
feat_flag varchar(1) NULL,
in_crt_id varchar(3) NULL,
in_trk_code varchar(4) NULL,
auth_code varchar(13) NULL,
ixc_code varchar(4) NULL,
ma_uui varchar(1) NULL,
node_num varchar(2) NULL,
out_crt_id varchar(3) NULL,
unformatted varchar(400) NULL,
date_logged datetime DEFAULT getdate() NOT NULL

4. Create a stored procedure that looks something like this:

create procedure dbo.CSP_CALL_LOG @unformatted varchar(4000)

as
begin
set TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
set nocount on

declare
@acct_code varchar(15),
@attd_console varchar(2),
@bandwidth varchar(2),
@bcc varchar(1),
@date varchar(6),
@duration varchar(4),
@frl varchar(1),
@ins varchar(3),
@ppm varchar(5),
@res_flag varchar(1),
@time varchar(4),
@tsc_ct varchar(4),
@tsc_flag varchar(1),
@calling_num varchar(15),
@clg_num_in_tac varchar(15),
@code_dial varchar(4),
@code_used varchar(4),
@cond_code varchar(1),
@call_date varchar(6),
@dialed_num varchar(23),
@feat_flag varchar(1),
@in_crt_id varchar(3),
@in_trk_code varchar(4),
@auth_code varchar(13),
@ixc_code varchar(4),
@ma_uui varchar(1),
@node_num varchar(2),
@out_crt_id varchar(3),
@sec_dur varchar(5)

set @unformatted = replace(@unformatted,char(0),"")
set @acct_code = substring(@unformatted,1,15)
set @attd_console = substring(@unformatted,16,2)
set @bandwidth = substring(@unformatted,18,2)
set @bcc = substring(@unformatted,20,1)
set @calling_num = substring(@unformatted,21,15)
set @clg_num_in_tac = substring(@unformatted,36,15)
set @code_dial = substring(@unformatted,51,4)
set @code_used = substring(@unformatted,55,4)
set @cond_code = substring(@unformatted,59,1)
set @date = substring(@unformatted,60,6)
set @dialed_num = substring(@unformatted,66,23)
set @duration = substring(@unformatted,89,4)
set @feat_flag = substring(@unformatted,93,1)
set @frl = substring(@unformatted,94,1)
set @in_crt_id = substring(@unformatted,95,3)
set @in_trk_code = substring(@unformatted,98,4)
set @ins = substring(@unformatted,102,3)
set @auth_code = substring(@unformatted,105,13)
set @ixc_code = substring(@unformatted,118,4)
set @ma_uui = substring(@unformatted,122,1)
set @node_num = substring(@unformatted,123,2)
set @out_crt_id = substring(@unformatted,125,3)
set @ppm = substring(@unformatted,128,5)
set @res_flag = substring(@unformatted,133,1)
set @sec_dur = substring(@unformatted,134,5)
set @time = substring(@unformatted,139,4)
set @tsc_ct = substring(@unformatted,143,4)
set @tsc_flag = substring(@unformatted,147,1)

if len(@unformatted) > 30
insert into call_log (unformatted, bandwidth, bcc, duration, frl, ins, ppm, res_flag, time, tsc_ct, tsc_flag,
acct_code, attd_console, calling_num, clg_num_in_tac, code_dial, code_used, cond_code, date, dialed_num, feat_flag,
in_crt_id, in_trk_code, auth_code, ixc_code, ma_uui, node_num, out_crt_id, sec_dur)
values (@unformatted, rtrim(ltrim(@bandwidth)), rtrim(ltrim(@bcc)), rtrim(ltrim(@duration)), rtrim(ltrim(@frl)), rtrim(ltrim(@ins)), rtrim(ltrim(@ppm)),
rtrim(ltrim(@res_flag)), rtrim(ltrim(@time)), rtrim(ltrim(@tsc_ct)), rtrim(ltrim(@tsc_flag)), rtrim(ltrim(@acct_code)), rtrim(ltrim(@attd_console)),
rtrim(ltrim(@calling_num)), rtrim(ltrim(@clg_num_in_tac)), rtrim(ltrim(@code_dial)), rtrim(ltrim(@code_used)), rtrim(ltrim(@cond_code)),
rtrim(ltrim(@date)), rtrim(ltrim(@dialed_num)), rtrim(ltrim(@feat_flag)), rtrim(ltrim(@in_crt_id)), rtrim(ltrim(@in_trk_code)), rtrim(ltrim(@auth_code)),
rtrim(ltrim(@ixc_code)), rtrim(ltrim(@ma_uui)), rtrim(ltrim(@node_num)), rtrim(ltrim(@out_crt_id)), rtrim(ltrim(@sec_dur)))
set TRANSACTION ISOLATION LEVEL READ COMMITTED
end

5. Set up SerialLogger to log to the ODBC connection with the following statement:

exec CSP_CALL_LOG ?

6. You can convert the data to decimal etc, I havent finished my reports yet.

7. The column definitions are located in the Administrators Guide, Chapter 20 (Features and technical Reference) under Call Detail Recording

Hope this helps!

If you found this helpful, mark it as such, so other people will see it. Thanks!