This article applies to Abiquo V1.8.5
Many organisations have requirements to track the use of specific VM images from the Abiquo App Library. For example they will need to account for the use of Microsoft Licenses that are used. Abiquo provides the ability to track the use of VM images using the Cost Code. The Cost Code is a unique identifier that is assocatied with and image in the App Library and can be used to track its use.
The Wiki documentation at http://wiki.abiquo.com/display/ABI18/Managing+the+Appliance+Library provides detail on how to add a Cost Code to a library image.
Once images have Cost Codes then the additional data is tracked in the Abiquo Accounting tables within the database. These database tables can then be queried in order to understand the Enterprise or Virtual Datacenters which have consumed the VM image.
Here is some SQL that should hopefully point you in the right direction to create the report that you need.
Below are two sample queries that can be run against the Abiquo kinton database in order to return the number of Hours per costCode (one query is Enterprise-based and one VDC-based).The sample queries can be run by uncommenting the date ranges, and supplying appropriate dates:
#
# Enterprise-level counts of hourly cost code
#
SELECT idEnterprise, costCode, COUNT(*) AS HoursOfCostCodeUse, MIN(startTime) AS 'Earlist Use', MAX(endTime) AS 'Latest Use'
FROM
(SELECT * FROM kinton.accounting_event_detail acc
WHERE
idAccountingResourceType=1
# Uncomment line below and set appropriate date range
# AND startTime > '2011-11-02 00:00' AND endTime < '2011-12-03 00:00'
GROUP BY startTime, idVirtualMachine ) AS innerQuery
GROUP BY idEnterprise, costCode;
#
# VDC-level counts of hourly cost code
#
SELECT idEnterprise, idVirtualDataCenter, costCode, count(*) AS HoursOfCostCodeUse, MIN(startTime) AS 'Earlist Use', MAX(endTime) AS 'Latest Use'
FROM
(SELECT * FROM kinton.accounting_event_detail acc
WHERE
idAccountingResourceType=1
# Uncomment line below and set appropriate date range
# AND startTime > '2011-11-02 00:00' AND endTime < '2011-12-03 00:00'
GROUP BY startTime, idVirtualMachine ) AS innerQuery
GROUP BY idVirtualdataCenter, costCode;
0 Comments