Abiquo accounting architecture

1 Introduction to accounting architecture
2 Triggers and stored procedures
3 Accounting tables
4 accounting_event_detail table
- 4.1 Resource IDs
- 4.2 Resource owners
  - 4.2.1 Description of resource owners

Introduction to accounting architecture

The Abiquo accounting architecture consists of accounting tables, triggers for data capture, and procedures.

This detailed diagram shows the basic architecture of the Accounting system.

Triggers and stored procedures

The Abiquo kinton schema contains triggers for accounting integration. The triggers run stored procedures in the kinton_accounting schema to automatically capture data in if/then/when scenarios.

Resource	Triggers

Resource	Triggers
VMs	update_virtualmachine_update_stats AFTER UPDATE ON virtualmachine
Storage	create_rasd_management_update_stats AFTER INSERT ON rasd_management delete_rasd_management_update_stats AFTER DELETE ON rasd_management
IPs	update_ip_pool_management_update_stats AFTER UPDATE ON ip_pool_management update_rasd_management_update_stats AFTER UPDATE ON rasd_management
VLANs	create_vlan_network_update_stats AFTER INSERT ON vlan_network delete_vlan_network_update_stats AFTER DELETE ON vlan_network
Racks	rack_update AFTER UPDATE ON rack
Physical Machines	physicalmachine_create AFTER INSERT ON physicalmachine physicalmachine_delete AFTER DELETE ON physicalmachine physicalmachine_update AFTER UPDATE ON physicalmachine
VM 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

Stored procedures for 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

Accounting tables

This section gives a general description of the accounting tables.

The accounting_event_"resource" tables are 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 3 years.

Accounting 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.

Table accounting_event_vm

Field name	Field type	Description

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
idDataCenter	INTEGER	ID of datacenter
VirtualMachine-haHosted	INTEGER	Deprecated
antiAffinity	INTEGER	For VMs in an anti-affinity layer
idHardwareProfile	INTEGER	ID of hardware profile

Table accounting_event_pm

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

Field name	Field type	Description
idPMAccountingEvent	BIGINT	Unique column identifier for the accounting event
idPhysicalMachine	INT	ID 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
idEnterprise	INT	ID 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
cpu	INT	The number of physical CPUs in the machine
ram	INT	The physical machine’s RAM (in MB)
startTime	TIMESTAMP	The time at which the machine reservation started
stopTime	TIMESTAMP	The time at which the reservation finished. This value is NULL if the reservation is still active
idDataCenter	INT	ID 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
consolidated	TINYINT	Indicates whether this row has been consoliated with other rows. Currently this value is always 0
version_c	INT	Software controlled data version

Table accounting_event_repository

This table contains details of the repository usage. There is one row for each VM Template and Instance stored in the repository. Persistent VM templates are not included in this table, because they are stored as external volumes and charged separately.

Field name	Field type	Description
idRepoAccountingEvent	BIGINT	Unique column identifier for the accounting event
idImage	INT	ID 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
idEnterprise	INT	ID 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
idImageTypeName	INT	The 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
idImageFormatName	INT	The 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
imageSize	BIGINT	Physical size of the repository image, in bytes
idRepository	INT	The ID of the image repository as defined in the kinton.repository table
idRepositoryName	INT	Deprecated - The 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. THIS FIELD WAS DEPRECATED IN ABIQUO 3.4
startTime	TIMESTAMP	The time at which the image was created in the repository
stopTime	TIMESTAMP	The time at which the image was deleted from the repository. This value is NULL if the image still exists.
idDataCenter	INT	ID 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.
consolidated	TINYINT	Indicates whether this row has been consoliated with other rows. Currently this value is always 0.
version_c	INT	Software controlled data version.

List of accounting event tables

The accounting event tables include the following.

backup
costcode
detail
ds_storage
firewall
ips
loadbalancer
pm
protect
repository
storage
vlan
vm
vm_off
vm_on

accounting_event_detail table

The accounting_event_detail table 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 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

Field name	Field type	Description
starttime	TIMESTAMP	Start of the time slice
endTime	TIMESTAMP	End of the time slice
idAccountingResourceType	TINYINT	Resource type posted, for example: virtualmachine-vcpu, virtualmachine-vram, virtualmachine-vhd, ExternalStorage, IPAddress, VLAN, VirtualMachine-hypervisorType
resourceType	VARCHAR	Resource type (text)
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.
resourceUnits	BIGINT	Resource units to account for
idEnterprise	INTEGER	ID enterprise resource owner
idVirtualDataCenter	INTEGER	ID virtual datacenter owner
idVirtualApp	INTEGER	ID virtual app resource owner
idVirtualMachine	INTEGER	ID virtual machine owning the resource
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
costCode	VARCHAR	Cost code of virtual image, hardware profile, or VM
idStorageTier	INTEGER	Code associated with this storage resource's QoS/Tier level. For storage resource types
costCodeName	varchar(255)	Name of cost code
storageTierName	varchar(40)	Name of storage tier
idDataCenter	int(11)	ID of datacenter
dataCenterName	varchar(255)	Name of datacenter
idHardwareProfile	int(11)	ID of hardware profile
idAccountingEvent	bigint(20)	ID of accounting event
hypervisorType	varchar(255)	Hypervisor type (includes public cloud provider type)
region	varchar(255)	Name of datacenter or public cloud region
internal_provider_id	varchar(255)	ProviderID of the resource
account_id	varchar(255)	Optional, can be set with the enterprise property “internal_customer_id”

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.

Resource IDs

The resources in the accounting_event_detail table are represented with a numerical value, as defined in the table below in the idAccountingResourceType column.

Parameter name	idAccountingResourceType
VirtualMachine	1
VirtualMachine-vcpu	1
VirtualMachine-vram	2
VirtualMachine-vhd (datastores)	3
ExternalStorage	4
IPAddress/IPAddress (public IP)	5
VLAN	6
VirtualMachine-hypervisorType	7
VirtualMachine-haHosted	8
ReservedPhysicalMachine-cpu	9
ReservedPhysicalMachine-ram	10
RepositoryStorage	11
VirtualMachine-antiAffinity	12
VirtualMachine-tierhd (datastore tiers)	13
backup_usage/backup_policy	14
vm_usage_on/vm_on - cpu_usage_on/cpu_on	15
vm_usage_off/vm_off - cpu_usage_off/cpu_off	16
memory_usage_on/memory_on	17
memory_usage_off/memory_off	18
firewall_usage/firewall	19
loadbalancer_usage/loadbalancer	20
cost_code_usage	24
draas_protection_usage/draas_protection	25

Resource owners

Abiquo records resource ownership at the following levels:

virtual machine
virtual datacenter
enterprise

Description of 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.