Hello, I'm new to Kamailio and considering its use for our inbound/outbound proxy, call rating, and billing system. I'd like to get your feedback on my current approach and understand the best practices for this scenario. Currently, I'm using SQL queries within my Kamailio configuration to handle authorization and rating. Here's a simplified example:
# Call authorization route[DISPATCH] { if (is_method("INVITE") && isbflagset(FLB_SRC_CARRIER)) {
if ($au == $null) { $var(acc_query) = $_s(SELECT ct.tag as tag, ct.customer_id as customer_id, cast(cb.nobal_amt as integer) as nobal_amount, cast(cb.cash as integer) as cash, ct.digits, cast(ct.retail_rate as varchar) as retail_rate, cast(ct.wholesale_rate as varchar) as wholesale_rate, ct.dispatcher_group, cast(ct.cps as integer) as cps from customer_ip ip INNER JOIN customer_balance cb ON (ip.customer_id = cb.customer_id) INNER JOIN customer_tariff ct ON (ip.customer_id = ct.customer_id) INNER JOIN customers cust ON (ip.customer_id = cust.customer_id) WHERE ip_address = '$si' AND '$rU' LIKE ct.digits || '%' order by ct.digits desc limit 1;); } else { $var(acc_query) = $_s(SELECT ct.tag as tag, ct.customer_id as customer_id, cast(cb.nobal_amt as integer) as nobal_amount,cast(cb.cash as integer) as cash, ct.digits, cast(ct.retail_rate as varchar) as retail_rate, cast(ct.wholesale_rate as varchar) as wholesale_rate, ct.dispatcher_group, cast(ct.cps as integer) as cps from customer_auth auth INNER JOIN customer_balance cb ON (auth.customer_id = cb.customer_id) INNER JOIN customer_tariff ct ON (auth.customer_id = ct.customer_id) INNER JOIN customers cust ON (auth.customer_id = cust.customer_id) WHERE auth.subcriber = '$au' AND '$rU' LIKE ct.digits || '%' order by ct.digits desc limit 1;);
$dlg_var(auth) = "1";
} xalert("query is $var(acc_query)"); if (sql_xquery("dbh", "$var(acc_query)", "row") == 1) { $dlg_var(account) = $xavp(row[0]=>customer_id); $dlg_var(dispatcher_group) = $xavp(row[0]=>dispatcher_group); $dlg_var(retail_rate) = $xavp(row[0]=>retail_rate); $dlg_var(wholesale_rate) = $xavp(row[0]=>wholesale_rate); $dlg_var(cps) = $xavp(row[0]=>cps); $dlg_var(service_plan) = $xavp(row[0]=>tag); # $var(cash) = $xavp(row[0]=>cash); xalert("account: $xavp(row[0]=>customer_id) cash: $xavp(row[0]=>cash) retail_rate: $xavp(row[0]=>retail_rate) wholesale rate: $xavp(row[0]=>wholesale_rate) group: $xavp(row[0]=>dispatcher_group) cps: $xavp(row[0]=>cps) tag: $xavp(row[0]=>tag) \n"); if ($xavp(row[0]=>cash) <= $xavp(row[0]=>nobal_amount)) { sl_send_reply("403", "Insufficient balance"); exit; }
append_hf("X-Account: $dlg_var(account),$dlg_var(retail_rate),$dlg_var(wholesale_rate)\r\n");
} else { sl_send_reply("500", "Server Error!"); exit; }
#!ifdef WITH_CPSLIMIT # perform pipe match for INVITE if (is_method("INVITE")) { $var(invlimit) = $(dlg_var(cps){s.int}); if ($var(invlimit) > 0) { if (!pl_check("$si-$dlg_var(dispatcher_group)", "TAILDROP", "$var(invlimit)")) { pl_drop("5"); exit; } } } #!endif
# round robin dispatching on gateways group '1' if(!ds_select_dst("$dlg_var(dispatcher_group)", "4")) { send_reply("404", "No destination"); exit; }
t_on_failure("RTF_DISPATCH"); route(RELAY); exit; } }
…… ……. ……
# Billing and Rating using acc_cdrs and a customer_balance table event_route[dialog:end] { $var(call_duration) = $DLG_lifetime; # Fetch call duration $avp(originator) = $dlg_var(originator); $avp(accountcode) = $dlg_var(account); $avp(caller) = $dlg_var(caller); $avp(callee) = $dlg_var(callee); $avp(pdd) = $dlg_var(pdd); $avp(service_plan) = $dlg_var(service_plan); $avp(hangup_source) = 'CALLEE';
$avp(hangup_code) = '200'; $avp(hangup_reason) = 'Normal Clearing';
$avp(elapsed) = ( $TV(s) - $dlg_var(init_timestamp) );
if ($dlg_var(auth) != $null) { setbflag(FLB_SRC_CARRIER); }
if (isbflagset(FLB_SRC_CARRIER) && $dlg_var(originator) == "CUSTOMER") { $avp(hangup_source) = 'CALLER'; } if (isbflagset(FLB_SRC_PBX) && $dlg_var(originator) == "SYSTEM") { $avp(hangup_source) = 'CALLER'; }
# Calculate billed amount using postgres query $var(bill_query) = $_s(select cast(round($dlg_var(retail_rate)/60::numeric * $var(call_duration), 4) as varchar) as bill;);
if (sql_xquery("dbh", "$var(bill_query)", "row") == 1) { $avp(billed) = $xavp(row[0]=>bill); }
if (isbflagset(FLB_SRC_CARRIER)) { $var(query) = "UPDATE customer_balance set cash = cash - (" + $dlg_var(retail_rate) + " / 60 * " + $var(call_duration) + ") WHERE customer_id = " + $dlg_var(account) + ";"; sql_xquery("dbh", "$var(query)", "ra"); } # xalert("~~~~~ Call ended (duration: $var(call_duration)) total_billed: $avp(billed) ~ $avp(hangup_source) ~ $T(reply_code) ~ $T(reply_reason) ~ $T_reply_code ~ \n");
}
My questions are: • Is this a sustainable approach for a Kamailio billing/rating setup? Are there performance or scalability concerns? • Would it be better to handle rating and billing directly on our FreeSWITCH B2BUA? What are the advantages and disadvantages of each approach? • Are there best practices or alternative solutions I should consider? Thank you in advance for sharing your experience and insights!
Hello, any query you make from Kamailio, you'll have to handle it yourself, so the load will be affected, of course. There are alternatives to use such as CGRates, or simply through reading JSON socket events. Also, avoid performing any procedures within the B2BUA, whether it's FreeSwitch or Asterisk. The more external your billing control is, the better performance you can achieve. Here's some material for you to check out:
https://github.com/cgrates/cgrates https://www.kamailio.org/events/2015-KamailioWorld/Day2/14-Dan.Bogos-Advance... https://www.youtube.com/watch?v=Hsvcwleb-fY&t=2s
This is my humble opinion, within what I can contribute ;).
regards
On Apr 19, 2024, at 1:02 PM, Ghulam Mustafa via sr-users sr-users@lists.kamailio.org wrote:
Hello, I'm new to Kamailio and considering its use for our inbound/outbound proxy, call rating, and billing system.
Hello, thank you for flying Kamailio.
I'd like to get your feedback on my current approach and understand the best practices for this scenario. Currently, I'm using SQL queries within my Kamailio configuration to handle authorization and rating. Here's a simplified example:
[snip]
My questions are: • Is this a sustainable approach for a Kamailio billing/rating setup? Are there performance or scalability concerns? • Would it be better to handle rating and billing directly on our FreeSWITCH B2BUA? What are the advantages and disadvantages of each approach? • Are there best practices or alternative solutions I should consider? Thank you in advance for sharing your experience and insights!
I’ll be pleasantly surprised if Alex hasn’t responded by the time I’ve written this and I’m sure that should he respond, he will provide an excellent, verbose opinion different than mine.
I say that to say this:
One of the wonderful things about Kamailio is your ability to do things in the way that fits your needs. One size doesn’t fit all here… there are many different approaches and choosing what’s best for you will be different than what may be best for someone else.
This said, I’d first caution you that if you want to run sql from Kamailio, I’d highly recommend using SQL transformations (https://www.kamailio.org/wikidocs/cookbooks/5.8.x/transformations/#sql-trans...) among other considerations when running raw queries.
With that out of the way, there’s many different approaches to billing that really become business decisions. It also needs to take into consideration taxes, fees, and other pass through or assessments for your local area (as well as the local area of the origination and termination locations). Good idea to work with accountants and lawyers before locking yourself into a model.
Kamailio has several outgoing rating engines (see Kamailio Modules) and many use sql lookups against their carrier rates to determine the least coat.
You may want to look at these as well as CGRATES before reinventing the wheel here.
But, using a complex sql query does have concerns when there’s high call rates and sometimes is best to instead use an API / cache / etc system. If you do run sql, I’d recommend the sanitization discussed above.
Sorry for the non-answer, but there’s really not a “this is what to do” answer.
—Fred Posner qxork.com http://qxork.com/
Regards,
Fred Posner p: +1 (352) 664-3733 https://qxork.com
I think Fred's answer is correct, complete and I have no amendments to offer. :-)
-- Alex
On Apr 19, 2024, at 8:34 AM, Fred Posner via sr-users sr-users@lists.kamailio.org wrote:
On Apr 19, 2024, at 1:02 PM, Ghulam Mustafa via sr-users sr-users@lists.kamailio.org wrote:
Hello, I'm new to Kamailio and considering its use for our inbound/outbound proxy, call rating, and billing system.
Hello, thank you for flying Kamailio.
I'd like to get your feedback on my current approach and understand the best practices for this scenario. Currently, I'm using SQL queries within my Kamailio configuration to handle authorization and rating. Here's a simplified example:
[snip]
My questions are: • Is this a sustainable approach for a Kamailio billing/rating setup? Are there performance or scalability concerns? • Would it be better to handle rating and billing directly on our FreeSWITCH B2BUA? What are the advantages and disadvantages of each approach? • Are there best practices or alternative solutions I should consider? Thank you in advance for sharing your experience and insights!
I’ll be pleasantly surprised if Alex hasn’t responded by the time I’ve written this and I’m sure that should he respond, he will provide an excellent, verbose opinion different than mine.
I say that to say this:
One of the wonderful things about Kamailio is your ability to do things in the way that fits your needs. One size doesn’t fit all here… there are many different approaches and choosing what’s best for you will be different than what may be best for someone else.
This said, I’d first caution you that if you want to run sql from Kamailio, I’d highly recommend using SQL transformations (https://www.kamailio.org/wikidocs/cookbooks/5.8.x/transformations/#sql-trans...) among other considerations when running raw queries.
With that out of the way, there’s many different approaches to billing that really become business decisions. It also needs to take into consideration taxes, fees, and other pass through or assessments for your local area (as well as the local area of the origination and termination locations). Good idea to work with accountants and lawyers before locking yourself into a model.
Kamailio has several outgoing rating engines (see Kamailio Modules) and many use sql lookups against their carrier rates to determine the least coat.
You may want to look at these as well as CGRATES before reinventing the wheel here.
But, using a complex sql query does have concerns when there’s high call rates and sometimes is best to instead use an API / cache / etc system. If you do run sql, I’d recommend the sanitization discussed above.
Sorry for the non-answer, but there’s really not a “this is what to do” answer.
—Fred Posner qxork.com http://qxork.com/
Regards,
Fred Posner p: +1 (352) 664-3733 https://qxork.com
Kamailio - Users Mailing List - Non Commercial Discussions To unsubscribe send an email to sr-users-leave@lists.kamailio.org Important: keep the mailing list in the recipients, do not reply only to the sender! Edit mailing list options or unsubscribe:
-
Alex Balashov -
Fred Posner -
Ghulam Mustafa -
Ricardo Castillo