[Serusers] Logging weirdness with Aliases.

Greger V. Teigre greger at teigre.com
Sat Jul 1 10:29:16 CEST 2006


Good to hear that my input help and a big thanks for sharing your 
resulting query with explanation. I will store your message in my "smart 
SER tricks" folder :-)
g-)

sip wrote:
> Thanks, Greger, that actually gave me an idea of how to solve it but 
> in a completely different way. 
>
> I was playing with your idea, which works really well.... the modified 
> query comes to:
>
> SELECT t1.from_uri as Caller, t1.to_uri as Callee, TIMEDIFF(t2.time, 
> t1.time) as Duration, t1.time as CallDate, a1.username as alias, 
> a1.contact as myuser FROM acc t1,acc t2, aliases a1 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>%' OR (t1.to_uri=a1.contact AND a1.contact LIKE 
> '%<username>%')) AND (select count(*) from acc where 
> sip_callid=t1.sip_callid and sip_method='INVITE' and from_uri LIKE 
> '%<username>%')=0 GROUP by t1.time ORDER BY t1.time DESC;
>
> Of course, this doesn't show anything actually useful in the 'alias' 
> column because it selects the same entry once for each alias (a cross 
> join), and with my 'group by t1.time' to limit that to one entry per 
> time slot, it ends up displaying just the first alias in the table... 
> but this is really almost EXACTLY what I wanted. I could discard that 
> field completely, and still, with that query, show who called the user 
> and when. An elegant solution, thank you.
>
> However, I decided to add one minor step to it in order to, well, make 
> things more complex, really. 
>
> I grab a list of aliases for a particular user and place them into an 
> array... then I build the query string based on that list:
>
> 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>%' or t1.to_uri LIKE '%<alias1>%' OR t1.to_uri LIKE 
> '%<alias2>%'... etc) 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;
>
>
> What this ends up giving me is a list of calls to that user, but 
> formats them in such a way that I can show not only where the calls 
> came from, but to which alias they went (all the aliases are DIDs, so 
> this may be helpful for some people).
>
> Thank you thank you thank you again. Without your help, I probably 
> just would have given up. :)
>
> N.
> *
>
> On Fri, 30 Jun 2006 11:07:16 +0200, Greger V. Teigre wrote*
> > 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/20060701/3833d03d/attachment.htm>


More information about the sr-users mailing list