Cloud Zone is brought to you in partnership with:

Build a Cloud.org is a resource for those users who want to build cloud computing software with both open source and proprietary software. Mark is a DZone MVB and is not an employee of DZone and has posted 72 posts at DZone. You can read more from them at their website. View Full User Profile

Analyzing CloudStack Usage Records with Excel

03.29.2013
| 2007 views |
  • submit to reddit

Curator's Note: The content of this article was originally written by David Nailey on the Build a Cloud blog.

Alex Bederov from Nokia wrote up a post over a year ago about analyzing his cloud usage stats with Excel. I was going to refer to that, and when I went looking for it, I realized that it was posted as a KB article on the now defunct docs.cloudstack.org site. Since I still had the write up, and it's still relevant, I thought I'd repost that content here, so it is accessible again.

CloudStack comes with very well thought out and reasonably well implemented record keeping system. All information is there, however “batteries are not included”, you have to pull information and massage it yourself to create meaningful representation like billing records or usage reports. Fortunately, CloudStack Usage Server makes this task very easy to accomplish and Microsoft Excel pivot tables makes reporting fun and easy.
In my setup Usage Server runs every 24 hours. Usage database called “cloud_usage” is on the same MySQL server machine as main database called “cloud”. Resource usage is stored in table called cloud_usage (same name as database). I use following query to pull de-normalized usage records into comma delimited file ready to be processed in Excel:
(SELECT dc.name as "Datacenter", ac.account_name as "Account", u.domain_id
as "Domain ID", u.raw_usage,
s.cpu as "CPU count", s.speed as "CPU, MHz", s.ram_size as "RAM, MB",
8589934592/1024/1024/1024 as "Storage, GB", u.start_date,
"-", u.offering_id, u.usage_id, u.usage_type, u.description
FROM cloud_usage.cloud_usage u, cloud.account ac,
cloud.data_center dc, cloud.service_offering s
WHERE u.account_id=ac.id
AND u.zone_id=dc.id
AND u.offering_id=s.id
AND u.usage_type=1)
UNION
(SELECT dc.name as "Datacenter", ac.account_name as "Account", u.domain_id as
"Domain ID", u.raw_usage,
NULL as "CPU count", NULL as "CPU, MHz", NULL as "RAM MB",
u.size/1024/1024/1024 as "Storage, GB", u.start_date,
"-", u.offering_id, u.usage_id, u.usage_type, u.description
FROM cloud_usage.cloud_usage u, cloud.account ac,
cloud.data_center dc, cloud.disk_offering dsk
WHERE u.account_id=ac.id
AND u.zone_id=dc.id
AND u.offering_id=dsk.id
AND u.usage_type=6)
This query combines VMs and data volumes usage into the same table and de-referencing id fields. For now I am not including network usage data since I am using direct attached VLAN based networking model. Please note that usage_type “1” corresponds to VM, “6” – to data volume.
In order to import data into Excel we need to install MySQL ODBC connector which will allow us to connect to MySQL server directly. The MySQL ODBC connector is available free of charge from Oracle.
When you install the connector open a new Excel workbook, click Data, then select “From Other Sources” then select “From Microsoft Query”. Select “New Data Source”
Name the source and select MySQL ODBC driver:

I am using SSH tunnel and read-only account with read privileges to cloud and cloud_usage database. When you enter credentials click Test button and select cloud_usage database.

The new data source is available now.

Select the newly created data source and click OK button. Welcome to Windows 3.1 interface! Do not use Microsoft Query Wizard to create query. Cancel all the prompts and click SQL button go to SQL statement window.

Paste your SQL into ridiculously small window and save your query into a file with meaningful name. Excel will prompt you where to put the resulting data, accept the defaults.
The result of this exercise will be our query results imported into Excel spreadsheet. Here is how it looks like for me:

Please note that all my templates have 8GB HDD so I hardcode root disk size into SQL query. Now let’s analyze some data. Click top right corner to select the whole sheet and click Insert in Excel main menu, then Pivot Table, and then Pivot Chart. Make following selection in Pivot Table Field List:







Published at DZone with permission of Mark Hinkle, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)