Using Cost Codes to track VM Licenses

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;

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk