Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here



How do I log CDR / SMDR Data to SQL for a Definity G3 by KenNECIVSIPS
Posted: 16 Sep 02 (Edited 18 Feb 03)

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)

    set nocount on

     @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)))

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!

Back to Avaya: CM/Aura (Definity) FAQ Index
Back to Avaya: CM/Aura (Definity) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close