doc/cdr.txt

   1 Call data records can be stored in many different databases or even CSV text.
   2
   3 MSSQL:          Asterisk can currently store CDRs into an MSSQL database in
   4                 two different ways:  cdr_odbc.c or cdr_tds.c
   5
   6                 Call Data Records can be stored using unixODBC (which requires
   7                 the FreeTDS package) [cdr_odbc.c] or directly by using just the
   8                 FreeTDS package [cdr_tds.c]  The following provide some
   9                 examples known to get asterisk working with mssql.
  10                 NOTE:  Only choose one db connector.
  11
  12         ODBC [cdr_odbc.c]:
  13                 Compile, configure, and install the latest unixODBC package:
  14                    tar -zxvf unixODBC-2.2.9.tar.gz &&
  15                    cd unixODBC-2.2.9 &&
  16                    ./configure --sysconfdir=/etc --prefix=/usr --disable-gui &&
  17                    make &&
  18                    make install
  19
  20                 Compile, configure, and install the latest FreeTDS package:
  21                    tar -zxvf freetds-0.62.4.tar.gz &&
  22                    cd freetds-0.62.4 &&
  23                    ./configure --prefix=/usr --with -tdsver=7.0 \
  24                         --with-unixodbc=/usr/lib &&
  25                    make &&
  26                    make install
  27
  28                 Compile, or recompile, asterisk so that it will now add support
  29                 for cdr_odbc.c
  30
  31                    make clean &&
  32                    make update &&
  33                    make &&
  34                    make install
  35
  36                 Setup odbc configuration files.  These are working examples
  37                 from my system.  You will need to modify for your setup.
  38                 You are not required to store usernames or passwords here.
  39
  40                 /etc/odbcinst.ini
  41                    [FreeTDS]
  42                    Description    = FreeTDS ODBC driver for MSSQL
  43                    Driver         = /usr/lib/libtdsodbc.so
  44                    Setup          = /usr/lib/libtdsS.so
  45                    FileUsage      = 1
  46
  47                 /etc/odbc.ini
  48                    [MSSQL-asterisk]
  49                    description         = Asterisk ODBC for MSSQL
  50                    driver              = FreeTDS
  51                    server              = 192.168.1.25
  52                    port                = 1433
  53                    database            = voipdb
  54                    tds_version         = 7.0
  55                    language            = us_english
  56
  57                 Only install one database connector.  Do not confuse asterisk
  58                 by using both ODBC (cdr_odbc.c) and FreeTDS (cdr_tds.c).
  59                 This command will erase the contents of cdr_tds.conf
  60
  61                 [ -f /etc/asterisk/cdr_tds.conf ] > /etc/asterisk/cdr_tds.conf
  62
  63                 NOTE:  unixODBC requires the freeTDS package, but asterisk does
  64                 not call freeTDS directly.
  65
  66                 Setup cdr_odbc configuration files.  These are working samples
  67                 from my system.  You will need to modify for your setup. Define
  68                 your usernames and passwords here, secure file as well.
  69
  70                 /etc/asterisk/cdr_odbc.conf
  71                    [global]
  72                    dsn=MSSQL-asterisk
  73                    username=voipdbuser
  74                    password=voipdbpass
  75                    loguniqueid=yes
  76
  77                 And finally, create the 'cdr' table in your mssql database.
  78
  79                 CREATE TABLE cdr (
  80                         [calldate]      [datetime]              NOT NULL ,
  81                         [clid]          [varchar] (80)          NOT NULL ,
  82                         [src]           [varchar] (80)          NOT NULL ,
  83                         [dst]           [varchar] (80)          NOT NULL ,
  84                         [dcontext]      [varchar] (80)          NOT NULL ,
  85                         [channel]       [varchar] (80)          NOT NULL ,
  86                         [dstchannel]    [varchar] (80)          NOT NULL ,
  87                         [lastapp]       [varchar] (80)          NOT NULL ,
  88                         [lastdata]      [varchar] (80)          NOT NULL ,
  89                         [duration]      [int]                   NOT NULL ,
  90                         [billsec]       [int]                   NOT NULL ,
  91                         [disposition]   [varchar] (45)          NOT NULL ,
  92                         [amaflags]      [int]                   NOT NULL ,
  93                         [accountcode]   [varchar] (20)          NOT NULL ,
  94                         [uniqueid]      [varchar] (32)          NOT NULL ,
  95                         [userfield]     [varchar] (255)         NOT NULL
  96                 )
  97
  98                 Start asterisk in verbose mode, you should see that asterisk
  99                 logs a connection to the database and will now record every
 100                 call to the database when it's complete.
 101
 102         TDS [cdr_tds.c]:
 103                 Compile, configure, and install the latest FreeTDS package:
 104                    tar -zxvf freetds-0.62.4.tar.gz &&
 105                    cd freetds-0.62.4 &&
 106                    ./configure --prefix=/usr --with-tdsver=7.0
 107                    make &&
 108                    make install
 109
 110                 Compile, or recompile, asterisk so that it will now add support
 111                 for cdr_tds.c  (Currently only asterisk CVS supports cdr_tds.c)
 112
 113                    make clean &&
 114                    make update &&
 115                    make &&
 116                    make install
 117
 118                 Only install one database connector.  Do not confuse asterisk
 119                 by using both ODBC (cdr_odbc.c) and FreeTDS (cdr_tds.c).
 120                 This command will erase the contents of cdr_odbc.conf
 121
 122                 [ -f /etc/asterisk/cdr_odbc.conf ] > /etc/asterisk/cdr_odbc.conf
 123
 124                 Setup cdr_tds configuration files.  These are working samples
 125                 from my system.  You will need to modify for your setup. Define
 126                 your usernames and passwords here, secure file as well.
 127
 128                 /etc/asterisk/cdr_tds.conf
 129                    [global]
 130                    hostname=192.168.1.25
 131                    port=1433
 132                    dbname=voipdb
 133                    user=voipdbuser
 134                    password=voipdpass
 135                    charset=BIG5
 136
 137                 And finally, create the 'cdr' table in your mssql database.
 138
 139                 CREATE TABLE cdr (
 140                         [accountcode]   [varchar] (20)          NULL ,
 141                         [src]           [varchar] (80)          NULL ,
 142                         [dst]           [varchar] (80)          NULL ,
 143                         [dcontext]      [varchar] (80)          NULL ,
 144                         [clid]          [varchar] (80)          NULL ,
 145                         [channel]       [varchar] (80)          NULL ,
 146                         [dstchannel]    [varchar] (80)          NULL ,
 147                         [lastapp]       [varchar] (80)          NULL ,
 148                         [lastdata]      [varchar] (80)          NULL ,
 149                         [start]         [datetime]              NULL ,
 150                         [answer]        [datetime]              NULL ,
 151                         [end]           [datetime]              NULL ,
 152                         [duration]      [int]                   NULL ,
 153                         [billsec]       [int]                   NULL ,
 154                         [disposition]   [varchar] (20)          NULL ,
 155                         [amaflags]      [varchar] (16)          NULL ,
 156                         [uniqueid]      [varchar] (32)          NULL
 157                 )
 158
 159                 Start asterisk in verbose mode, you should see that asterisk
 160                 logs a connection to the database and will now record every
 161                 call to the database when it's complete.
 162
 163 MYSQL:
 164
 165 PGSQL:
 166
 167 SQLLITE:
 168
 169 -------------------------------------------------------------------------------
 170
 171 08/02/2004 : Duane Cox <dcox@illicom.net> - added mssql information