VMware-虚拟化平台的Sql脚本Select查询Storage常用脚本
USE db_vCenter
go
SELECT
a1.NAME as '数据中心',
/***************************************iSCSI-LUN***************************************/
/*iSCSI-LUN0-存储容量（GB）*/
CONVERT(decimal(18, 2),((SELECT SUM([CAPACITY]/(1024*1024*1024.00))
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name like 'iSCSi%'
))) AS 'iSCSI-LUN-存储容量（GB）',
/*iSCSI-LUN0-可用存储（GB）*/
CONVERT(decimal(18, 2),((SELECT SUM([FREE_SPACE]/(1024*1024*1024.00))
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name like 'iSCSI%'
))) AS 'iSCSI-LUN-可用存储（GB）',
/***************************************iSCSI-LUN0***************************************/
/*iSCSI-LUN0-存储容量（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[CAPACITY]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN0'
)/(1024.00*1024*1024)) AS 'iSCSI-LUN0-存储容量（GB）',
/*iSCSI-LUN0-可用存储（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[FREE_SPACE]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN0'
)/(1024*1024*1024.00)) AS 'iSCSI-LUN0-可用存储（GB）',
/*iSCSI-LUN0-百分比*/
CONVERT(decimal(18, 1),(SELECT CONVERT(float, [FREE_SPACE]) / CONVERT(float, [CAPACITY]) * 100
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN0'
)) AS 'iSCSI-LUN0-百分比',
/***************************************iSCSI-LUN1***************************************/
/*iSCSI-LUN1-存储容量（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[CAPACITY]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN1'
)/(1024.00*1024*1024)) AS 'iSCSI-LUN1-存储容量（GB）',
/*iSCSI-LUN1-可用存储（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[FREE_SPACE]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN1'
)/(1024*1024*1024.00)) AS 'iSCSI-LUN1-可用存储（GB）',
/*iSCSI-LUN1-百分比*/
CONVERT(decimal(18, 1),(SELECT CONVERT(float, [FREE_SPACE]) / CONVERT(float, [CAPACITY]) * 100
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN1'
)) AS 'iSCSI-LUN1-百分比',
/***************************************iSCSI-LUN2***************************************/
/*iSCSI-LUN2-存储容量（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[CAPACITY]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN2'
)/(1024.00*1024*1024)) AS 'iSCSI-LUN2-存储容量（GB）',
/*iSCSI-LUN0-可用存储（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[FREE_SPACE]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN2'
)/(1024*1024*1024.00)) AS 'iSCSI-LUN2-可用存储（GB）',
/*iSCSI-LUN2-百分比*/
CONVERT(decimal(18, 1),(SELECT CONVERT(float, [FREE_SPACE]) / CONVERT(float, [CAPACITY]) * 100
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN2'
)) AS 'iSCSI-LUN2-百分比',
/***************************************iSCSI-LUN3***************************************/
/*iSCSI-LUN3-存储容量（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[CAPACITY]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN3'
)/(1024.00*1024*1024)) AS 'iSCSI-LUN3-存储容量（GB）',
/*iSCSI-LUN0-可用存储（GB）*/
CONVERT(decimal(18, 2),(SELECT [VPXV_DATASTORE].[FREE_SPACE]
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN3'
)/(1024*1024*1024.00)) AS 'iSCSI-LUN3-可用存储（GB）',
/*iSCSI-LUN3-百分比*/
CONVERT(decimal(18, 1),(SELECT CONVERT(float, [FREE_SPACE]) / CONVERT(float, [CAPACITY]) * 100
FROM [db_vCenter].[dbo].[VPXV_DATASTORE]
Where [VPXV_DATASTORE].name = 'iSCSI-LUN3'
)) AS 'iSCSI-LUN3-百分比',
a2.NAME as '群集名称',
/*群集内存（GB）---------------Edit-------------*/
CONVERT(decimal(18, 2),((SELECT SUM([MEM_SIZE]/(1024*1024*1024.00))
FROM [db_vCenter].[dbo].[VPXV_HOSTS]
Where [VPXV_HOSTS].NAME like '07[1-2]%'
))) AS '群集内存（GB）',
/*群集可用内存（GB）---------------Edit-------------*/
CONVERT(decimal(18, 2),((SELECT SUM([MEM_SIZE]/(1024*1024*1024.00))
FROM [db_vCenter].[dbo].[VPXV_HOSTS]
Where [VPXV_HOSTS].NAME like '07[1-2]%'
))) AS '群集可用内存（GB）',
[VPXV_HOSTS].NAME as '虚拟化主机名称',
/*虚拟化主机内存*/
CONVERT(decimal(18, 2),([MEM_SIZE]/(1024*1024*1024.00))) AS '虚拟化主机内存（GB）',
/*虚拟化主机可用内存*/
CONVERT(decimal(18, 2),([MEM_SIZE]/(1024*1024*1024.00))) AS '虚拟化主机可用内存（GB）',
[VPXV_VMS].NAME as '虚拟机名称'
FROM [db_vCenter].[dbo].[VPXV_VMS]
LEFT OUTER JOIN [dbo].[VPXV_HOSTS] ON [VPXV_VMS].HOSTID = [dbo].[VPXV_HOSTS].[HOSTID] /*虚拟化主机*/
LEFT OUTER JOIN [dbo].[VPX_ENTITY] as a1 ON a1.ID = [dbo].[VPXV_HOSTS].DATACENTER_ID /*数据中心*/
LEFT OUTER JOIN [dbo].[VPX_ENTITY] as a2 ON a2.ID = [dbo].[VPXV_HOSTS].FARMID /*群集名称*/