[asterisk-dev] Possible Call Event Logging (CEL) Bugs

Wed Dec 30 16:00:09 CST 2009

Steve,

Thanks for your reply. Basically we have a ASP.Net website that users see to configure their settings, view billing information and (hopefully) pay bills. On the Linux side we are running real-time asterisk with a postgreSQL database to deal with the real work of VOIP.

In-between the two we have a bunch of little applications (windows services) that synchronise the settings and billing information between Postgres and MSSQL, most of this is already working really well. Unfortunately we ran into the same CDR issues around transfers / parking etc. that you describe. We also have another requirement of knowing which SIP / IAX channels actually deal with a call.
For example if I have a extension for incoming calls e.g. :
exten => 123456,1,Dial(IAX/1000&IAX/1001&IAX/1002)
Then we want to know which of 1000,1001 and 1002 actually took the call.
Accuracy is really important to us, and the source of the call is just as important as its destination (in all cases).

I plan to intermittently check the CEL table for new calls, then check those calls are complete (actually hung-up and finished), reconcile any billing and other information we want from them, and then put that more concise information in the MSSQL database in a nicer format for the website.

This will be done with a single query (to postgresql) per-run of the sync app and all the logic will be in the sync app so it can't see it nailing the postgresql database too much (using the ID serial I added to limit the amount of data returned - so we don't always have to see the same calls more than once), of the two the MSSQL database is likely to get the most abuse but that's fine.

I can see the value of more general CEL->CDR processing but the richness of the CEL information can be really useful and a one-size-fits-all solution to the problems with the current CDR will itself be non-trivial. Maybe there's some kind of happy medium between the two, where less information is stored but still in the form of several "things" (events / bridge creations / something else??) happening for a single call.

As for logging to a text file I personally find it easier to experiment against a database and were not currently running anything mission critical so if there is quirkiness it's no big deal!

I hope this puts across what you wanted to know re. my use of CEL.

Regards,
Nic.

From: asterisk-dev-bounces at lists.digium.com [mailto:asterisk-dev-bounces at lists.digium.com] On Behalf Of Steve Murphy
Sent: 30 December 2009 16:36
To: Asterisk Developers Mailing List
Subject: Re: [asterisk-dev] Possible Call Event Logging (CEL) Bugs

Nic--

Before diving into databasing the CEL events, I would suggest that you log all events to a
text log file first, and then get a feel for the data you are going to database. Then, after
that, I'd map out the queries you are going to need to do to get the information you want.

Originally, I provided the same backends that CDR records have, but the first implementation
based on CEL did not use any database, as the queries necessary to pick out the information
desired were either impossible or impractically complex. It pulled the data it needed directly
from the event list itself.

So, I'm interested in how you go about using CEL. I've offered to do a CEL->CDR converter
before, but no-one seemed interested in actually having to pay something to get a CDR
based system that actually works in the face of transfers, parking, etc. And, if no-one
is willing to pay to get a job done, the job ain't worth doing.

murf

On Mon, Dec 28, 2009 at 9:58 AM, Nic Colledge <nic at njcolledge.net<mailto:nic at njcolledge.net>> wrote:
Hi,

I've been playing around with CEL in asterisk trunk (SVN-Trunk-r236144) and have found some strange behaviour and am wondering if these are bugs.

I have the CEL table in a postgreSQL database the layout of which was taken from the cel_pgsql.conf file. I also added a "id" serial to use a primary key. The SQL create statement is included below.

CREATE TABLE cel
(
  uniqueid character varying(150) NOT NULL,
  linkedid character varying(150) NOT NULL,
  eventtime timestamp with time zone NOT NULL,
  eventtype integer NOT NULL,
  userdeftype character varying(100),
  cid_name character varying(100),
  cid_num character varying(100),
  cid_ani character varying(100),
  cid_rdnis character varying(100),
  cid_dnid character varying(100),
  exten character varying(100),
  context character varying(100),
  channame character varying(100),
  appname character varying(100),
  appdata character varying(200),
  accountcode character varying(100),
  peeraccount character varying(100),
  amaflag integer,
  userfield character varying(100),
  peer character varying(100),
  id serial NOT NULL
)
WITH (
  OIDS=FALSE
);

Anyway, when I use cel_pgsql.conf to connect to the database the "id" is always set to zero (which when it were a primary key caused a violation and made errors to that effect show up in the asterisk console).

Ok, so not the end of the world, I tried cel_adaptive_odbc.conf. Here the "id" serial is always set correctly by the database, but the following errors show up in the asterisk console:
[Dec 28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574 odbc_log: CEL variable amaflag is not an integer.
[Dec 28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574 odbc_log: CEL variable id is not an integer.

Any idea what's going on here? I'm working under the assumption that amaflag should be an int because the cel_pgsql.conf file comments say "amaflag (an int)".

Is there any way to make the pgsql backend ignore the ID (serial) column so that the database will sort its value out properly?
What are these other two errors from the adaptive ODBC backend?

I think it makes sense to have a primary key on this table, unless I'm missing something obvious I don't think one can be made from the other columns, can it?
Maybe it would be better to have asterisk make a extra field so that uniqueid + [somthing extra] can be used as a primary key? Of course I'm assuming it's not possible to use something that already exists when I suggest this.

Thanks in advance.

Regards,
Nic Colledge

_______________________________________________
--Bandwidth and Colocation Provided by http://www.api-digital.com--

asterisk-dev mailing list
To UNSUBSCRIBE or update options visit:
  http://lists.digium.com/mailman/listinfo/asterisk-dev

--
Steve Murphy
ParseTree Corp
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.digium.com/pipermail/asterisk-dev/attachments/20091230/0ab0b34d/attachment-0001.htm 