[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