[Serusers] Logging weirdness with Aliases.

Greger V. Teigre greger at teigre.com
Fri Jun 30 11:07:16 CEST 2006


To acc, I don't know off the top of my head how accounting modules uses 
from vs ruri, but it should be fairly easy to change it to use ruri 
(which is changed by lookup("aliases")). There are some format 
modification possibilities found in README of acc module.

Or what about adding something like this to your query:
SELECT <existing>, a1.username as alias a1.contact as myuser FROM 
aliases a1, <existing> WHERE <existing AND (t1.to_uri LIKE '%username>%' 
OR (t1.to_uri=contact AND contact LIKE '%username>%')) <existing>

it's an attempt at a join with the alias table. No guarantees, but play 
around with it.
g-)

sip wrote:
> Okay... let me approach this a different way and see if anyone has ideas. 
>
> We do logging using the ACC table... not for billing purposes, but so users
> can have a look at their logs (billing CDRs are kept on Asterisk and are
> accurate and good).  The problem is this... 
>
> Our current query to display incoming calls looks like this:
>
> 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='INVITE'
> AND t2.sip_method='BYE' AND t1.to_uri LIKE '%<username>%' AND (select count(*)
> from acc where sip_callid=t1.sip_callid and sip_method='INVITE' and from_uri
> LIKE '%<username>%')=0 ORDER BY t1.time DESC;
>
> It's a complex query, but it works quite well... UNTIL you put aliases into
> the picture. 
>
> When using aliases, all transactions in and out in the logs are written as the
> user's alias, and not the user himself. This makes sense, but it's giving me a
> headache for logging. If I attempt to use a lookup("aliases") command to
> rewrite the URI for the purposes of logging, the BYE message vanishes. 
>
> So I guess my question is:  is there an elegant solution for this, or can
> someone give me a way to go through each and every user's alias, create a
> table like the one above, and then somehow merge the tables, ordered by timestamp?
>
> I have quite taxed the limit of my SQL knowledge, so I'm at a loss. 
>
>
> N.
>
>
>
>
> On Wed, 28 Jun 2006 19:02:58 -0400, sip wrote
>   
>> Something else I noticed that perhaps someone could shed light upon:
>>
>> If I DO put the lookup("aliases") line in there, the call never logs 
>> a BYE message.  Why does that happen? Can someone fill me in on that?
>>
>> It's strange... just when you think you have a handle on all of this,
>> something comes along you just completely don't understand.
>>
>> N.
>>
>> On Wed, 28 Jun 2006 18:37:29 -0400, sip wrote
>>     
>>> Okay... help me visualise something.  Now, as far as I can tell, SER 
>>> is doing exactly what it's supposed to be doing, but I can't figure 
>>> out how to get it to do something different.
>>>
>>> I have an accounting section (my log flag is 1) that looks like this:
>>>
>>> if((method=="INVITE" && !has_totag()) || method=="BYE")
>>> {
>>>         setflag(1);
>>> };
>>>
>>> Basic, simple logging section. But when someone calls through from a 
>>> DID to an alias in the aliases table, it of course logs the sip_to 
>>> as the alias.
>>>
>>> Fine, I thought. I'll just add a lookup("aliases") before the 
>>> setflag and all will be well, right? No. Then it doesn't seem to log 
>>> anything at ALL.
>>>
>>> So what I'm trying to figure out is how to get SER to log a call 
>>> coming into an alias as a call to the user who's aliased.
>>>
>>> I.e. a call is coming in from 1010 at remote.proxy.com to 
>>> 9999 at proxy.com (which is really an alias to user 1234 at proxy.com).  
>>>  Right now, SER is logging in the acc tables that the call went to 
>>> sip:9999 at proxy.com.
>>>
>>> I want to have it log that the call went to 1234 at proxy.com and NOT 
>>> that it went to the alias.
>>>
>>> This:
>>>
>>> if((method=="INVITE" && !has_totag()) || method=="BYE")
>>> {
>>>      lookup("aliases");   
>>>      setflag(1);
>>> };
>>>
>>> gets me nothing at all.
>>>
>>> Ideas, anyone? I'm stumped. I'm sure it's something drastically 
>>> simple that I'm just not getting.
>>>
>>> N.
>>> _______________________________________________
>>> Serusers mailing list
>>> Serusers at lists.iptel.org
>>> http://lists.iptel.org/mailman/listinfo/serusers
>>>       
>
> _______________________________________________
> Serusers mailing list
> Serusers at lists.iptel.org
> http://lists.iptel.org/mailman/listinfo/serusers
>
>
>   
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.sip-router.org/pipermail/sr-users/attachments/20060630/0ac2d6b9/attachment.htm>


More information about the sr-users mailing list