Does anyone have a query that'd generate some sort of a CDR out of ACC log table? Supposely it's been posted before but I've looked thru the archives and didn't find it.
This is one I've been using, it gets me what I need.
SELECT invite.from_uri, invite.to_uri, invite.sip_callid, invite.timestamp as start, bye.timestamp as end, TIMESTAMPDIFF(MINUTE, invite.timestamp, bye.timestamp) as time, subscriber.id, user_info.first_name, user_info.last_name FROM acc as invite, acc as bye, subscriber, subscriber as user_info WHERE invite.sip_method="INVITE" AND bye.sip_method="BYE" AND invite.sip_callid = bye.sip_callid AND (subscriber.username = bye.username OR subscriber.username = invite.username) AND user_info.username = invite.username AND user_info.domain = invite.domain
On Friday 26 May 2006 12:52, Script Head wrote:
Does anyone have a query that'd generate some sort of a CDR out of ACC log table? Supposely it's been posted before but I've looked thru the archives and didn't find it.
You are right, these kind of thread is already been discussed.... ;)
But not in this list (at least from what I am able to remember. :-( )
Try to look for such a thread on the SER list. It was responded by 'sip' about 6~12 month ago..
_____
From: users-bounces@openser.org [mailto:users-bounces@openser.org] On Behalf Of Script Head Sent: sexta-feira, 26 de maio de 2006 13:53 To: OpenSER-users Subject: [Users] SQL query to generate CDR out of ACC logs
Does anyone have a query that'd generate some sort of a CDR out of ACC log table? Supposely it's been posted before but I've looked thru the archives and didn't find it.
something like that maybe ;)
SELECT t1.from_uri as Caller, t1.to_uri as Callee, TIMEDIFF(t2.time, t1.time) as Duration, t1.time as CallDate FROM acc t1, acc t2 WHERE t1.sip_callid = t2.sip_callid AND ((t1.fromtag = t2.fromtag and t1.totag = t2.totag) OR (t1.fromtag = t2.totag and t1.totag = t2.fromtag)) AND t1.sip_method='ACK' AND t2.sip_method='BYE' order by t1.time desc
Olivier
Edson a écrit :
You are right, these kind of thread is already been discussed.... ;)
But not in this list (at least from what I am able to remember. :-( )
Try to look for such a thread on the SER list. It was responded by 'sip' about 6~12 month ago..
From: users-bounces@openser.org [mailto:users-bounces@openser.org] On Behalf Of Script Head Sent: sexta-feira, 26 de maio de 2006 13:53 To: OpenSER-users Subject: [Users] SQL query to generate CDR out of ACC logs
Does anyone have a query that'd generate some sort of a CDR out of ACC log table? Supposely it's been posted before but I've looked thru the archives and didn't find it.
Users mailing list Users@openser.org http://openser.org/cgi-bin/mailman/listinfo/users
-
Edson -
Mike Williams -
olivier.taylor -
Script Head