PostGreSQL and Asterisk

Asterisk Asterisk Support
1

I just installed PostGreSQL. Asterisk is writing CDR in my database but some of the data are not in the right champ. FOr exemple, in the bill sec champ I got “tTwW” which are the options of my Dial function.

Anyone one could point me in the right direction ?

Here is the script used to create my table :

CREATE TABLE cdr (
AcctId BIGSERIAL PRIMARY KEY,
calldate TIMESTAMP with time zone NOT NULL DEFAULT now(),
clid VARCHAR(80) NOT NULL default ‘’,
src VARCHAR(80) NOT NULL default ‘’,
dst VARCHAR(80) NOT NULL default ‘’,
dcontext VARCHAR(80) NOT NULL default ‘’,
channel VARCHAR(80) NOT NULL default ‘’,
dstchannel VARCHAR(80) NOT NULL default ‘’,
lastapp VARCHAR(80) NOT NULL default ‘’,
lastdata VARCHAR(80) NOT NULL default ‘’,
duration INTEGER NOT NULL default ‘0’,
billsec INTEGER NOT NULL default ‘0’,
disposition VARCHAR(45) NOT NULL default ‘’,
amaflags INTEGER NOT NULL default ‘0’,
accountcode VARCHAR(20) NOT NULL default ‘’,
uniqueid VARCHAR(32) NOT NULL default ‘’,
userfield VARCHAR(255) NOT NULL default ‘’
);

2

result of pq_dump (cdr table)
(idsw is a sequence)

--
-- TOC entry 6 (OID 65441)
-- Name: cdr; Type: TABLE; Schema: public; Owner: fdragowski
--

CREATE TABLE cdr (
    id numeric(18,0) DEFAULT nextval('idsw'::text) NOT NULL,
    calldate timestamp without time zone NOT NULL,
    clid character varying(80) NOT NULL,
    src character varying(80) NOT NULL,
    dst character varying(80) NOT NULL,
    dcontext character varying(80) NOT NULL,
    channel character varying(80) NOT NULL,
    dstchannel character varying(80) NOT NULL,
    lastapp character varying(80) NOT NULL,
    lastdata character varying(80) NOT NULL,
    duration integer NOT NULL,
    billsec integer NOT NULL,
    disposition character varying(45) NOT NULL,
    amaflags integer NOT NULL,
    accountcode character varying(40) NOT NULL,
    uniqueid character varying(32) NOT NULL,
    userfield character varying(255) NOT NULL
);

everything is logged properly
i’m using * 1.2.4

3

If found my problem, thanks

It was my Dial command:
I was using : Dial(ZAP/g1/${EXTEN},20,tTwW)

Using the coma create some error in my postgresql. So I repalce every, by / and everything is working.

Dial(ZAP/g1/${EXTEN}/20/tTwW)

Thx for sharing your table structur.

4

i’m usig always |
it’s like this:

Dial(ZAP/g1/${EXTEN}|20|tTwW)
5

Hummm, If I remove any options from my dial string, everything is fine. As soon as I had options using coma or |, I’m getting wrong data is some row. I posted my problem on the bugs tracker. I should try with 1.2.4. Just to make sure, you could post one of your Dial String with options you have in your diaplan.

Do you have changed anything in any other cdr_XXXX.conf files exept in cdr_pgsql.conf ?

6

I don’t use much of options in Dial:
for incoming calls i use:

exten => s,8,Dial(${cel},30|t)

or

exten => s,10,Dial(${boss}&${fd}&${md},20|t)

for outgoing calls
voip: exten => s,4,Dial(SIP/${provider}/${dzwon_do},30,)
zap: exten => s,5,Dial(Zap/1/${dzwon_do},30,)

for internal calls exten => s,5,Dial(SIP/${wewn_do}|25|t)

I use to record every call and don’t use ‘W’ option in Dial

exten => s,6,Monitor(wav|${FNAME}|m)

for transfering i put only ‘t’ option and it’s work fine.
exemple cdr log directly from database:

  id  |      calldate       | clid          |src   |dst  | dcontext | channel          |     dstchannel        |lastapp |       lastdata              |dur.  |bills.|disp.     |ama|accountcode|    uniqueid    |userfield
------+---------------------+---------------+------+-----+----------+------------------+-----------------------+--------+-----------------------------+------+------+----------+---+-----------+----------------+-----------
 2410 | 2006-05-08 21:01:28 | "BOss" <104>  | 104  | s   | outgoing | SIP/104-9ead     | SIP/sip.provider-2749 | Dial   | SIP/sip.provider/number|30| |  449 |  433 | ANSWERED | 2 | provider  | 1147114888.236 | number
 2411 | 2006-05-09 08:08:52 | XXXX          |      | s   | incoming | SIP/ontp-f07b    | SIP/101-a19d          | Hangup |                             |   58 |   58 | ANSWERED | 3 | incoming  | 1147154932.238 | 101

In /etc/asterisk i have :
cdr_custom.conf - no modification since asterisk installation
cdr_odbc.conf - nothing inside
cdr_psql.conf:

[global] hostname=localhost port=5432 dbname=asterisk password=db_pass user=asterisk table=cdr