查询vCenter vcdb数据库获取虚拟机使用的datastore

目录

1.需求描述

2.查询vcdb on MSSQLServer

3.查询vcdb on Postgresql

4.几个有用的视图

5.注意

6.参考

---

1.需求描述

需要查询统计vCenter中所有虚拟机使用的VMFS卷(数据存储datastore)的容量及可用容量。

已知vCenter中所有基本数据都存储在支撑数据库中，我们可以通过SQL语句从数据库中直接查询出所需要的数据。

2.查询vcdb on MSSQLServer

vCenter Server Version:  vCenter 6.0 u2 on Windows  Server 2008R2

vCenter vcdb:  MSSQLServer 2008R2 ，安装vcenter之前手动创建的

SELECT vv.NAME AS VM_Name, 
 vv.IP_ADDRESS AS VM_IP, 
 vh.NAME AS HOST_IP,
 vcr.NAME AS ResourePool_Name, 
 vd.NAME AS DataStore_Name,
 ((CONVERT(BIGINT,vd.CAPACITY))/(1024*1024*1024)) AS CapacityGB,
 ((CONVERT(BIGINT,vd.FREE_SPACE))/(1024*1024*1024)) AS FreeGB
FROM
 VCDB.dbo.VPXV_VM_DATASTORE AS vvd, 
 VCDB.dbo.PXV_DATASTORE AS vd,
 VCDB.dbo.VPXV_VMS AS vv,
 VCDB.dbo.VPXV_HOSTS AS vh,
 VCDB.dbo.VPXV_COMPUTE_RESOURCE AS vcr
 where vd.ID = vvd.DS_ID
 and vv.VMID = vvd.VM_ID
 and vv.HOSTID = vh.HOSTID
 and vh.FARMID = vcr.RESOURCEPOOLID
 and vd.NAME not like 'datastore%'
ORDER BY vcr.NAME, vd.NAME;

3.查询vcdb on Postgresql

vCenter Server Version:  vCenter 6.0 u2 on Windows  Server 2008R2

vCenter vcdb:  vCenter安装过程中自带的Postgresql，PostgreSQL 9.3.9 (VMware Postgres 9.3.9.0-2921310 release), compiled by Visual C++ build 1500, 64-bit

vcdb postgresql数据库的密码是在安装过程中自动创建的，其密码文件保存在C:\ProgramData\VMware\vCenterServer\cfg\vmware-vpx\vcdb.properties：

driver = org.postgresql.Driver
dbtype = PostgreSQL
url = jdbc:postgresql://localhost:5432/VCDB
username = vc
password = 3q^LXZclUY2(a?F
password.encrypted = false

SQL语句：

SELECT vv."name" AS VM_Name,
  vv.IP_ADDRESS AS VM_IP,
  vh.NAME AS HOST_IP,
  vcr.NAME AS ResourePool_Name,
  vd.NAME AS DataStore_Name,
  cast(vd.capacity AS BIGINT) /(1024*1024*1024) AS CapacityGB,
  cast(vd.free_space AS BIGINT) /(1024*1024*1024) AS FreeGB
FROM vpxv_vms AS vv, 
  vpxv_datastore AS vd, 
  vpxv_vm_datastore AS vvd, 
  vpxv_hosts AS vh, 
  vpxv_compute_resource AS vcr
WHERE vv.vmid = vvd.vm_id
  AND vd.id = vvd.ds_id
  AND vv.hostid = vh.hostid
  AND vh.farmid = vcr.resourcepoolid
  AND vd."name" NOT LIKE 'datastore%'
ORDER BY  vcr."name", vd."name";

通过navicat连接到Postgresql，查询结果示例:

查询结果示例

4.几个重要的视图

VPXV_VMS  虚拟机

VPXV_HOSTS  主机

VPXV_DATASTORE  数据存储

VPXV_VM_DATASTORE  虚拟机--数据存储映射

VPXV_COMPUTE_RESOURCE  计算资源

5.注意

严格控制好vcdb数据库的访问权限，最好给需要查询的用户以只读权限即可。

6.参考

1. Gather information from VMware vCenter VCDB about SQL Server
2. Querying the VMware vCenter Database (VCDB) for Performance and Configuration Information
3. 在安装了 Windows 的 vCenter Server 6.0 中连接到嵌入式 vPostgres 数据库 (2108848)