add more options (issue #5137)
[asterisk/asterisk.git] / 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