Abiquo Billing Integration User Guide

See Changes to Abiquo Billing Integration

Overview of the Abiquo Billing Integration

The Abiquo Billing Integration (billing integration) can process Abiquo Accounting data and provide it to a third party billing system to generate customer invoices for Abiquo usage.

The billing integration is a separate stand-alone utility. You should install it on a machine independent of other Abiquo software. It comprises the following components.

Core Billing Utility: Calculates the usage data and manages it on a 'per account' basis
Billing Connector: Takes the 'usage' data from the core billing utility and provides it to a third-party billing system.

There are two billing connectors

CSV: generates CSV files with Abiquo usage data
DBMS: populates a database table in any suitable MySQL database with Abiquo usage data.

After you configure billing, set up separate processes to:

Run the billing integration on a regular basis, e.g. with a weekly scheduled task
Maintain the mappings between Abiquo entities and the billing account IDs

Install and Configure the Billing Integration

To install and configure the Abiquo Billing Integration do these steps.

Select a suitable billing server to install the software
1. It must be able to connect to the main Abiquo database
2. In a test environment, it may be the Abiquo Server
3. Install billing package by running "yum install abiquo-billing"
In the Abiquo database
1. Create a user for billing and grant permissions to the user. For example
  
  CREATE USER 'billing'@'%' IDENTIFIED BY 'mypassword';
  GRANT SELECT,EXECUTE ON kinton.* TO 'billing'@'%';
  GRANT SELECT ON mysql.proc TO 'billing'@'%';
  GRANT SELECT ON kinton_accounting.* TO 'billing'@'%';
  
  note: if biling service is in the same host as Abiquo database, the user should be created with 'billing'@'localhost', instead of @'%'.
2. Define the Abiquo enterprises and VDCs that the billing integration should generate Usage data for. Add a record for each enterprise or VDC to the account mapping table.
  For example, to add mappings for all enterprises you would use the following SQL:
```
INSERT INTO kinton.billing_account_mapping (idAbiquo, idMappingType, billingAttributeName, billingAttributeValue)
(SELECT idEnterprise, 1, 'ENTERPRISE', e.name FROM kinton.enterprise e);
```
  For full details, see Populate the account mapping table for billing.
  Note: you must set up a separate process to maintain the account mapping table. For example, you can create a cron job which runs before billing cron job (see step "schedule the billing integration to run automatically"):
```
10 0 * * * root mysql --host specifythedbhost --user root --password yourpass --execute "INSERT IGNORE INTO kinton.billing_account_mapping (idAbiquo, idMappingType, billingAttributeName, billingAttributeValue) (SELECT idEnterprise, 1, 'ENTERPRISE', e.name FROM kinton.enterprise e);"
```
If you are using the DBMS connector, create a target database and MySQL user account for the billing integration. Do not use the Abiquo database. You must also grant permissions, for example.

CREATE DATABASE abiquo_billing CHARACTER SET=utf8;
CREATE USER 'bill'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON abiquo_billing.* TO 'bill'@'%';

note: if biling service is in the same host as Abiquo database, the user should be created with 'billing'@'localhost', instead of @'%'.
On the billing server, configure the billing.properties file in the same folder as the billing.sh script.
1. Set the connector_name property to "CSV" or "DBMS" for your connector. Also change billing init period to "month". And enter the details of the billing user and IP address to access the Abiquo database.
```
connector_name=CSV
init_period=MONTH
```
  For full details, see Configure billing properties
On the billing server, Configure the connector properties file in the same folder as the billing.sh script.
1. For the CSV connector, edit csv.properties.
  Set the provider_mapping_name property.
  
  provider_mapping_name=ENTERPRISE
  
  For full details of CSV connector configuration, see CSV Billing Integration
2. For the DBMS connector, edit dbms.properties.
  Set the provider_mapping_name and also add the credentials for the output database
  
  provider_mapping_name=ENTERPRISE
  dbms_connection_url=jdbc:mysql://<Bill Data IP Address>:3306/abiquo_billing
  dbms_user=bill
  dbms_password=password
  
  For full details of DBMS connector configuration, see DBMS Billing Integration
Run the billing.sh file ('./billing.sh' from the install directory) to generate the usage data.
Check the generated usage data. CSV files are in the 'CSV_files' folder of the installation directory and DBMS data is in the abiquo_billing_data table in your database.

Schedule the billing integration to run automatically

To generate billing data at regular intervals, e.g. once a week, you can schedule billing integration jobs using programs such as cron. If you are using cron, set the script's current working directory correctly in your cron job. For example, if you have installed the Abiquo Billing Integration into the '/opt/BillingIntegration' folder, then your crontab entry command could look like this:

0 1 * * * cd /opt/abiquo-billing; /bin/bash /opt/abiquo-billing/billing.sh 2>&1 | tee --append /var/log/billing.log

Note that the billing integration automatically tracks which data it has processed for each account, so it should not process usage data more than once.

Sample billing.properties file

This is a sample file only, please use the files provided with your billing integration.

Click here to expand...

##############
#
# Billing specific properties
#
##############

# Set granularity for processing Usage data
account_period=HOUR

# Convert usage units be converted to 'per hour' units (true) or use the accounting granularity (false)
usage_units_per_hour=false

# Try to get usage data for this period for any 'new' account. If not supplied, the default is the account_period
init_period=DAY

# Set the period of the bill, for example, a value of 15 means from 15/MM/YYYY to 15/MM+1/YYYY
bill_day_of_month=1

# Define the connector to use.  Currently we support 'CSV' and 'DBMS'
connector_name=CSV

# Or you can Specify a class explicitly if no connector_name is supplied:
#connector_class=com.abiquo.billing.connectors.csv.CSVConnector
#connector_class=com.abiquo.billing.connectors.dbms.DBMSConnector


# DBMS settings for reading the usage data and account mapping info:
# local connection dbms_connection_url=jdbc:mysql://localhost/kinton
dbms_connection_url=jdbc:mysql://sacedo40.bcn.abiquo.com:3306/kinton
dbms_jdbc_driver=com.mysql.jdbc.Driver
dbms_user=billing
dbms_password=myPassword

# Group billing data by physical Datacenter?
dc_grouping=true
# Filtering options to limit the datacenters included for billing:
dc_filtering=false
dc_include_filter_1=London
#dc_include_filter_2=New York


# A job ID for this Billing process. Only required if concurrent billing processes are required.
# Default id value is 1
#job_id=1

# Collect core usage counters configured below by consumption
# or by the hard limit allocation for the Enterprise/VDC
# The default is false (i.e. collect by consumption)
#allocation_usages=true


# Core usage enable/disable settings
vm_usage=false
vm_on_usage=false
vm_off_usage=false
cpu_usage=true
cpu_on_usage=false
cpu_off_usage=false
memory_usage=true
memory_on_usage=false
memory_off_usage=false
local_storage_usage=true
ip_usage=true
vlan_usage=true
external_storage_usage=true
ha_usage=false
reserved_server_cpu_usage=false
reserved_server_memory_usage=false
repository_usage=false
anti_affinity_usage=false
firewall_usage=false
loadbalancer_usage=false
hardwareprofile_usage=false
hardwareprofile_onoff_usage=false

# Tiered Storage Usages
tiered_storage_usage=false
#storage_tier_1=false
#storage_tier_2=false
#storage_tier_2_name=Tier 2 <--- Configure the non-default Tier names, if required...
#storage_tier_3=false
#storage_tier_4=false

# Local Tiered Storage Usages
local_tier_storage_usage=false
#local_tier_storage_1=false
#local_tier_storage_2=false
#local_tier_storage_2_name=Tier 2 <--- Configure the non-default Tier names, if required...
#local_tier_storage_3=false
#local_tier_storage_4=false


# backup Usages
backup_usage=false
#backup_policy_1=false
#backup_policy_2=false
#backup_policy_2_code=Backup 2 <--- Configure the Policy code
#backup_policy_3=false
#backup_policy_4=false


# DRaaS usage
draas_protection_usage=false

# Cost Code usages
cost_code_usage=false
#cost_code_1_name=Monowall

# Hypervisor usages (all disabled by default)
hypervisor_usage=false
hypervisor_1=false
hypervisor_1_name=VMX_04
hypervisor_2=false
hypervisor_2_name=KVM
hypervisor_3=false
hypervisor_3_name=ORACLE_VM
hypervisor_4=false
hypervisor_4_name=XENSERVER
hypervisor_5=false
hypervisor_5_name=HYPERV_301
hypervisor_6=false
hypervisor_6_name=AMAZON


# Custom Usage example (disabled by default)
custom_usage=false
custom_1=false
custom_1_name=RepositoryUsageGB
custom_1_ent_sql=SELECT SUM(d.diskFileSize)/(1048576*1024) FROM kinton.enterprise e JOIN kinton.virtualimage vi ON (e.idEnterprise=vi.idEnterprise) JOIN disk d on (vi.idImage = d.template_id) WHERE d.stateful=false AND e.idEnterprise={ABQ_ENTERPRISE_ID};
custom_1_vdc_sql=