<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Tahoma;
panose-1:2 11 6 4 3 5 4 4 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman","serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-reply;
font-family:"Calibri","sans-serif";
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-GB link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Steve,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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. <o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>For example if I have a extension for incoming calls e.g. :<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>exten => 123456,1,Dial(IAX/1000&IAX/1001&IAX/1002)<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Then we want to know which of 1000,1001 and 1002 actually took
the call.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Accuracy is really important to us, and the source of the call
is just as important as its destination (in all cases).<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>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!<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>I hope this puts across what you wanted to know re. my use of
CEL.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Regards,<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'>Nic.<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri","sans-serif";
color:#1F497D'><o:p> </o:p></span></p>
<div style='border:none;border-top:solid #B5C4DF 1.0pt;padding:3.0pt 0cm 0cm 0cm'>
<p class=MsoNormal><b><span lang=EN-US style='font-size:10.0pt;font-family:
"Tahoma","sans-serif"'>From:</span></b><span lang=EN-US style='font-size:10.0pt;
font-family:"Tahoma","sans-serif"'> asterisk-dev-bounces@lists.digium.com
[mailto:asterisk-dev-bounces@lists.digium.com] <b>On Behalf Of </b>Steve Murphy<br>
<b>Sent:</b> 30 December 2009 16:36<br>
<b>To:</b> Asterisk Developers Mailing List<br>
<b>Subject:</b> Re: [asterisk-dev] Possible Call Event Logging (CEL) Bugs<o:p></o:p></span></p>
</div>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal style='margin-bottom:12.0pt'>Nic--<br>
<br>
Before diving into databasing the CEL events, I would suggest that you log all
events to a<br>
text log file first, and then get a feel for the data you are going to
database. Then, after<br>
that, I'd map out the queries you are going to need to do to get the
information you want.<br>
<br>
Originally, I provided the same backends that CDR records have, but the first
implementation<br>
based on CEL did not use any database, as the queries necessary to pick out the
information<br>
desired were either impossible or impractically complex. It pulled the data it
needed directly<br>
from the event list itself.<br>
<br>
So, I'm interested in how you go about using CEL. I've offered to do a
CEL->CDR converter<br>
before, but no-one seemed interested in actually having to pay something to get
a CDR<br>
based system that actually works in the face of transfers, parking, etc. And,
if no-one<br>
is willing to pay to get a job done, the job ain't worth doing.<br>
<br>
murf<br>
<br>
<o:p></o:p></p>
<div>
<p class=MsoNormal>On Mon, Dec 28, 2009 at 9:58 AM, Nic Colledge <<a
href="mailto:nic@njcolledge.net">nic@njcolledge.net</a>> wrote:<o:p></o:p></p>
<div>
<div>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>CREATE
TABLE cel<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>(<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
uniqueid character varying(150) NOT NULL,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
linkedid character varying(150) NOT NULL,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
eventtime timestamp with time zone NOT NULL,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
eventtype integer NOT NULL,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
userdeftype character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
cid_name character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
cid_num character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
cid_ani character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
cid_rdnis character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
cid_dnid character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
exten character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
context character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
channame character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
appname character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
appdata character varying(200),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
accountcode character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
peeraccount character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
amaflag integer,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
userfield character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
peer character varying(100),<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
id serial NOT NULL<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>)<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>WITH
(<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>
OIDS=FALSE<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>);<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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).<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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:<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>[Dec
28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574 odbc_log: CEL variable
amaflag is not an integer.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>[Dec
28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574 odbc_log: CEL variable id
is not an integer.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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)”.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Is
there any way to make the pgsql backend ignore the ID (serial) column so that
the database will sort its value out properly?<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>What
are these other two errors from the adaptive ODBC backend?<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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?<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>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. <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Thanks
in advance.<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Regards,<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Nic
Colledge<o:p></o:p></p>
<p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p>
</div>
</div>
<p class=MsoNormal><br>
_______________________________________________<br>
--Bandwidth and Colocation Provided by <a href="http://www.api-digital.com--"
target="_blank">http://www.api-digital.com--</a><br>
<br>
asterisk-dev mailing list<br>
To UNSUBSCRIBE or update options visit:<br>
<a href="http://lists.digium.com/mailman/listinfo/asterisk-dev"
target="_blank">http://lists.digium.com/mailman/listinfo/asterisk-dev</a><o:p></o:p></p>
</div>
<p class=MsoNormal style='margin-bottom:12.0pt'><br>
<br clear=all>
<br>
-- <br>
Steve Murphy<br>
ParseTree Corp<o:p></o:p></p>
</div>
</body>
</html>