Discussion on the state of cloud computing and open source software that helps build, manage, and deliver everything-as-a-service.
Analyzing CloudStack usage records with Excel
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: