Tag Archives: a2billing

A2Billing useful MySQL queries

I am not condoning anyone who does not understand MySQL or the internal workings of A2Billing to be messing around with MySQL commands; however occassionally you do need to, to save yourself a lot of time and effort. I’m intending to update this page with MySQL commands for specific jobs as I work them out, for my future reference mostly.

I’ve posted before about various A2Billing MySQL queries, so this is a down-and-dirty page of commands, not a full explanation of what each does or why you might need them.

Here’s the first one:
You have 2 tarrifs setup, and you need to change the sellprice of all the rates in tariff 2 to match those of tariff 1:

use mya2billing;
update cc_ratecard as a, cc_ratecard as b
set a.rateinitial = b.rateinitial where a.dialprefix=b.dialprefix and a.idtariffplan=2

 

Next up it’s bills. How to find out the total bill for calls ever, for all customers?

use mya2billing;
select sum(sessionbill),card_id, stoptime from cc_call group by card_id

And similaly to get the unbilled usage (assuming a billing date of 1st of the month):

select sum(sessionbill),card_id, stoptime from cc_call where stoptime >
DATE_FORMAT(LAST_DAY(DATE_SUB(NOW(), INTERVAL 0 MONTH)),'%Y-%m-01 00:00:00')  group by card_id

Next is useful sort of query – it gives you the outgoing calls made by a customer between 2 dates, and finds the name of the destination as well as naming the columns for a nicely exportable CSV from MySQL Workbench:

select cc_call.starttime as "Time",cc_call.sessiontime as "Seconds",cc_call.calledstation as "Called Number",cc_call.sessionbill as "Cost",cc_call.src as "Source",cc_prefix.destination as "Destination" from cc_call left join cc_prefix on cc_call.destination=cc_prefix.prefix where cc_call.card_id = "1" and cc_call.starttime between '2015-02-01 00:00:00' and '2015-03-01 23:59:00' order by cc_call.starttime DESC;

Note this query has been updated to account for your destinations not being complete (IE some may not have a name, and would not have shown up in your list).

A2Billing – invalid rate card expiry date 0000-00-00

Recently I’ve had a few calls about rate cards in A2Billing not working as expected. Everything looked good except the expiry date on the rate card was showing as 0000-00-00 00:00. This caused the rates in that rate card not to be evaluated as part of the call plan.

It looks like this is caused by a limitation in 32 bit Linux with the last date able to be represented as 19 January 2038. See here for more information about this – http://en.wikipedia.org/wiki/Year_2038_problem

When you create a new rate card, A2Billing tries to add 25 years to the current date to generate the expiry date for the rate card. Since 19 January 2013 this would have taken it past the last valid date in 32 bit Linux – causing the 0000-00-00 00:00

Continue reading

A2Billing – PostPay, Invoices and Customers Balances

So there’s a lot of info about how to setup A2Billing, import rates etc, but not really much on how to manage it thereafter.

This is what I’ve found through much trial and much more error, about what things mean and how they work. Pre-pay billing is where A2Billing is aimed, but what about post-pay billing, which is also an option?

Lets start with a simple example: we have a customer on a post-paid account and a billing date of the 1st of the month. We are not applying VAT with A2B at this stage, and really the customer isn’t self-billing, we are invoicing them separately and keeping track of things outside of A2B at the moment.
At this point the customer’s balance is 0 – they haven’t made any calls

We will reference 2 sections of A2Billing at each stage: ‘CB’ meaning Customer Balance as shown in Customers > Add/Search screen. This is what is used to allow calls or not, depending on your CB and credit limit. The second reference is ‘CBs’ which is Customers Balances found under Billing > Customers Balances. There are 4 fields relevant today: Credit, Invoice, Payment, To Pay.

Continue reading

Integrating OpenSIPS with Asterisk and A2Billing

Below are links to the 3 parts of a post covering integrating OpenSIPS with Asterisk and A2Billing. The setup described uses OpenSIPS handling A2Billing customer SIP requests for calls (for example when providing a wholesale service to other SIP PBXs).

It’s based on OpenSIPS v1.8 / Asterisk v1.8 / A2Billing v2.01

Please take security in to consideration with VOIP whether testing or in a live situation.

Part 1 – Overview of using A2Billing and OpenSIPS together

Part 2 – Setting up the OpenSIPS MySQL database and integrating it with the A2Billing MySQL database

Part 3 – The opensips.cfg config file

A2Billing and OpenSIPS – Part 3

This post has the actual config of OpenSIPS described in part 1 and part 2. Some of this config will not make sense unless you read those parts.

First a warning … many, many people want to use your call credit!! Make sure that your systems are secure. If only the OpenSIPS server needs to talk to your A2Billing/Asterisk servers over SIP then use a fierwall to block other connections.

In the configuration below OpenSIPS does not handle the Audio/RTP traffic, this is passed directly to the Asterisk/A2Billing server.

The code below is the whole opensips.cfg file, just broken up with some description. All indentation has been removed, apologies if this sometimes makes it difficult to read.

First some global settings, including the IP address of the OpenSIPS server –

listen=udp:1.1.1.1:5060 # CUSTOMIZE ME
debug=1
log_stderror=no
log_facility=LOG_LOCAL6
fork=yes
children=4
dns_try_ipv6=no
auto_aliases=no
disable_tcp=yes
disable_tls=yes
server_signature=no

next we load the modules that are required –

mpath="/usr/local/lib/opensips/modules/"
loadmodule "signaling.so"
loadmodule "sl.so"
loadmodule "tm.so"
loadmodule "rr.so"
loadmodule "maxfwd.so"
loadmodule "sipmsgops.so"
loadmodule "mi_fifo.so"
loadmodule "uri.so"
loadmodule "db_mysql.so"
loadmodule "avpops.so"
loadmodule "acc.so"
loadmodule "dispatcher.so"
loadmodule "permissions.so"
loadmodule "dialog.so"
loadmodule "siptrace.so"
loadmodule "auth.so"
loadmodule "auth_db.so"

Continue reading

A2Billing and OpenSIPS – Part 2

In this part of the guide I’m going to look at some of the set up required for the example config to work.

We are going to enable IP and USER/SECRET authentication for our A2Billing SIP customer calls. To do that we are going to create a couple of MySQL VIEWS in the ‘opensips’ database that point to tables in the ‘a2billing’ database. This means we can create our A2Billing SIP users as normal, and they will then become valid users in OpenSIPS.

Part 2 and Part 3 of the guide assume the following –

  • OpenSIPS server IP = 1.1.1.1
  • A2Billing server IP = 2.2.2.2
  • MySQL server IP = 2.2.2.2   (same server as A2Billing)
  • domain name for our calls = mya2billingcalls.com

On the A2Billing/Asterisk server

So now we create our VIEWS in the database. We are going to use the ‘address’ table for IP authentications and the ‘subscriber’ table for USER/SECRET authentications.

Here we create the ‘address’ VIEW –

DROP TABLE opensips.address

CREATE VIEW opensips.address AS
SELECT id AS id,
CAST(1 AS DECIMAL) AS grp,
host AS ip,
CAST(32 AS DECIMAL) AS mask,
cast(port as DECIMAL) AS port,
'UDP' AS proto,
'' AS pattern,
accountcode AS context_info
from a2billing.cc_sip_buddies
WHERE host != 'dynamic';

Continue reading

A2Billing and OpenSIPS – Part 1

This is part 1 of a 3 part post discussing A2Billing and OpenSIPS. A2Billing is a billing platform for Asterisk, and OpenSIPS is an Open Source SIP Server. In this first part I’m going to talk about what OpenSIPS is and why you may want to use it. In the second part I’ll talk about some prerequisites for the setup I’m going to show, and in the third part will be the OpenSIPS config.

A2Billing works perfectly well without OpenSIPS, so why would you want to use them together? Well, with OpenSIPS sitting in front of A2Billing/Asterisk and handling all of the SIP connections it can provide the following benefits –

  • load balance across multiple Asterisk/A2Billing servers
  • failover – take an Asterisk server out of the cluster if it should fail
  • limit SIP connections so that only the OpenSIPS server talks to Asterisk/A2Billing over SIP
  • register all of your SIP customers in a single place – the OpenSIPS server (the config I show is not going to cover SIP registrations)
  • OpenSIPS has much better logging of SIP connections (than Asterisk) so we can use fail2ban more efficiently to block attacks

There are probably many more benefits than those listed above. OpenSIPS has lots of modules that provide flexibility to handle the SIP connections exactly as you need.

In the config that follows I am going to show how to do SIP termination. SIP clients authenticate to OpenSIPS using either IP or USER/SECRET authentication and then calls are passed to A2Billing/Asterisk for completion. This example does not cover SIP registrations or incoming DID numbers.

OpenSIPS will sit between the A2Billing SIP customers and the A2Billing/Asterisk server. All customer SIP connections will be to the OpenSIPS server, which will then pass these on to Asterisk/A2Billing once authenticated. A2Billing/Asterisk will talk to the call provider directly (not via OpenSIPS). So the setup looks something like this –

A2Billing SIP Customer  -->  OpenSIPS  -->  A2Billing/Asterisk  --> Call provider
                                       -->  A2Billing/Asterisk  --> Call provider
                                       -->  A2Billing/Asterisk  --> Call provider

This diagram above shows calls going to 3 different A2Billing/Asterisk servers. In the example config there is just one set up, but it will be obvious how to add more.

Also, in OpenSIPS there are 2 different ‘load balancing’ modules. There is one called ‘dispatcher’ which in unintelligent and just send the calls to a group of A2Billing/Asterisk servers. And there is a module called ‘load-balancer’ which knows the state of each A2Billing/Asterisk server and evenly distributes the load across them. For simplicity in this example I will be using the ‘dispatcher’ module.

This guide assumes that you have –

  • a working A2Billing/Asterisk server in place
  • a working OpenSIPS v1.8 server in place
  • created a database called ‘opensips’ (as per the OpenSIPS install instructions) that is on MySQL running on the A2BIlling/Asterisk server

We are going to have both the A2Billing and OpenSIPS databases running on the A2Billing server so that we can integrate the two

In part 2 I’ll discuss some of the prerequisites and the database setup.

Stats for SysAdminMan YouTube channel

I started doing some training videos on YouTube about a year ago when I was getting more requests for FreePBX and A2Billing training than I could cope with. The videos have proved very popular and today I was looking at some of the reports produced by YouTube.

The graphs below are just for the last 3 months as YouTube only started producing ‘Minutes Watched’ statistics from September 1st.

Just in the last 3 months there have been 10,000 views and over 25,000 minutes watched!

YouTube analytics

and it looks like VOIP is a pretty male dominated arena –

VOIP sex

Click here to check out the SysAdminMan YouTube channel yourself

A2Billing drops call when * (star) pressed

I saw this on a customer’s system where calls were being disconnected when the * button was pressed during the call.

This was being caused by 2 settings. The first being the default Asterisk feature code for disconnecting a call. You can see these at the Asterisk CLI like this –

Connected to Asterisk 1.8.5.0 currently running on uk1 (pid = 5257)
server1*CLI> features show
Builtin Feature           Default Current
---------------           ------- -------
Pickup                    *8      *8
Blind Transfer            #       #
Disconnect Call           *       *

So you can see there is a default feature code for disconnecting the call by pressing * Continue reading

A2Billing release Version 2 upgrade

A couple of days ago Star2Billing announced the latest release of their popular open source call billing platform – A2Billing.

This release takes it from version 1.9.4 to 2.0.1. Here is a list of items updated in the latest release –

  • Major security improvements to the UI
  • New Paypal API to support HTTP 1.1
  • Support for all Asterisk versions including Asterisk 11
  • Enhanced DID billing including peak and off peak rates for A-leg
  • New least cost routing mode to select the lowest cost rate from a range of carriers
  • New API added for third party phones to retrieve and display the balance
  • Tax and Country added to bulk creation of accounts
  • Code cleaning to adhere to PHP coding standards

One of the most interesting additions is the update of the least cost routing model. You could always create different outbound routes in A2Billing and calls would be routed via the least expensive. However, A2Billing would always match the most explicit rate, meaning sometimes calls did not choose the least expensive trunk.

For example, if you had two carriers for calls to the UK, and in their rate cards rates were listed like this –

Carrier A  –  Destination : 441604  –  Cost : $0.05

Carrier B  –  Destination : 441  –  Cost : $0.01

A2Billing would choose to route calls to 441604283000 via Carrier A because the rate matched more closely.

This has been resolved in A2Billing v2 and rates are now matched on all rate cards before the least expensive route is chosen.

We’re currently testing A2Billing v2 and will likely upgrade the hosting templates available shortly.

For more information about A2Billing hosting visit the website – sysadminman.net/a2billing-2go-hosting.html

More information about A2Billing can be found on the Star2Billing website