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: _______________________________________________ Users mailing list Users(a)openser.org http://openser.org/cgi-bin/mailman/listinfo/users

Be careful because some user agents use same call id many times. To be 100% sure, you have to check the From and To tags, too. Cheers, Daniel On 10/31/05 17:31, Evan Borgstrom wrote: > > 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(a)iptel.org >> http://mail.iptel.org/mailman/listinfo/serusers > > _______________________________________________ > Users mailing list > Users(a)openser.org > http://openser.org/cgi-bin/mailman/listinfo/users >

On 11/01/05 20:46, Evan Borgstrom wrote: Here is where the problem usually occurs. Most of the UA I have seen relies on IP address to fulfill this requirement, but this fails in the case of private addresses. Usually, the call id is in the form of "some_hash@ip".

According to RFC, what really identifies the call is call-id, from-tag and to-tag, so in my opinion is better to use all of them. If you control the type of user agents in your network, then you can do a lot of optimization everywhere, in an open environment you have to be prepared for the worst (btw: I am not at all voip service provider/itsp). Just for example, the person implementing serweb chose the same approach, to check the tags when getting cdrs, too.

Daniel > > > AFAIK there's no method specific behavior for INVITE's that would > warrant the UA's reusing call-id's... > > > -Evan > > Daniel-Constantin Mierla wrote: >> Be careful because some user agents use same call id many times. To >> be 100% sure, you have to check the From and To tags, too. >> >> Cheers, >> Daniel >> >> >> On 10/31/05 17:31, Evan Borgstrom wrote: >>> >>> 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(a)iptel.org >>>> http://mail.iptel.org/mailman/listinfo/serusers >>> >>> _______________________________________________ >>> Users mailing list >>> Users(a)openser.org >>> http://openser.org/cgi-bin/mailman/listinfo/users >>> > > _______________________________________________ > Serusers mailing list > Serusers(a)iptel.org > http://mail.iptel.org/mailman/listinfo/serusers >