satis egitimisatis egitimitengda.pro

Open@Blog

Discussion on the state of cloud computing and open source software that helps build, manage, and deliver everything-as-a-service.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that has been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.
  • Login
Posted by on in CloudStack Tips
  • Font size: Larger Smaller
  • Hits: 20152
  • Print
  • Report this post

Analyzing CloudStack usage records with Excel

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:
Rate this blog entry:
Trackback URL for this blog entry.
David Nalley is currently employed by Citrix as the Community Manager for the CloudStack project. In addition he's a long time contributor to the Fedora Project, where among other things he is currently serving on the Fedora Project Board. He's also contributed to in various forms to Cobbler, Zenoss, Opengroupware.org, OLPC Math4, and Sahana. He is a frequent speaker at Free Software conferences around the nation, and writes for a number of technical and open source media publications including Linux Pro Magazine and OpenSource.com
  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Wednesday, 23 July 2014

Open@Citrix

Citrix supports the open source community via developer support and evangeslism. We have a number of developers and evangelists that participate actively in the open source community in Apache Cloudstack, OpenDaylight, Xen Project and XenServer. We also conduct educational activities via the Build A Cloud events held all over the world. 

Connect