Abiquo Documentation Cookies Policy

Our Documentation website uses cookies to improve your experience. Please visit our Cookie Policy page for more information about cookies and how we use them.


Abiquo 2.6


Skip to end of metadata
Go to start of metadata

Accounting Services Integration

 

 

Date

Revision Information ____________________________

2013-04-03Abiquo v2.4
2014-01-14Updated to include Amazon Integration

Copyright Notice

Copyright ©2014, Abiquo Holdings S.L. All rights are reserved. This documentation may not be reproduced in whole or in part in any manner or form (including photocopying or storing it in any medium by electronic means and whether or not transiently or incidentally to some other use of this documentation) other than in accordance with any applicable license agreement or with the prior written consent of Abiquo Holdings S.L. Any copies of this documentation must incorporate this notice.
Abiquo and the Abiquo logo are trademarks of Abiquo, Inc. Other trademarks used may be owned by third parties.

Contents

1. Introduction

Accounting features are essential for the development of pay per use models for cloud computing. Therefore, Abiquo Enterprise Edition incorporates a set of services to measure which resources are being consumed and when they are being consumed. In addition, these resources can be classified by different parameters and services.

2. Service Description

The Abiquo accounting system works in three dimensions, which are:

  • Accounted Resources
  • Accounting Resource Time Period and Granularity
  • Resource Owner(s)

In some instances, Abiquo differentiates between consumption and reservation. In other words, some resources can be billed according to the amount reserved for use (by reservation), while others can be billed according to the amount that was actually consumed (by consumption).

2.1. Recorded Resources

Abiquo tracks and reports on the following resources. Resources that are new in this version are marked in blue. Custom resources that are also new in this version are marked in pink.

The ticks in the last columns indicate the resources that are available in a Datacenter (DC) or Public Datacenter (PDC).

Resource

Description

Unit

Billing Type

Association

DCPDC

Cores

Virtual cores in virtual machines created by users

Virtual cores

Consumption

Virtual machine

(tick)(tick)

RAM

RAM in virtual machines created by users

RAM in megabytes

Consumption

Virtual machine

(tick)(tick)

Local Storage

Disk space connected to the hypervisor as datastores for virtual machine disks.
In v2.0+, secondary hard disks are local storage

Bytes

Consumption

Virtual machine

(tick)(tick)

External Storage

Disk space for use as external volumes created by users on virtual machines

Bytes

Reservation

Virtual machine or
Independent

(tick) 

Public IPs

IP addresses with Internet access for use by virtual machines

Number of IP addresses

Reservation

Virtual Machine or
Independent

(tick) 

VLANs

Private VLANs for virtual machines.
Unmanaged, external and public network VLANs are not counted
A private VLAN is automatically created when a user creates a VDC

Number of VLANs

Reservation

Virtual Datacenter

(tick)(tick)

Hypervisor Type

In v2.0+ the hypervisor type for the virtual machine

Hypervisor type

Consumption

Virtual machine

(tick)(tick)

Cost Code

Optional cost codes for virtual image templates

Cost code

Consumption

Virtual machine

(tick)(tick)
HA Hosted VMVirtual machine deployed on a high-availability rackTrue or falseConsumptionVirtual machine(tick) 
Reserved Physical
Machine Cores
CPU of each server that has been explicitly reserved for an enterprise.
Recorded even if the server is not being used.
Virtual coresReservationEnterprise(tick)(tick) 
Reserved Physical
Machine RAM
RAM of each server that has been explicitly reserved for an enterprise.
Recorded even if the server is not being used.
RAM in megabytesReservationEnterprise(tick)(tick) 
Repository Storage

Repository space is used when:

  1. Customers create a VM instance
  2. Customers upload disk images into the template repository
  3. Abiquo converts instances and templates to other hypervisor formats

Abiquo accounts for space used by instances and templates (cases 1 and 2).
Create a custom metric for conversions (case 3).

Bytes used by each
template
ConsumptionEnterprise(tick) 
Anti-affinity VMVirtual machine deployed in an anti-affinity layer separate from other VMs in the same layerTrue or falseConsumptionVirtual machine(tick) 
CustomSee Custom Accounting Resource DefinitionsUser definedUser definedUser defined(tick)(tick)

The Abiquo Amazon integration accounts for:

  • vCPU
  • vRAM
  • HDD used on the system disk
  • Elastic IPs (public IPs)
  • VPCs (VLANs)

 

2.1.1. Numeric Resource Values and Enabling or Disabling Resources

In the accounting_event_detail table, the resources are assigned a numerical value, as defined in the table below in the idAccountingResourceType column.

Since Abiquo 2.4, you can select which accounting resources are collected into the accounting_event_detail table (used by all of the main accounting views). For example, if the VLAN or Hypervisor resources are not used in your billing process, you can configure Abiquo so that it does not collect them. This will reduce the space used by the database and increase the performance of the accounting and billing processes.

Set the parameters to enable or disable resource collection in the kinton_accounting.accounting_parameters table. Each parameter has the same name as the resource it represents. The parameter values are: enable = 1 and disable = 0. By default, all parameters for pre-2.4 resources are enabled. By default, all new resources marked in blue are disabled.

 

Parameter Name

idAccountingResourceType
(in accounting_event_detail)

Enabled/Disabled
Default Value
(in accounting_parameters)

VirtualMachine-vcpu

1

1

VirtualMachine-vram

2

1

VirtualMachine-vhd

3

1

ExternalStorage

4

1

IPAddress

5

1

VLAN

6

1

VirtualMachine-hypervisorType

7

1

VirtualMachine-haHosted

8

0

ReservedPhysicalMachine-cpu

9

0

ReservedPhysicalMachine-ram

10

0

RepositoryStorage

11

0

VirtualMachine-antiAffinity120

2.2. Accounting Resource Time Period

Resource usage information is stored by accounting period size and accounting period granularity. These two parameters can be set in the "accounting_parameters" table of the kinton_accounting schema. If they are not set, then the default value of one hour is used for both parameters.

For each accounting period, the system checks for resources that have been reserved and/or consumed in the last period by granularity and records the usage data in a database table. To allow for aggregating of resources, the database usage information has appropriate ownership at virtual machine, virtual datacenter, and enterprise level.

If a resource is active for an interval of less than one accounting period, the system automatically rounds consumption up to the corresponding time interval. This means that the minimum period of consumption of a resource is always one accounting period but the value stored will depend on the granularity. Additionally, various resources may be turned on and off during an accounting period. Abiquo stores all these events but the database will return only the maximum value for them for an accounting period.

Access resource usage through the ACCOUNT_PERIOD_USAGE_VW view of the kinton_accounting_schema. The returned columns are identical to HOURLY_USAGE_MAX_2_VW but the name of the view has been changed to indicate that the accounting period can be changed.

Previous versions of Abiquo provided additional views (HOURLY_USAGE_SUM_VW, DAILY_USAGE_SUM_VW, MONTHLY_USAGE_SUM_VW).  These views are still provided for backwards compatibility but now you should use the equivalent template queries provided in a later section of this document. Abiquo does not recommend the USAGE_SUM views for performance and scalability reasons. Also, if you wish to use these views, then the accounting granularity must be less than or equal to the granularity of the view. If the granularity is incompatible, then the view will not return any data.  For example, the DAILY_USAGE_SUM_VW will work if the accounting granularity is HOUR or DAY, but will return an empty result set if the accounting granularity is WEEK or MONTH.

2.2.1. Configuring the accounting period and granularity

Parameter Name: AccountPeriodSize 
Default Value: HOUR
Description: Controls the size of the aggregated account period of the accounting_event_detail table, and may be one of the following values: HOUR, DAY, WEEK, MONTH.   If the value is bad or missing, the default value is used.
 
Parameter Name: AccountPeriodGranularity 
Default Value: HOUR
Description: Controls the granularity of the resource units within an accounting period - so for example it is possible to have an account period of DAY, but a granularity of HOUR. The granularity does not affect the minimum usage which is recorded by accounting period but it does affect the units of the minimum usage. (For example, if accounting period = DAY and granularity = HOUR, then for a single CPU VM, Abiquo would record 24 resource units for a day record; if accounting period = DAY and granularity = DAY, Abiquo will record 1 resource unit. Possible values are HOUR, DAY, WEEK and MONTH. If the value is bad or missing, the default value is used.
 
The table below shows account periods and compatible granularity. Abiquo uses calendar months, so week granularity is not compatible with months.

Accounting Period and Granularity Compatibility Table

 

Period

 

Hour

Day

Week

Month

Granularity

Hour

(tick)

(tick)

(tick)

(tick)

 

Day

 

(tick)

(tick)

(tick)

 

Week

 

 

(tick)

 

 

Month

 

 

 

(tick)

 

There are also other parameters which control the account period processing:
 
Parameter Name: MaximumPeriodsToFirstInit 
Default Value: 1
Description: This parameter is only used when the accounting_event_detail table is empty, and is used to determine how many ‘old’ accounting periods should be used to initially populate up the accounting table.   The default value is 1, which effectively means that accounting data will be generated from ‘now’.   However, for existing installations this value can be adjusted to ensure that data accounting is generated for any previous number of hours, days, weeks, or months, depending on the account period size.   The minimum number of periods to initialise is 1, and the maximum is 720 - any value outside of this range will use the default value of 1.
 
Parameter Name: MaximumPeriodsToProcess
Default Value: 24
Description: In the case of a backlog of missed accounting periods, this parameters controls how many missed accounting periods can be processed in one call to the UpdateAccounting stored procedure.   The default value is 24, which would process 1 day of accounting data assuming default settings for other parameters. This setting ensures a degree of quality of service for the DBMS, minimizing the resource impact of recovering missed accounting data. The minimum number of periods to process is 1, and the maximum is 720 - any value outside of this range will use the default value of 24.

2.2.2. Changing the accounting period of an existing Abiquo system

To change the account period size or granularity of an existing Abiquo system, first backup and remove any existing data from the kinton_accounting accounting_event_detail table. This is essential because the accounting procedure analyses the existing table content to determine the next accounting period of data. If you do not remove the existing data, this may cause duplicate accounting data or mis-aligned accounting periods.
 
To change the accounting period, do these steps:

  1. Backup the kinton_accounting.accounting_event_detail table
  2. Change the accounting parameters to the desired values 
    1. set the ‘MaximumPeriodsToFirstInit’ parameter to ensure that any old data is accounted correctly
  3. Delete the existing content of the accounting_event_detail table
  4. Monitor the content of accounting_event_detail to ensure the generated data meets your needs. If not, then repeat from step 2.

2.3. Resource Owners

Resource data is stored for the following resource owners.

1. Virtual Machine

  • Cores
  • RAM
  • Local storage
  • Cost codes (if the VM template has a cost code)

    Grouped virtual machine components and virtual machines are accounted per group, not per individual virtual machine.

2. Virtual Appliance

  • Groups of virtual machines
    • Cores
    • RAM
    • Local storage
    • Cost codes (if the VM template has a cost code)

      Grouped virtual appliances are accounted per group, not per individual virtual appliance.

3. Virtual Datacenter

  • Groups of virtual machines
  • Groups of virtual appliances
    • Cores
    • RAM
    • Local storage
    • VLANS
    • Public IPs
    • External storage

      Virtual Datacenter accounting is the total of the resources reserved and/or consumed by the Virtual Machines, Virtual Appliances, and users of a Virtual Datacenter.

4. Enterprise
An Enterprise has no managed resources that do not belong to a Virtual Datacenter, so there is no accounting per Enterprise. However, the Enterprise associated with each resource is recorded for aggregating resources at Enterprise level, and this information can be retrieved through the database views.

3. Architecture

Accounting tables, triggers for data capture, and procedures comprise the Abiquo accounting architecture.

3.1. Triggers

Triggers have been created specifically for accounting integration. These triggers exist in the kinton schema and tie to rules for the Abiquo APIs to automatically capture data in if/then/when scenarios.

  • Accounting of VMs:

    update_virtualmachine_update_stats AFTER UPDATE ON virtualmachine 

  • Accounting for Storage:

    create_rasd_management_update_stats AFTER INSERT ON rasd_management
    delete_rasd_management_update_stats AFTER DELETE ON rasd_management
    
  • Accounting of IPs:

    update_ip_pool_management_update_stats AFTER UPDATE ON ip_pool_management
    update_rasd_management_update_stats AFTER UPDATE ON rasd_management
    
  • Accounting of VLANs:

    create_vlan_network_update_stats AFTER INSERT ON vlan_network
    delete_vlan_network_update_stats AFTER DELETE ON vlan_network
    
  • Accounting of Racks:

    rack_update AFTER UPDATE ON rack
  • Accounting of Physical Machine:

    physicalmachine_create AFTER INSERT ON physicalmachine
    physicalmachine_delete AFTER DELETE ON physicalmachine
    physicalmachine_update AFTER UPDATE ON physicalmachine
  • Accounting of Virtual Machine Templates:

    virtualimage_create AFTER INSERT ON virtualimage
    virtualimage_delete AFTER DELETE ON virtualimage
    virtualimage_update AFTER UPDATE ON virtualimage
    virtualimageconversion_update AFTER UPDATE ON virtualimage_conversion

3.2. Event Accounting

The triggers collect the information necessary and call stored procedures responsible for recording accounting events. The stored procedures are:

AccountingVMRegisterEvents
AccountingStorageRegisterEvents
AccountingIPsRegisterEvents
AccountingVLANRegisterEvents

3.3. Accounting Tables

Since Abiquo 2.4, the accounting_event_"resource" tables are now the definitive source of accounting data and log of customer activity. You must back up these tables to prevent accidental loss of accounting data.

The default for removing stale data from these tables is now 3 years (instead of 90 days in Abiquo 2.2). When you upgraded to Abiquo 2.4, if you were using default values, they were updated to 3 years. However, non-default values were not updated.

The triggers are always active. The resultant data is stored in event-based database tables that store resource data, and the time resource usage begins and ends.

These tables all follow a similar design pattern, and are prefixed accounting_event_"resource", where "resource" can be substituted depending on the accounting resource type. For example, the data table on virtual machines (CPU, memory, storage) shown below.

3.3.1. Table accounting_event_vm

 

Field Name

Field Type

Description

idVMAccountingEvent

BIGINT

ID event unique

idVM

INTEGER

ID of virtual machine owning the resource

idEnterprise

INTEGER

ID of Enterprise resource owner

idVirtualDataCenter

INTEGER

ID of Virtual Datacenter facility owner

idVirtualApp

INTEGER

ID Virtual App resource owner

cpu

INTEGER

'cores' units

ram

INTEGER

Memory, in megabytes (MB)

hd

BIGINT

Local storage, in bytes (B)

startTime

TIMESTAMP

When a resource was created

stopTime

TIMESTAMP

When a resources was destroyed

consolidated

TINYINT

Flag for processed or not

costCode

TINYINT

Cost code assigned for pricing purposes

hypervisorType

VARCHAR

The type of hypervisor hosting the virtual machine

idDataCenterINTEGERID of Datacenter
VirtualMachine-haHostedINTEGERFlag for hosted on HA-enabled PM or not

3.3.2. Table accounting_event_pm

This table stores details of reserved physical servers - one row for each reserved server.

 

Field NameField TypeDescription
idPMAccountingEventBIGINTUnique column identifier for the accounting event
idPhysicalMachineINTID of the physical machine, as defined in the kinton.physicalmachine table.
NOTE: Use the kinton_accounting ABQ_PM_ID_TO_NAME(id) function to obtain the machine name associated with the ID
idEnterpriseINTID of the ENTERPRISE, as defined in the kinton.enterprise table.
NOTE: Use the kinton_accounting ABQ_ENT_ID_TO_NAME(id) function to obtain the machine name associated with the ID
cpuINTThe number of physical CPUs in the machine
ramINTThe physical machine’s RAM (in MB)
startTimeTIMESTAMPThe time at which the machine reservation started
stopTimeTIMESTAMPThe time at which the reservation finished.   This value is NULL if the reservation is still active
idDataCenterINTID of the physical datacenter, as defined in the kinton.datacenter table.
NOTE: Use the kinton_accounting ABQ_DC_ID_TO_NAME(id) function to obtain the datacenter name associated with the ID
consolidatedTINYINTIndicates whether this row has been consoliated with other rows.   Currently this value is always 0
version_cINTSoftware controlled data version

 

3.3.3. Table accounting_event_repository

This table contains details of the repository usage. There is one row for each VM Template, Instance, and conversions of the template. Note that Persistent VM Templates (except template conversions) are not included in this table, because they are stored as external volumes and charged separately.

 

Field NameField TypeDescription
idRepoAccountingEventBIGINTUnique column identifier for the accounting event
idImageINTID of the repository image, as defined in the kinton.virtualimage table.
NOTE: Use the kinton_accounting ABQ_IMG_ID_TO_NAME(id) function to obtain the image name associated with the ID. 
idEnterpriseINTID of the ENTERPRISE, as defined in the kinton.enterprise table.
NOTE: Use the kinton_accounting ABQ_ENT_ID_TO_NAME(id) function to obtain the machine name associated with the ID 
idImageTypeNameINTThe ID of the image type name in the generic accounting name table, use the kinton_accounting ABQ_OBJECT_ID_TO_NAME (id)  function to retrieve the actual type name.   Names are one of:
INSTANCE, TEMPLATE, INSTANCE-CONVERSION, TEMPLATE-CONVERSION
idImageFormatNameINTThe ID of the image format name in the generic accounting name table, use the kinton_accounting ABQ_OBJECT_ID_TO_NAME (id)  function to retrieve the actual format name.   Names are: VMDK_FLAT, RAW, etc. see the wiki API reference for further details
imageSizeBIGINTPhysical Size of the repository image, in bytes
idRepositoryINTThe ID of the image repository as defined in the kinton.repository table
idRepositoryNameINTThe ID of the repository name in the generic accounting name table, use the kinton_accounting ABQ_OBJECT_ID_TO_NAME (id)  function to retrieve the actual repository name.
startTimeTIMESTAMPThe time at which the image was created in the repository
stopTimeTIMESTAMPThe time at which the image was deleted from the repository.   This value is NULL if the image still exists.
idDataCenterINTID of the physical datacenter, as defined in the kinton.datacenter table.
NOTE: Use the kinton_accounting ABQ_DC_ID_TO_NAME(id) function to obtain the datacenter name associated with the ID. 
consolidatedTINYINTIndicates whether this row has been consoliated with other rows.   Currently this value is always 0.
version_cINTSoftware controlled data version.

 

3.4. Recording hourly resource usage information

The data stored in the accounting_event_"resource" tables must be processed every hour to record the previous hour of resource usage. This is done by running the UpdateAccounting stored procedure, which retrieves resource data from the accounting_event_"resource" database tables and stores it in the accounting_event_detail table.

The Enterprise Edition of Abiquo creates a cron job which automatically calls this procedure every hour, as detailed below.

   0 * * * * mysql -uroot -hlocalhost -P3306 -Proot -e "CALL kinton.UpdateAccounting ();"

3.5. Table accounting_event_detail

This is an accounting database table (located in the kinton_accounting database schema) which contains the information needed for accounting and billing functions. This table should not be accessed directly - if you need to access non-aggregated data then please use the kinton_accounting schema ACCOUNT_PERIOD_USAGE_VW (or the HOURLY_USAGE_MAX_VW or HOURLY_USAGE_MAX_2_VW) views to access the content of this table.

Warning: Running unoptimized or long running SQL queries against this table can have a significant impact on the overall performance of the transactional database, and might affect the proper functioning of AbiquoServer. Therefore Abiquo recommends the use of database replication to replicate this table to another database server which has been prepared to process OLAP type queries.

This table may accumulate significant amounts of information over time, and therefore its space requires suitable management.

The following rows are returned from ACCOUNT_PERIOD_USAGE_VW view:

Field Name

Field Type

Description

idVM

INTEGER

ID virtual machine owning the resource

idEnterprise

INTEGER

ID Enterprise resource owner

idVirtualDataCenter

INTEGER

ID Virtual Datacenter owner

idVirtualApp

INTEGER

ID Virtual App resource owner

enterpriseName

VARCHAR

Company Name resource owner

virtualDataCenter

VARCHAR

Virtual Datacenter name resource owner

virtualApp

VARCHAR

Virtual App Name resource owner

virtualmachine

VARCHAR

Virtual Machine Name resource owner

starttime

TIMESTAMP

Start of the time slice

endTime

TIMESTAMP

End of the time slice

idAccountingResourceType

TINYINT

Resource type posted: virtualmachine-vcpu, virtualmachine-vram, virtualmachine-vhd, ExternalStorage, IPAddress, VLAN, VirtualMachine-hypervisorType

resourceType

VARCHAR

Resource type (text)

resourceUnits

BIGINT

Resource units to account for

resourceName

VARCHAR

Resource Name to account for. A new resource type 'VirtualMachine-hypervisorType' is now recorded for each VM. The Hypervisor Type is recorded in this 'resourceName' column.

costCode

VARCHAR

Cost code of Virtual Image. Only valid when the idAccountingResourceType is 1 (virtualmachine-vcpu)

idStorageTier

INTEGER

Code associated with this storage resource's QoS/Tier level. Only valid for the 'External Storage' resource type

Every hour, the status of each resource implemented in the system is recorded, which can generate a large number of rows. Abiquo recommends that the table is periodically purged of resource information which is no longer required.

3.6. Stored Procedure to Purge Old Accounting Data

The data stored in the tables for accounting_event_"resource" is purged periodically from the database to prevent excessive table growth. This is done using the following stored procedure:

  DeleteOldRegisteredEvents 

The stored procedure deletes old usage records that are inactive, complete, and older than a certain number of hours. By default in Abiquo 2.4, this value is 26280 hours, which is 3 years of 365 days.

Abiquo automatically creates a cron job that runs every Sunday at 12:00 hours. The cron job calls the stored procedure. In the following example cron job, the input parameter is set to 2160 hours, which is 90 days. However, by default the input parameter will not be used by the stored procedure.* By default, the stored procedure uses the DeleteRegEventsDeleteHours parameter in the kinton_accounting.accounting_parameters table, which is set to 26280 hours.

   0 12 * * 0 mysql -uroot -hlocalhost -P3306 -Proot -e "CALL kinton.DeleteOldRegisteredEvents(2160)"

* By default the DeleteRegEventsUseSPParam in the Accounting Parameters table is set to 0, which means do not use the input parameter.

3.7. Aggregating Resource Usage data

For accounting and billing, Abiquo has previously implemented database views (in both the kinton and kinton_accounting schemas) to help you access the resource usage information correctly. However, the use of these views is no longer recommended for scalability and performance reasons. Instead, use the equivalent queries listed below the view definitions.

3.7.1. View definitions

ACCOUNTING_CONFIG_VW - Returns the current accounting configuration as a single row with the columns described in the table below.

ACCOUNT_PERIOD_USAGE_VW - Returns the resource use for the accounting period with the maximum values. If the same resource has been used more than once in the same time zone, it takes the greater value. Not grouped by virtual machine and virtual appliance. The columns returned ar the same as the HOURLY_USAGE_MAX_2_VW but the name has been changed to reflect the change to the accounting period.

Legacy maximum usage views

  • HOURLY_USAGE_MAX_VW - Returns the resource use for hours with their maximum values. If the same resource has been used more than once in the same time zone, it takes the greater value. Not grouped by virtual machine and virtual appliance.
  • HOURLY_USAGE_MAX_2_VW

Legacy sum views

  • HOURLY_USAGE_SUM_VW - Returns the resource use for hours with their values added together. The data is aggregated to virtual datacenter level, but can be further aggregated to enterprise level if required.
  • DAILY_USAGE_SUM_VW - Returns the resource use for days with their values added together. The data is aggregated to virtual datacenter level, but can be further aggregated to enterprise level if required.
  • MONTHLY_USAGE_SUM_VW - Returns the resource use for months with their values added together. The data is aggregated to virtual datacenter level, but can be further aggregated to enterprise level if required.

3.7.1.1. Accounting Config View

 

ColumnDescription
ACCOUNTING_ENABLEDA value of 0 means that accounting is disabled, a value of 1 means accounting is enabled.
PERIOD_SIZEThe current size of an accounting period. Possible values: HOUR,DAY,WEEK,MONTH
PERIOD_GRANULARITYThe current granularity of an accounting period. Possible values: HOUR,DAY,WEEK,MONTH
DELETE_HOURSThe number of hours old that data in the accounting_event_vm|ips|vlan|storage tables be before it is eligible for deletion.
MAX_PERIODS_PER_RUNThe maximum number of accounting periods that the UpdateAccounting() procedure will process in any one call.
MAX_INIT_PERIODSIndicates the number previous of accounting periods (relative to the time that UpdateSP is run) used to populate the table. Only used during initial population of an empty accounting_event_detail table.

 

3.7.2. Hourly Usage Template Queries

Use the template queries below instead of the SUM_VW views. The template queries provide scalable query performance.

3.7.2.1. Hourly Usage Sum Template Query

HOURLY_USAGE_SUM_VW equivalent
SELECT
  v.startTime AS startTime,
  v.endTime AS endTime,
  v.idAccountingResourceType AS idAccountingResourceType,
  v.resourceType AS resourceType,
  SUM(v.resourceUnits) AS resourceUnits,
  v.idEnterprise AS idEnterprise,
  v.idVirtualDataCenter AS idVirtualDataCenter,
  v.enterpriseName AS enterpriseName,
  v.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW v
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  v.startTime >= '2012-04-16' AND v.endTime < '2012-04-17'
GROUP BY
  v.startTime,
  v.idAccountingResourceType,
  v.idEnterprise, v.idVirtualDataCenter;

3.7.2.2. Daily Usage Sum Template Query

DAILY_USAGE_SUM_VW equivalent
SELECT
  CAST(a.startTime AS DATE) AS startTime,
  CAST(a.startTime AS DATE) AS endTime,
  a.idAccountingResourceType AS idAccountingResourceType,
  a.resourceType AS resourceType,
  SUM(a.resourceUnits) AS resourceUnits,
  a.idEnterprise AS idEnterprise,
  a.idVirtualDataCenter AS idVirtualDataCenter,
  a.enterpriseName AS enterpriseName,
  a.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW a
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  a.startTime >= '2012-04-16' AND a.endTime < '2012-04-17'
GROUP BY
  CAST(a.startTime AS DATE),
  a.idAccountingResourceType,
  a.idEnterprise, a.idVirtualDataCenter;

3.7.2.3. Monthly Usage Sum Template Query

MONTHLY_USAGE_SUM_VW equivalent
SELECT
  CAST((a.startTime - INTERVAL (DAYOFMONTH(a.startTime) - 1) day) AS DATE) AS startTime,
  LAST_DAY(a.startTime) AS endTime,
  a.idAccountingResourceType AS idAccountingResourceType,
  a.resourceType AS resourceType,
  SUM(a.resourceUnits) AS resourceUnits,
  a.idEnterprise AS idEnterprise,
  a.idVirtualDataCenter AS idVirtualDataCenter,
  a.enterpriseName AS enterpriseName,
  a.virtualDataCenter AS virtualDataCenter
FROM
  kinton_accounting.HOURLY_USAGE_MAX_VW a
WHERE
  # APPLY CUSTOM QUERY FILTERS HERE, E.G. BY DATE RANGE AS BELOW
  a.startTime >= '2012-04-01' AND a.endTime < '2012-05-01'
GROUP BY
  CAST((a.startTime - INTERVAL (dayofmonth(a.startTime) - 1) day) AS DATE),
  a.idAccountingResourceType,
  a.idEnterprise, a.idVirtualDataCenter;

3.8. Replicating the accounting_event_detail table

As mentioned above, it is important to avoid running queries on the accounting_event_detail table. Excessive queries on that table may negatively affect the Abiquo Server. So a periodic process to dump the contents of this table from the transactional database to the OLAP database has been created. The OLAP database reports can then run without harming the performance of the online system. The frequency of replication depends on the frequency of access to usage data. If the database is replicated by existing asynchronous tools, you should consider a replication every hour after completion of the process UpdateAccouting(). This way, customers can see their resource consumption on a per hour basis. If you do not wish to implement a replication process as described, you can implement a process to export the table to a file that is imported into the OLAP database once a day, for example.

4. Configure Accounting

The accounting functionality is integrated into the Abiquo platform. Do the steps below to configure access to this function.

4.1. Configure Access to Accounting Tables

For customers who would like to see the detailed tables, the data can be viewed as read-only by authorized users from external networks.
To grant access to username "accounting" with password "acc0unt1ng", make the following changes to the kinton:

grant select on kinton_accounting.DAILY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.HOURLY_USAGE_MAX_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.HOURLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.MONTHLY_USAGE_SUM_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.accounting_event_detail to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.ACCOUNT_PERIOD_USAGE_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.ACCOUNTING_CONFIG_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.ACCOUNT_PERIOD_USAGE_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_ERRORS_VW to accounting@'%' identified by 'acc0unt1ng';
grant select on kinton_accounting.LOG_WARNINGS_VW to accounting@'%' identified by 'acc0unt1ng';
grant execute on procedure kinton_accounting.ABQ_SET_PARAMETER to accounting@'%' identified by 'acc0unt1ng';