Create custom billing usage queries with SQL

When you create custom usage queries for Abiquo billing, you can use a number of special placeholders. The billing integration will replace the placeholders with appropriate values when it processes accounts. The placeholders are all case-sensitive and they are described in the following table.

Placeholder	Description

Placeholder	Description
{ABQ_PERIOD_START}	Refers to the timestamp representing the account period start time. Note that the start time is INCLUSIVE.
{ABQ_PERIOD_END}	Refers to the timestamp representing the account period end time. Note that the end time is EXCLUSIVE.
{ABQ_ENTERPRISE_ID}	Refers to an Enterprise ID.
{ABQ_VDC_ID}	Refers to a VDC ID.
{ABQ_ACCOUNTING_SOURCE}	Refers to the Accounting table. Do not directly query the Accounting tables or views, use this placeholder instead. This placeholder provides optimized DB access to the data defined by the 'accounting_event_detail' table and the 'HOURLY_USAGE_MAX_VW' view.
{DC_ID}	Refers to a Datacenter ID. Useful when grouping billing by datacenter using the dc_grouping property

An example SQL query might be:

SELECT COUNT(*) FROM kinton.hypervisor h, kinton.virtualmachine vm WHERE type LIKE 'VMX%' AND vm.idHypervisor=h.id and vm.idEnterprise={ABQ_ENTERPRISE_ID}

The data in the main Abiquo 'kinton' schema typically contains no date information. The accounting tables overcome this limitation by tracking specific resources on an hourly basis. This means that any non-account-based custom metrics will be based upon data as currently stored at the time which Abiquo Billing Integration runs, rather than the time of the account period.