[Users] Re: [Serusers] Help with MySQL acc query

[Evan Borgstrom]

Here's a query to find "active calls".

SELECT
	t1.from_uri as orig_number,
	t1.to_uri as term_number,
	DATE_FORMAT(t1.timestamp, '%a %b %D %Y, %h:%i:%s %p') as calldate,
	t1.sip_callid as callid
FROM
	acc t1
WHERE
	t1.timestamp > (NOW() - 14400) AND
	t1.sip_method='INVITE' AND
	t1.sip_callid NOT IN (SELECT t2.sip_callid FROM acc t2 WHERE 
t2.sip_method = 'BYE')


The calldate is formatted a way that I prefer (eg. Mon Oct 31st 2005, 
09:49:20 AM), feel free to change it. The other thing to note is 
"t1.timestamp > (NOW() - 14400)", this sets the query to only find 
INVITEs in the last 4 hours to handle situations where INVITEs are sent 
but BYEs aren't.


-Evan


Daryl Sanders wrote:
> I'm trying to come up with a query on the acc table that will return
> all INVITES that don't have a matching BYE. I'm basically looking for
> sessions that are currently open.
> 
> - Daryl
> 
> _______________________________________________
> Serusers mailing list
> Serusers at iptel.org
> http://mail.iptel.org/mailman/listinfo/serusers