Summary:ASTERISK-09010: [patch] fields "start", "answer" and "end" are always empty.
Reporter:Rod Thomson (rottenroddy)Labels:
Date Opened:2007-03-14 13:35:22Date Closed:2008-02-25 17:00:41.000-0600
Versions:Frequency of
Environment:Attachments:( 0) 20080125__bug9279.diff.txt
( 1) extensions.conf_cdrtest.txt
( 2) modules.conf_cdrtest.txt
( 3) pgsql_standardcdr.patch
( 4) sip.conf_cdrtest.txt
Description:CDR format and content are different between given interfaces and documentation is inconsistent. Default CDR construction in cdr-csv is 18 fields and matches the documentation for the module(see below). For the remaining non-configurable interfaces, mysql,mssql and pgsql, mssql is the only documentation matching the standard format while the others do not. mssql was not tested for functionality. cdr_pgsql.so should match the output of the single standard cdr format for 1.4x. It does not. Fields "start", "answer" and "end" are always empty.


Fields of the CDR in Asterisk

  1. accountcode: What account number to use, (string, 20 characters)
  2. src: Caller*ID number (string, 80 characters)
  3. dst: Destination extension (string, 80 characters)
  4. dcontext: Destination context (string, 80 characters)
  5. clid: Caller*ID with text (80 characters)
  6. channel: Channel used (80 characters)
  7. dstchannel: Destination channel if appropriate (80 characters)
  8. lastapp: Last application if appropriate (80 characters)
  9. lastdata: Last application data (arguments) (80 characters)
 10. start: Start of call (date/time)
 11. answer: Answer of call (date/time)
 12. end: End of call (date/time)
 13. duration: Total time in system, in seconds (integer), from dial to hangup
 14. billsec: Total time call is up, in seconds (integer), from answer to hangup
 15. disposition: What happened to the call: ANSWERED, NO ANSWER, BUSY
 16. amaflags: What flags to use: DOCUMENTATION, BILL, IGNORE etc,
      specified on a per channel basis like accountcode.
 17. user field: A user-defined field, maximum 255 characters

In some cases, uniqueid is appended:

   * uniqueid: Unique Channel Identifier (32 characters)
     This needs to be enabled in the source code at compile time

This list was coppied from the 1.4 online developer documentation "
Comments:By: Clod Patry (junky) 2007-03-14 14:30:01

Do you use any specific CDR stuff (like ForkCDR, etc) ?

By: Rod Thomson (rottenroddy) 2007-03-14 15:19:29

I did some error checking with plpgsql and the fields are not sent as part of the new record. ie. they are not in the SQL formatted by the client. Not "empty" as I had first assumed.
Junky - no ForkCDR in dialplan just Set(CDR(accountcode)=123456), Set(CDR(amaflags)="BILLING") and Set(CDR(userfield)=16048720002)

cdr_manager.conf; enable=yes

By: Clod Patry (junky) 2007-03-14 15:21:52

I will take a look at this, but if you could attach a quick dialplan invoking this, that will be useful.
Also, do you use cdr batch mode?

By: Rod Thomson (rottenroddy) 2007-03-14 15:46:05

CDR logging: enabled
CDR mode: simple
CDR registered backend: pgsql

for this run I had not loaded cdr_manager but it made no difference.

Thanks and have fun

By: Rod Thomson (rottenroddy) 2007-03-14 16:00:57

Just a short update:

I had the field names wrong -"startdate", "answerdate" and "enddate" and they are in the SQL insert but they are NULL as first reported.

By: Joshua C. Colp (jcolp) 2007-06-27 17:31:21

I'm closing this out, the schema for pgsql does not show those fields being part of it.

By: Rod Thomson (rottenroddy) 2007-06-28 16:25:12

Interesting rational; Are you saying that you are deciding not to support the pgsql interface for the 1.4x product, or are you saying that the 1.4x product does not have a standard interface across databases? Either way the results diminish the Asterisk product. By not carrying these fields forward you are cutting off all  integration with realtime billing systems utilizing pgsq. Oh, and by the by, why,  if the schema does not contain the fields, are they in the insert statement? I find your decision shortsighted and inconsistent.

By: Joshua C. Colp (jcolp) 2007-06-28 16:35:40

It was up to the developer, Matthew D. Hardeman, in this case to determine what they wanted to write the module to insert. The module also does not have it as part of the INSERT. It has the calldate, clid, src, dst, dcontext, channel, dstchannel, lastapp, lastdata, duration, billsec, disposition, amaflags, accountcode, uniqueid, and userfield.

By: Donny Kavanagh (donnyk) 2007-06-29 22:09:39

Try the patch i just uploaded, its not really tested only that it compiles.  See if that provides you with the information you are looking for using cdr_pgsql.  Please note that calldate is no longer used, its previous contents go in start (as requested) and answer and end are also populated.

Let me know if this works.

By: Donny Kavanagh (donnyk) 2007-06-29 22:10:12

I guess my patch will show up when my license is approved :(

By: Steve Murphy (murf) 2007-07-06 09:06:01

Juggie-- your license is approved!!! Yay!! (Sorry for the delay...)

I've looked at your patch, and have but just one question: What will happen to all those using pgsql at this point in time? Their billdate column will no longer be populated, and the backend will try to write to 3 new fields that don't exist.

It looks like pgsql users will have to update their table schema, and maybe, maybe tweek their post-processing software to handle at least the changed field name.

As such, this change in behavior probably won't be allowed in 1.4, but we can add it to trunk.

Does pgsql have an ODBC interface available?

By: Donny Kavanagh (donnyk) 2007-07-06 09:11:25

murf, i just posted this as a quick fix for the bug poster to try.

pgsql definitely does have odbc.

as for existing users, yeah, this would obviously break them, but it does bring it up to speed with the cdr record as detailed within the cdr documentation.  So the question is to you just go ahead and break it in trunk, do you add an option to cdr_pgsql.conf to enable this mode, etc...

this may just bring to light a problem it might be advisable to look at for 1.6, do we really want to keep maintaining multiple cdr engines, mysql, pgsql, etc.. or would it be advisable to just drop support for those and keep only odbc.

By: Joshua C. Colp (jcolp) 2007-07-08 22:45:33

I would be fine with putting it into trunk as long as we detail it as previously discussed.

By: Donny Kavanagh (donnyk) 2007-07-09 09:09:40

I have no idea if this even works however as the original poster hasnt let us know.  And i admittedly don't have a pgsql setup to test.

Could some one double check this, it should have been trivial and i should not have broke it, but ... you never know.

By: Tilghman Lesher (tilghman) 2007-07-18 15:53:25

We could also grab the code that cdr_adaptive_odbc uses in the pgsql odbc driver and conditionally create those fields, if they exist in the table schema.  This has the distinct advantage of it being completely backwards compatible with the old schema, yet it will populate the new fields, if they exist.

By: Tilghman Lesher (tilghman) 2007-07-26 18:27:35

Proof-of-concept code uploaded to do autodetection and autopopulation of arbitrary fields.

By: Tilghman Lesher (tilghman) 2007-08-29 13:56:16

Anybody else feel like testing this patch, or has everybody lost interest?

By: Tilghman Lesher (tilghman) 2007-10-18 01:53:40

Reopen if anybody feels like testing and moving this forward.

By: Tilghman Lesher (tilghman) 2007-12-30 11:23:41.000-0600

I might test this in the next couple days, since I just converted cdr_addon_mysql to have the same functionality.

By: Tilghman Lesher (tilghman) 2008-01-24 18:28:21.000-0600

Updated to current trunk

By: Digium Subversion (svnbot) 2008-02-25 17:00:41.000-0600

Repository: asterisk
Revision: 104101

U   trunk/CHANGES
U   trunk/cdr/cdr_pgsql.c

r104101 | tilghman | 2008-02-25 17:00:40 -0600 (Mon, 25 Feb 2008) | 9 lines

Permit additional CDR columns to be saved in Postgres.  Note that these
changes are backward-compatible, so no changes to UPGRADE.txt are
(closes issue ASTERISK-9010)
Reported by: rottenroddy
      20080125__bug9279.diff.txt uploaded by Corydon76 (license 14)
Tested by: Corydon76