Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

But of course...I just assumed that goes without saying. That is one reason to use re-INVITEs because at least then you can bill from the first INVITE to the final re-INVITE. If you're session-timer fires re-INVITE messages every two (2) minutes then you can get sure to have at most 120 seconds that are not billable in the event of a missing BYE message.

But like I mentioned in my original post, that SQL statement will not work with when accounting for re-INVITEs

Regards, Paul

On 7/4/05, Gustavo García ggb@tid.es wrote:

NOTE2: This will not work if the BYE is lost, too.

Regards. G.

-----Mensaje original----- De: serusers-bounces@iptel.org [mailto:serusers-bounces@lists.iptel.org] En nombre de Java Rockx Enviado el: lunes, 04 de julio de 2005 15:11 Para: Iqbal CC: serusers@lists.iptel.org Asunto: Re: [Serusers] session duration accounting

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

Hi Iqbal, I think the query u gave is not really correct, the INVITES are the beginning of the sessions, I think we must use the ACKs for a real accounting of the call. Just think, if it takes 30 seconds to the callee to hang on, u will overcharge him. I just suggest to replace the INVITES by ACKs.

Regards.

Olivier

-----Message d'origine----- De : serusers-bounces@iptel.org [mailto:serusers-bounces@lists.iptel.org] De la part de Iqbal Envoyé : mardi 5 juillet 2005 13:28 À : Ryan Pagquil Cc : serusers@lists.iptel.org Objet : Re: [Serusers] session duration accounting

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

_______________________________________________ Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

Hi Paul,

Well, in this case, I must agree, in my case I am charged only from the ACKs, I use B2bua for accounting, but I have no diffrence between ACC standard table, Radacct(radius accounting) and my pstn provider, that's why I use ACKs and not Invites.

In your case, you will charge the caller even if the callee is away and even if the caller decides to cancel the call before the callee answers.

What's the meaning of life ;)

Regards, Olivier

-----Message d'origine----- De : Java Rockx [mailto:javarockx@gmail.com] Envoyé : mardi 5 juillet 2005 15:44 À : Olivier Taylor Cc : Iqbal; Ryan Pagquil; serusers@lists.iptel.org Objet : Re: RE : [Serusers] session duration accounting

I understand what you're saying, however, I disagree with your suggestion because me (as a PSTN gateway owner) will be charged tolls based on the INVITE-to-BYE duration from my carriers for completed calls.

So if I'm only billing durations from ACK to BYE, then :

1) How would I accurately determine which ACK to account against considering that many ACKs can happen during any call? For instance, if you get put on hold or a re-INVITE.

2) Why should I give the customer 30 seconds of free platform time when my carriers bill me toll charges for that period?

Regards, Paul

On 7/5/05, Olivier Taylor olivier.taylor@gmail.com wrote:

Hi Iqbal, I think the query u gave is not really correct, the INVITES are the beginning of the sessions, I think we must use the ACKs for a real accounting of the call. Just think, if it takes 30 seconds to the callee to hang on, u will overcharge him. I just suggest to replace the INVITES by ACKs.

Regards.

Olivier

-----Message d'origine----- De : serusers-bounces@iptel.org [mailto:serusers-bounces@lists.iptel.org] De la part de Iqbal Envoyé : mardi 5 juillet 2005 13:28 À : Ryan Pagquil Cc : serusers@lists.iptel.org Objet : Re: [Serusers] session duration accounting

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

U right :)

Olivier

-----Message d'origine----- De : Java Rockx [mailto:javarockx@gmail.com] Envoyé : mardi 5 juillet 2005 16:02 À : Olivier Taylor Cc : Iqbal; serusers@lists.iptel.org Objet : Re: RE : RE : [Serusers] session duration accounting

No so. We only charge if the INVITE has a matching BYE, which means that the other end when off hook or went to voice mail or was call forwarded to another number.

In any event, a CANCEL would not be billable because of the 486 reply that was accounted for.

Regards, Paul

On 7/5/05, Olivier Taylor olivier.taylor@gmail.com wrote:

Hi Paul,

Well, in this case, I must agree, in my case I am charged only from the ACKs, I use B2bua for accounting, but I have no diffrence between ACC standard table, Radacct(radius accounting) and my pstn provider, that's why I use ACKs and not Invites.

In your case, you will charge the caller even if the callee is away and even if the caller decides to cancel the call before the callee answers.

What's the meaning of life ;)

Regards, Olivier

-----Message d'origine----- De : Java Rockx [mailto:javarockx@gmail.com] Envoyé : mardi 5 juillet 2005 15:44 À : Olivier Taylor Cc : Iqbal; Ryan Pagquil; serusers@lists.iptel.org Objet : Re: RE : [Serusers] session duration accounting

I understand what you're saying, however, I disagree with your suggestion because me (as a PSTN gateway owner) will be charged tolls based on the INVITE-to-BYE duration from my carriers for completed calls.

So if I'm only billing durations from ACK to BYE, then :

1. How would I accurately determine which ACK to account against

considering that many ACKs can happen during any call? For instance, if you get put on hold or a re-INVITE.

2. Why should I give the customer 30 seconds of free platform time

when my carriers bill me toll charges for that period?

Regards, Paul

On 7/5/05, Olivier Taylor olivier.taylor@gmail.com wrote:

...

Hi Iqbal, I think the query u gave is not really correct, the INVITES are the beginning of the sessions, I think we must use the ACKs for a real accounting of the call. Just think, if it takes 30 seconds to the callee to hang on, u will overcharge him. I just suggest to replace the INVITES by ACKs.

Regards.

Olivier

-----Message d'origine----- De : serusers-bounces@iptel.org [mailto:serusers-bounces@lists.iptel.org] De la part de Iqbal Envoyé : mardi 5 juillet 2005 13:28 À : Ryan Pagquil Cc : serusers@lists.iptel.org Objet : Re: [Serusers] session duration accounting

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

I concur, thats what the initial setup charge is usually for that you get billied by calling card companies, the provider gets charged the moment a signal hits the gateway, hence that has to be passed onto the customer, else the 30secs start to add up

Iqbal

Java Rockx wrote:

I understand what you're saying, however, I disagree with your suggestion because me (as a PSTN gateway owner) will be charged tolls based on the INVITE-to-BYE duration from my carriers for completed calls.

So if I'm only billing durations from ACK to BYE, then :

1. How would I accurately determine which ACK to account against

considering that many ACKs can happen during any call? For instance, if you get put on hold or a re-INVITE.

2. Why should I give the customer 30 seconds of free platform time

when my carriers bill me toll charges for that period?

Regards, Paul

On 7/5/05, Olivier Taylor olivier.taylor@gmail.com wrote:

...

Hi Iqbal, I think the query u gave is not really correct, the INVITES are the beginning of the sessions, I think we must use the ACKs for a real accounting of the call. Just think, if it takes 30 seconds to the callee to hang on, u will overcharge him. I just suggest to replace the INVITES by ACKs.

Regards.

Olivier

-----Message d'origine----- De : serusers-bounces@iptel.org [mailto:serusers-bounces@lists.iptel.org] De la part de Iqbal Envoyé : mardi 5 juillet 2005 13:28 À : Ryan Pagquil Cc : serusers@lists.iptel.org Objet : Re: [Serusers] session duration accounting

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

.

Hi Iqbal,

Ah ok, those queries will only generate the duration of INVITE to BYE methods data from my database. But how can I get INVITE and BYE accounted in my ser.cfg? I have this ser.cfg that I made but it is not running, it gaves me an error (core dumped). Well just want to consult you on how can I make this ser.cfg to work and account for INVITE's and BYE's. Like just I said, I'm a very newbie with ser =).

This is the config file:

#debug=7 fork=no log_stderror=yes

check_via=no dns=no port=5060 listen=10.0.1.5 fifo_db_url="mysql://ser:heslo@localhost/ser" fifo="/tmp/ser_fifo"

#load needed modules

loadmodule "/usr/local/lib/ser/modules/mysql.so" loadmodule "/usr/local/lib/ser/modules/sl.so" loadmodule "/usr/local/lib/ser/modules/tm.so" loadmodule "/usr/local/lib/ser/modules/acc.so" loadmodule "/usr/local/lib/ser/modules/rr.so" loadmodule "/usr/local/lib/ser/modules/maxfwd.so" loadmodule "/usr/local/lib/ser/modules/registrar.so" loadmodule "/usr/local/lib/ser/modules/auth.so" loadmodule "/usr/local/lib/ser/modules/auth_db.so" loadmodule "/usr/local/lib/ser/modules/usrloc.so"

#parameter settings

#usr module param modparam("usrloc", "db_mode", 1)

#acc module param modparam("acc", "log_level", 1) modparam("acc", "log_flag", 1) modparam("acc", "log_fmt", "mpoft") modparam("acc", "report_ack", 1) modparam("acc", "db_url", "mysql://ser:heslo@localhost/ser") modparam("acc", "db_flag", 2) modparam("acc", "db_missed_flag", 3)

#auth_db param mysql authentication active modparam("auth_db", "calculate_ha1", yes) modparam("auth_db", "password_column", "password")

#rr module param modparam("rr", "enable_full_lr", 1)

####routing logic####

route{

if (!mf_process_maxfwd_header("10")) { sl_send_reply("483","To many HOPS"); break; };

if (msg:len >= max_len ) { sl_send_reply("513","Message too big"); break; };

if (uri==myself) {

if (method=="REGISTER") {

if (!www_authorize("server4all", "subscriber")) { www_challenge("server4all", "1"); break; };

save("location"); break; };

if (method=="INVITE") record_route(); setflag(1); };

if (!t_relay()) { sl_reply_error(); }; }

Thanks a lot,

Iqbal wrote:

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

...

guys, just want to ask if i can make ser to account session durations for billing purposes?

Thanks,

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

Hi Iqbal, I downloaded the gettingstarted_04.pdf and i'm now reading it. I didn't know that there is something like that document that explains every single detail of the ser.cfg for the benefit of the newbies. Well i'm a newbie and i think i must read it... i was configuring SER without even knowing the function of each parameter, i keep on guessing and testing just to know what this thing do... well that's very nice and i really thanks all the people here in this mailing list that helps the newbies like me to understand the SER in almost detail.

Thanks =) Ryan

Iqbal wrote:

Hi

quite a few probs in the config,I cant see you using record_route anywhere which means that your messages will not pass through SER, hence you will never (almost never) see a BYE, oops my mistake, there is a record_route, but you are just doing it on the INVITE, you need to do it for all messages which are not REGISTER, then you have to setflag everywhere, I know this may sound a little vague, but I would suggest downloading the startup config from www.onsip.org, in version 1 they have a very simple setup which will allow you to make IP -IP calls, and then they build from there, so try it out.

Iqbal

Ryan Pagquil wrote:

...

Hi Iqbal,

  Ah ok, those queries will only generate the duration of INVITE 

to BYE methods data from my database. But how can I get INVITE and BYE accounted in my ser.cfg? I have this ser.cfg that I made but it is not running, it gaves me an error (core dumped). Well just want to consult you on how can I make this ser.cfg to work and account for INVITE's and BYE's. Like just I said, I'm a very newbie with ser =).

This is the config file:

#debug=7 fork=no log_stderror=yes

check_via=no dns=no port=5060 listen=10.0.1.5 fifo_db_url="mysql://ser:heslo@localhost/ser" fifo="/tmp/ser_fifo"

#load needed modules

loadmodule "/usr/local/lib/ser/modules/mysql.so" loadmodule "/usr/local/lib/ser/modules/sl.so" loadmodule "/usr/local/lib/ser/modules/tm.so" loadmodule "/usr/local/lib/ser/modules/acc.so" loadmodule "/usr/local/lib/ser/modules/rr.so" loadmodule "/usr/local/lib/ser/modules/maxfwd.so" loadmodule "/usr/local/lib/ser/modules/registrar.so" loadmodule "/usr/local/lib/ser/modules/auth.so" loadmodule "/usr/local/lib/ser/modules/auth_db.so" loadmodule "/usr/local/lib/ser/modules/usrloc.so"

#parameter settings

#usr module param modparam("usrloc", "db_mode", 1)

#acc module param modparam("acc", "log_level", 1) modparam("acc", "log_flag", 1) modparam("acc", "log_fmt", "mpoft") modparam("acc", "report_ack", 1) modparam("acc", "db_url", "mysql://ser:heslo@localhost/ser") modparam("acc", "db_flag", 2) modparam("acc", "db_missed_flag", 3)

#auth_db param mysql authentication active modparam("auth_db", "calculate_ha1", yes) modparam("auth_db", "password_column", "password")

#rr module param modparam("rr", "enable_full_lr", 1)

####routing logic####

route{

           if (!mf_process_maxfwd_header("10")) {
                   sl_send_reply("483","To many HOPS");
                   break;
           };

           if (msg:len >= max_len ) {
                   sl_send_reply("513","Message too big");
                   break;
           };

           if (uri==myself) {

                   if (method=="REGISTER") {

                   if (!www_authorize("server4all", "subscriber")) {
                           www_challenge("server4all", "1");
                           break;
                   };

           save("location");
           break;
           };

           if (method=="INVITE") record_route();
           setflag(1);
           };

           if (!t_relay()) {
           sl_reply_error();
   };

}

Thanks a lot,

Iqbal wrote:

...

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

> guys, > just want to ask if i can make ser to account session > durations > for billing purposes? > > Thanks, > >

---

Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

Hi,

I got a copy of that document. Please guide me in my learning process, I will post my questions if i have any...

Thank you very much everybody,

Ryan Java Rockx wrote:

Ryan,

I'd also recommend getting a copy of the Getting Started document from http://onsip.org/ because we documented many aspects of using SER.

For the accounting you basically need to

1. enable the mysql module (see the onsip.org docs for this)

2. tell ser to account for any particular message. this is done by

setting a flag, usually with a call to setflag(1)

So in general you need to do this in you ser.cfg

modparam("acc", "db_url", "mysql://ser:heslo@192.168.2.10/ser") modparam("acc", "db_flag", 1) modparam("acc", "db_missed_flag", 1) modparam("acc", "log_fmt", "cdfimorstup") modparam("acc", "log_level", 1) modparam("acc", "failed_transactions", 1) modparam("acc", "report_cancels", 1) modparam("acc", "report_ack", 0)

route {

   # sanity checks

   if (method=="INVITE" || method=="BYE" || method=="CANCEL") {
           # enable accounting for INVITE and BYE and CANCEL messages.
           setflag(1);
   };

   # other ususal stuff

}

Regards, Paul

On 7/6/05, Ryan Pagquil rpagquil@philonline.com wrote:

...

Hi Iqbal,

  Ah ok, those queries will only generate the duration of INVITE to

BYE methods data from my database. But how can I get INVITE and BYE accounted in my ser.cfg? I have this ser.cfg that I made but it is not running, it gaves me an error (core dumped). Well just want to consult you on how can I make this ser.cfg to work and account for INVITE's and BYE's. Like just I said, I'm a very newbie with ser =).

This is the config file:

#debug=7 fork=no log_stderror=yes

check_via=no dns=no port=5060 listen=10.0.1.5 fifo_db_url="mysql://ser:heslo@localhost/ser" fifo="/tmp/ser_fifo"

#load needed modules

loadmodule "/usr/local/lib/ser/modules/mysql.so" loadmodule "/usr/local/lib/ser/modules/sl.so" loadmodule "/usr/local/lib/ser/modules/tm.so" loadmodule "/usr/local/lib/ser/modules/acc.so" loadmodule "/usr/local/lib/ser/modules/rr.so" loadmodule "/usr/local/lib/ser/modules/maxfwd.so" loadmodule "/usr/local/lib/ser/modules/registrar.so" loadmodule "/usr/local/lib/ser/modules/auth.so" loadmodule "/usr/local/lib/ser/modules/auth_db.so" loadmodule "/usr/local/lib/ser/modules/usrloc.so"

#parameter settings

#usr module param modparam("usrloc", "db_mode", 1)

#acc module param modparam("acc", "log_level", 1) modparam("acc", "log_flag", 1) modparam("acc", "log_fmt", "mpoft") modparam("acc", "report_ack", 1) modparam("acc", "db_url", "mysql://ser:heslo@localhost/ser") modparam("acc", "db_flag", 2) modparam("acc", "db_missed_flag", 3)

#auth_db param mysql authentication active modparam("auth_db", "calculate_ha1", yes) modparam("auth_db", "password_column", "password")

#rr module param modparam("rr", "enable_full_lr", 1)

####routing logic####

route{

           if (!mf_process_maxfwd_header("10")) {
                   sl_send_reply("483","To many HOPS");
                   break;
           };

           if (msg:len >= max_len ) {
                   sl_send_reply("513","Message too big");
                   break;
           };

           if (uri==myself) {

                   if (method=="REGISTER") {

                   if (!www_authorize("server4all", "subscriber")) {
                           www_challenge("server4all", "1");
                           break;
                   };

           save("location");
           break;
           };

           if (method=="INVITE") record_route();
           setflag(1);
           };

           if (!t_relay()) {
           sl_reply_error();
   };

}

Thanks a lot,

Iqbal wrote:

...

put those queries in mysql and it should lookup your acc table

Iqbal

Ryan Pagquil wrote:

...

Hi, So the only concept on how will I account for duration is to account for INVITE and BYE methods...BTW, where will I put those queries? Sorry, I'm in a stage of discovering SER.=)

Thanks,

Java Rockx wrote:

...

Ryan,

This MySQL query should do the trick. It joins the acc table to itself. One side grabs INVITE messages and the other side grabs BYE messages.

NOTE: This will not work if you account for re-INVITE messages because you will then have multple INVITEs for the same call.

Regards, Paul

SELECT t1.from_uri as orig_number, t1.to_uri as term_number, TIMEDIFF(t2.time, t1.time) as duration, t1.timestamp 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'

On 7/4/05, Iqbal iqbal@gigo.co.uk wrote:

...

its records the INVITE and BYE, so fro there you can work out the session duration

Iqbal

Ryan Pagquil wrote:

>guys, > just want to ask if i can make ser to account session durations >for billing purposes? > >Thanks, > > > > _______________________________________________ Serusers mailing list serusers@lists.iptel.org http://lists.iptel.org/mailman/listinfo/serusers

-- Ryan Pagquil Infodyne Inc. - PhilOnline.com 3603 Antel Global Corporate Center Doña Julia Vargas Ave. Ortigas Center Pasig City Tel: 687-0715 Web: www.philonline.com