Christian,<div>To make this work, there are a number of changes you need to make. This is what I did:</div><div>1. ACC module params:</div><div><div>modparam("acc", "db_extra", "from_did=$avp(s:from_did); to_did=$avp(s:to_did)")</div>
<div>of course, you'll need to set from_did and to_did in your script</div><div><br></div><div>2. Update stored procedure:</div><div><div>CREATE PROCEDURE opensips_cdrs_1_6()</div><div>BEGIN</div><div> DECLARE done INT DEFAULT 0;</div>
<div> DECLARE bye_record INT DEFAULT 0;</div><div> DECLARE v_callid,v_from_tag, v_to_tag,v_from_did,v_to_did VARCHAR(64);</div><div> DECLARE v_inv_time, v_bye_time DATETIME;</div><div> DECLARE inv_cursor CURSOR FOR SELECT time, callid, from_tag, to_tag,from_did,to_did FROM acc where method='INVITE' and cdr_id='0';</div>
<div> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;</div><div> OPEN inv_cursor;</div><div> REPEAT</div><div> FETCH inv_cursor INTO v_inv_time, v_callid, v_from_tag, v_to_tag, v_from_did, v_to_did;</div>
<div> IF NOT done THEN</div><div> SET bye_record = 0;</div><div> SELECT 1, time INTO bye_record, v_bye_time FROM acc WHERE method='BYE' AND callid=v_callid AND ((from_tag=v_from_tag AND to_tag=v_to_tag) OR (from_tag=v_to_tag AND to_tag=v_from_tag)) ORDER BY time ASC LIMIT 1;</div>
<div> IF bye_record = 1 THEN</div><div> INSERT INTO cdrs (from_did,to_did,call_start_time,duration,sip_call_id,sip_from_tag,sip_to_tag,created) VALUES (v_from_did,v_to_did,v_inv_time,UNIX_TIMESTAMP(v_bye_time)-UNIX_TIMESTAMP(v_inv_time),v_callid,v_from_tag,v_to_tag,NOW());</div>
<div> UPDATE acc SET cdr_id=last_insert_id() WHERE callid=v_callid AND ( (from_tag=v_from_tag AND to_tag=v_to_tag) OR (from_tag=v_to_tag AND to_tag=v_from_tag));</div><div> END IF;</div><div> SET done = 0;</div>
<div> END IF;</div><div> UNTIL done END REPEAT;</div><div>END</div><div>//</div><div>DELIMITER ;</div><div>~ </div><div>(notice we pull from_did/to_did from acc table)</div><div><br></div><div>3. Add from_did/to_did fields to acc table</div>
<div>4. Add from_did/to_did fields to cdrs tables</div></div></div><div>5. Update cdrviewer configuration</div><div><div> // what fields to show </div><div> $show_field[0]['from_did'] = "Caller" ;</div>
<div> $show_field[1]['to_did'] = "Callee" ;</div><div> $show_field[2]['call_start_time'] = "Call Start Time";</div><div> $show_field[3]['duration'] = "Duration";</div>
<div> $show_field[4]['leg_type'] = "Leg Type";</div><div><br></div><div>I don't really know what Leg Type is. I'd probably stuff something in there like inbound/outbound/interstate/local/LD/TF/etc/etc/etc.</div>
<div><br></div><div>-Brett</div><div><br></div><div><br></div><div>Now a cavet here.. I just got this working.. however I'm getting multiple CDR records for each call.. for example, for one call I'll see 2 x 6 second calls and 1x36 second call. So I'm not entirely sure how that is happening yet.. I think it's because I'm logging ACKs and early media. (pretty sure actually).</div>
<div><br></div><div>On the other hand... Despite all the criticism out there regarding the generation of CDR from a proxy, I'd really like to see this done at the dialog level. After all, if you are going to piece together ACC recs to form a dialog based CDR, this really should be done in the dialog module. Rather than trying to replicate this kind of logic in a stored proc (blah!).</div>
<div><br></div><div><br></div><div><br></div><div class="gmail_quote">On Wed, Mar 24, 2010 at 3:49 PM, Christian Vo <span dir="ltr"><<a href="mailto:cvo@nvidia.com">cvo@nvidia.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<br>
<br>
Thx Bogdan,<br>
<br>
<br>
Seems to be correct though:<br>
<br>
// what fields to show<br>
$show_field[0]['caller_id'] = "Caller" ;<br>
$show_field[1]['callee_id'] = "Callee" ;<br>
$show_field[2]['call_start_time'] = "Call Start Time";<br>
$show_field[3]['duration'] = "Duration";<br>
$show_field[4]['leg_type'] = "Leg Type";<br>
<br>
<br>
<br>
I don't know what "leg_type" corresponds to, (no entry in the acc table afaik), but I do see caller_id and callee_id...<br>
but the CDRviewer is showing my call entry with these fields blanked out...<br>
<div><div></div><div class="h5"><br>
<br>
<br>
-----Original Message-----<br>
From: <a href="mailto:users-bounces@lists.opensips.org">users-bounces@lists.opensips.org</a> [mailto:<a href="mailto:users-bounces@lists.opensips.org">users-bounces@lists.opensips.org</a>] On Behalf Of Bogdan-Andrei Iancu<br>
Sent: Wednesday, March 24, 2010 12:24 PM<br>
To: OpenSIPS users mailling list<br>
Subject: Re: [OpenSIPS-Users] OpenSIPs-CP CDRviewer question<br>
<br>
Hi Christian,<br>
<br>
you need to go into opensips-cp > trunk > config > tools > system ><br>
cdrviewer > local.inc.php file and configure the $show_field array to<br>
show you whatever columns you need. Probably by mistake, it is<br>
configured to show some columns that does not exists.<br>
<br>
Regards,<br>
Bogdan<br>
<br>
<br>
Christian Vo wrote:<br>
> Hello,<br>
> So I realized I didn't have accounting to database enabled properly,<br>
> And added the following lines to my opensips.cfg:<br>
> modparam("acc", "db_url", "mysql://<user>:<passwd>@localhost/opensips")<br>
> modparam("acc", "db_extra", "caller_id=$fu; callee_id=$tu")<br>
> now I see there is an entry into the "acc" table:<br>
> mysql> select * from acc;<br>
> +----+--------+----------------------------------+----------+------------------------------------------+----------+------------+---------------------+--------+---------+---------+---------------------+---------------+------+--------+----------+--------------+------------+-------------+----------+---------------------+---------------+---------+<br>
> | id | method | from_tag | to_tag | callid | sip_code | sip_reason |<br>
> time | cdr_id | src_uri | dst_uri | caller_id | caller_domain | rpid |<br>
> src_ip | src_port | sip_proxy_ip | user_agent | accountcode | amaflags<br>
> | callee_id | callee_domain | contact |<br>
> +----+--------+----------------------------------+----------+------------------------------------------+----------+------------+---------------------+--------+---------+---------+---------------------+---------------+------+--------+----------+--------------+------------+-------------+----------+---------------------+---------------+---------+<br>
> | 1 | INVITE | 1BB69EB7976D43D1FA7F0A976BCFF582 | 9b522302 |<br>
> E06F4B164B33933E42E1DC53B510F59D1133C657 | 200 | OK | 2010-03-24<br>
> 11:20:03 | 0 | | | <a href="mailto:sip%3A1000@10.32.0.97">sip:1000@10.32.0.97</a> | | | | | | | | |<br>
> <a href="mailto:sip%3A1003@10.32.0.97">sip:1003@10.32.0.97</a> | | NULL |<br>
> | 2 | ACK | 1BB69EB7976D43D1FA7F0A976BCFF582 | 9b522302 |<br>
> E06F4B164B33933E42E1DC53B510F59D1133C657 | 200 | OK | 2010-03-24<br>
> 11:20:03 | 0 | | | <a href="mailto:sip%3A1000@10.32.0.97">sip:1000@10.32.0.97</a> | | | | | | | | |<br>
> <a href="mailto:sip%3A1003@10.32.0.97">sip:1003@10.32.0.97</a> | | NULL |<br>
> | 3 | BYE | 1BB69EB7976D43D1FA7F0A976BCFF582 | 9b522302 |<br>
> E06F4B164B33933E42E1DC53B510F59D1133C657 | 200 | OK | 2010-03-24<br>
> 11:20:26 | 0 | | | <a href="mailto:sip%3A1000@10.32.0.97">sip:1000@10.32.0.97</a> | | | | | | | | |<br>
> <a href="mailto:sip%3A1003@10.32.0.97">sip:1003@10.32.0.97</a> | | NULL |<br>
> +----+--------+----------------------------------+----------+------------------------------------------+----------+------------+---------------------+--------+---------+---------+---------------------+---------------+------+--------+----------+--------------+------------+-------------+----------+---------------------+---------------+---------+<br>
> However, when I view from CDRViewer, the<br>
> Caller, Callee , and Leg Type fields for this entry are blank.<br>
> Is this expected behavior?<br>
> ------------------------------------------------------------------------<br>
> This email message is for the sole use of the intended recipient(s)<br>
> and may contain confidential information. Any unauthorized review,<br>
> use, disclosure or distribution is prohibited. If you are not the<br>
> intended recipient, please contact the sender by reply email and<br>
> destroy all copies of the original message.<br>
> ------------------------------------------------------------------------<br>
> ------------------------------------------------------------------------<br>
><br>
> _______________________________________________<br>
> Users mailing list<br>
> <a href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a><br>
> <a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
><br>
<br>
<br>
--<br>
Bogdan-Andrei Iancu<br>
<a href="http://www.voice-system.ro" target="_blank">www.voice-system.ro</a><br>
<br>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
<br>
_______________________________________________<br>
Users mailing list<br>
<a href="mailto:Users@lists.opensips.org">Users@lists.opensips.org</a><br>
<a href="http://lists.opensips.org/cgi-bin/mailman/listinfo/users" target="_blank">http://lists.opensips.org/cgi-bin/mailman/listinfo/users</a><br>
</div></div></blockquote></div><br></div>