Abiquo 5.0

Skip to end of metadata
Go to start of metadata

The DBMS billing integration outputs usage data to a database table for billing. The database table may be hosted on any suitable MySQL database server (although we would recommend that you do NOT use the Abiquo server's Database server). 


Create the 'target' Database, and User Account for access

On your target database server, ensure that you create a database schema to hold the billing data, and a user account to access it. The following MySQL SQL can be used to help you do that:

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

You should adjust the follow values to suit your requirements:

  • abiquo_billing
  • bill
  • password

Configure DBMS Connector behavior

The settings for controlling the behavior of the DBMS connector are described below.

PropertyDefaultDescription
dbms_connection_url Location of the  the 'target' DBMS server (that will hold the generated billing data)
e.g. dbms_connection_url=jdbc:mysql://127.0.0.1:3306/abiquo_billing
dbms_user User for connecting to the 'target' DBMS server (that will hold the generated billing data)
e.g. dbms_user=bill
dbms_password User for connecting to the 'target' DBMS server (that will hold the generated billing data)
e.g. dbms_password=mypassword
dbms_jdbc_driver

dbms_jdbc_driver

Driver for connecting to the 'target' DBMS server (that will hold the generated billing data).
Currently only the default value of 'com.mysql.jdbc.Driver' is supported.
table_nameabiquo_billing_dataThe name of table in which the generated billing data is stored in the target database. Changing this setting after the billing integration has been run may cause a new table to be generated, however the new table will not contain any old/existing data.
validate_table

true

If true, the Billing Integration will check that the DBMS table exists, and that it contains suitable database columns to hold the configured data. It is strongly recommended that the default value of "true" is used.
mysql_ddl

true

If the DBMS connector determines that the target table does not exist, it will automatically try to create it.

If this property is set to true, use MySQL-specific data defintion language (DDL) to create the table. The default value of 'true' is recommended for MySQL target databases.

provider_mapping_name 

This is the name of the billing attribute name (defined in the billing_account_mapping table) which the DBMS connector will obtain the Billing System customer ID from.
e.g. provider_mapping_name=ZUORA_ACCOUNT_NUMBER

include_usage_costs

false

Include usage costs for a pricing template. When set to true, the DBMS connector will include additional costing columns in its output, with one additional column for each usage defined in the pricing template. Values that are not defined in the pricing templates are not covered by billing when including usage costs:

  • 'external storage' usage does not include costings; these should be costed via storage tier usage.
  • hypervisor
  • custom usages

Values that have charging periods independent of the billing charge period are not covered by billing:

  • minimum charge
  • standing charge

This is because the charging periods cannot be adjusted to the billing periods without risk of calculation error.

datacenter

false

If Physical Datacenter Billing for grouping Abiquo Datacenters is enabled in the billing.properties file, it must also be enabled in the associated Billing Connector.  If true, this property will include datacenter details in the connector output. The default value of this property is ‘false’.
Note: If this value is enabled but Physical Datacenter Billing by Abiquo Datacenter is not enabled in billing.properties, then datacenter details will be recorded as ‘n/a’.
start_period

true

Mandatory column. Timestamp of the start of the accounting period
end_period

true

Timestamp of the end of the accounting period (exclusive)

abiquo_type

true

The 'type' of the Abiquo ID (ENTERPRISE or VDC)

abiquo_id

true

The Abiquo ID value

bill_system_id

true

The Billing System's Customer ID, as defined in the 'billing_account_mapping' table

start_period

true

This mandatory column appears in all reports and it is the timestamp of the start of the accounting period

Other Columns


Set boolean values to false to exclude the following columns from the DBMS table. Do NOT change the column definitions after the billing integration has been run, since the table columns are fixed when the table is created.

end_period

true

If true, include the column containing the timestamp of the end of the accounting period (exclusive)

abiquo_type

true

If true, include the column containing the 'type' of the Abiquo ID (ENTERPRISE or VDC)

abiquo_id

true

If true, include the column containing the Abiquo ID value

bill_system_id

true

If true, include the Billing System's Customer ID, as defined in the 'billing_account_mapping' table

Configure connector properties to define billing output data

To include these columns in the usage data output file, in the {connector}.properties file set these properties to "true". Then configure any associated properties, such as name and display name.

By default all quantities are per accounting period, which by default is 1 hour. So for example, a 1 CPU VM running for the full 24 hours of a DAY Accounting period will consume a quantity of 24 CPUs.

Usage data columnsDefaultDescription

cpu

true

If true, include amount of CPU for the account period

cpu_on

trueIf true, include amount of CPU for the account period when the VM was ON

cpu_off

trueIf true, include amount of CPU for the account period when the VM was OFF

mem

true

If true, include amount of Memory for the account period (MB)

mem_on

trueIf true, include amount of Memory for the account period when the VM was ON

mem_off

trueIf true, include amount of Memory for the account period when the VM was OFF

ip

true

If true, include amount of IPs used for the account period

vlan

true

If true, include amount of VLANs used for the account period

local_storage

true

If true, include amount of Local Storage used for the Account Period (GB)

external_storage

true

If true, include amount of External Storage used for the account period (GB)

ha

trueIf true include if the VM is hosted on an Abiquo rack with high availability enabled

reserved_server_cpu

trueIf true, include amount of CPU in reserved servers that have been explicitly reserved for an Enterprise

reserved_server_memory

trueIf true, include amount of RAM in reserved servers that have been explicitly reserved for an Enterprise

repository

trueIf true, include the amount of Repository usage (GB) by the enterprise for VM Templates and Instances (conversions are not accounted)

anti_affinity

trueIf true, include if the Virtual machine is deployed in an anti-affinity layer to ensure it is deployed separately from other VMs in the same layer

firewall

trueIf true, include the number of firewalls

loadbalancer

trueIf true, include the number of load balancers

tiered_storage

false
  • If true, process tiers that are defined in billing.properties file with the storage_tier_<n>_name property
  • Define the storage tiers that you want to include in the output file, in the order that the custom columns should appear

storage_tier_<n>

 Define the order of the storage tier in the output file with a number <n> starting from 1

storage_tier_<n>_name

 The same case-sensitive name of the 'storage_tier_<n>_name' in the billing.properties file

storage_tier_<n>_display_name

 Custom column heading

local_tier_storage

false
  • If true, process datastore tiers that are defined in billing.properties file with the local_tier_storage_<n>_name property
  • Define the datastore tiers that you want to include in the output file, in the order that the custom columns should appear

local_tier_storage_<n>

 Defines the order of the datastore tier in the output file with a number <n> starting from 1

local_tier_storage_<n>_name

 The same case-sensitive name as the value of the 'local_tier_storage_<n>_name' in the billing.properties file

storage_tier_<n>_display_name

 Custom column heading

cost_codes

false
  • If true, process cost codes that are defined in billing.properties file with the cost_code_<n>_name property
  • Define the Cost Codes that you want to include in the output file, in the order you wish those cost codes to appear

cost_code_<n>

 Defines the order of the cost code in the output file with a cost code number <n> starting from 1 for the first cost code

cost_code_<n>_name

 The same case-sensitive name as the 'cost_code_<n>_name' value in the billing.properties file.

cost_code_<n>_display_name

 The customized display names can be values such as strings or account numbers, for example:
  • cost_code_1_display_name = LINUX1
  • cost_code_2_display_name = WINDOWS10

hypervisors

false
  • If true, process hypervisor usage
  • Also define the hypervisor usages in the billing.properties file with the hypervisor_<n>_name properties
  • Define the hypervisors that you want to include in the output file, in the order that the custom columns of hypervisors should appear

hypervisor_<n>

 Defines the order of the hypervisor in the output file with a hypervisor number <n> starting from 1 for the first hypervisor

hypervisor_<n>_name

 

E.g. hypervisor_1_name=VMX_04
The same case-sensitive name, which is the same as the value of the 'hypervisor_<n>_name' property in the billing.properties file

hypervisor_<n>_display_name

  hypervisor_1_display_name=VMWareESX

custom_<n>


  • e.g. custom_1=ESXHypervisors
  • If custom=true, set these properties to custom usage names to process the custom usages
  • Also define the custom usage in the billing.properties file with the custom_usage_<n>_name properties
  • Note that the usage number <n> does not need to match the number in the main billing properties file

Example dbms.properties

This is a sample file only. Please see the dbms.properties file on your billing system to check property values.

 Click here to expand...
##############
#
# DBMS Connector specific properties
#
##############

# DBMS settings used to connect to the 'target' DBMS where the billing
# data will be stored
# local connection dbms_connection_url=jdbc:mysql://localhost/kinton
dbms_connection_url=jdbc:mysql://10.81.3.30:3306/abiquo_billing
dbms_user=bill
dbms_password=password

# Name of the DBMS table that will hold the DBMS data
table_name=abiquo_billing_data

# Controls whether table validation (and creation if doesn't exist) occurs
# during initialization. Default is 'true'
#validate_table=false

# If/when auto creating the target DBMS table, determines whether MySQL specific
# DDL is used (preferred for MySQL).   Default is 'true'
#mysql_ddl=false


# name of the billing system property name to use as the DBMS Billing System ID value
provider_mapping_name=ENTERPRISE
 
# Should we include usage costings in the DBMS output?
include_usage_costs=true


# what optional information is required to qualify the billing data? 
end_period=true
abiquo_type=true
abiquo_id=true
bill_system_id=true
# Include datacenter details in connector output for billing by datacenter subgroups and datacenter filtering
# Note: if this value is enabled without Physical Datacenter being enabled in billing.properties,
# then datcenter details will be recorded as 'n/a'
datacenter=false


# These values determine whether particular usages will be included in the results
# Invalid Usages will be reported as 0 value...
cpu=true
mem=true
ip=true
vlan=true
local_storage=true
external_storage=true
ha=false
reserved_server_cpu=false
reserved_server_memory=false
repository=false
anti_affinity=false


tiered_storage=false
#storage_tier_1=false
#storage_tier_2=false
#storage_tier_2_name=Tier 2
#storage_tier_3=false
#storage_tier_4=false


# Only need to include the cost codes that are required in the DBMS table
cost_codes=false
#cost_code_1=true
#cost_code_1_name=Monowall


# Only need to include the hypervisor usages that are required in the CSV file
# Optional - the entries below have been overriden to use 'friendly' names
hypervisors=false
hypervisor_1=false
hypervisor_1_name=VMX_04
hypervisor_1_display_name=VMWareESXi
hypervisor_2=false
hypervisor_2_name=KVM
hypervisor_3=false
hypervisor_3_name=ORACLE_VM
hypervisor_3_display_name=OracleVM
hypervisor_4=false
hypervisor_4_name=XENSERVER
hypervisor_4_display_name=XenServer
hypervisor_5=false
hypervisor_5_name=HYPERV_301
hypervisor_5_display_name=HyperV
hypervisor_6=false
hypervisor_6_name=AMAZON
hypervisor_6_display_name=AmazonAWS


# Only need to include the custom usages that are required in the DBMS table
custom=true
custom_1=true
custom_1_name=ESXHypervisors