使用 Excel 分析 CloudStack 使用记录

注：本文最初由 David Nailey 在 Build a Cloud 博客上撰写。

诺基亚的亚历克斯·贝德罗夫（Alex Bederov）在一年前写了一篇关于如何使用 Excel 分析他的云使用情况的文章。但当我想再次找到它的时候，我意识到这篇文章发布的网站——docs.cloudstack.org——已经不存在了。鉴于这篇文章的内容仍然有用，正好我也有它的备份，因此我在这里再次发布它，供其他人参考。

CloudStack 具有非常好的理念以及一个完善的日志系统。然而，CloudStack在信息处理方面并没有提供完善的工具——就像没有提供电池的玩具一样。你必须自己动手处理信息。然后才能获得类似于帐单记录或者用量报告等有意义的内容。幸运的是，CloudStack Usage Server 使得这项任务非常容易完成，而 Microsoft Excel 的数据透视表则使生成报告变得更有趣和简单。

在我的设置中，Usage Server 每24小时运行一次。名为“cloud_usage”的使用数据库与主数据库（名为“cloud”）位于同一个 MySQL 服务器上。资源使用情况存储在名为 “cloud_usage”（与数据库同名）的表中。我使用下面的查询语句将非规范化的使用记录转变成用逗号分隔的文件以便在 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)

该查询将虚拟机和数据卷用使量组合到同一个表中，并去掉了 id 字段。我这里的网络模型是直接通过虚拟局域网（VLAN）连接，因此我没有包括网络使用数据。请注意，这里的 usage_type=“1” 对应虚拟机，而“6”则对应数据卷。

为了将数据导入到 Excel 中，我们需要安装 MySQL ODBC 连接器，这样我们就可以直接连接到 MySQL 服务器了。 MySQL ODBC 连接器可从 Oracle 免费获得。

安装好连接器后，打开一个新的 Excel 工作簿，单击“数据”，然后选择“从其他来源”，然后选择“从 Microsoft Query”，选择“新数据源”。

命名数据源源并选择 MySQL ODBC 驱动程序：

这里使用 SSH 以及一个只读帐户登录服务器，该账户具有对 cloud 和cloud_usage 数据库的读取权限。 输入用户名和密码之后，选择 cloud_usage 数据库，然后选择 “test” 测试连接。

新数据源创建完毕。

选择刚刚创建的数据源，然后单击确定按钮。 欢迎来到Windows 3.1界面！ 不要使用 Microsoft Query 向导来创建查询。 关闭所有弹出窗口，点击SQL按钮进入 SQL 语句窗口。

将你的 SQL 语句粘贴到上方的小窗口中，并将你的查询保存——记得起一个有意义的文件名。 Excel 会询问你放置结果数据的位置，选择默认值即可。

这个练习的结果是将我们的查询结果导入到 Excel 默认表格中。 下面是我机器上的截图：

请注意，我所有的镜像都包含 8GB 硬盘，因此这里我把根磁盘大小硬编码进了 SQL 查询。 现在让我们做一些数据分析。 单击右上角选择整个工作表，然后在Excel主菜单中单击插入-数据透视表，然后选择数据透视图。 在“数据透视表字段列表”中进行以下选择：