These are some queries I use against Vcenter Views (5.1) to get a summary on subscription/Avg. utilization etc. If you have any questions on these, I can help you.
Clusters Only:
===========
SELECT ID, Name
FROM vpxv_entity
WHERE Type_ID=3
ORDER BY Name
Clusters & Blades:
==============
select en.NAME as ClusterName, hs.name as HostName, hs.HOST_MODEL, hs.CPU_MODEL,
hs.CPU_COUNT, hs.CPU_CORE_COUNT, hs.CPU_HZ, hs.MEM_SIZE
from vpxv_entity en, VPXV_HOSTS hs
where hs.FARMID in (SELECT en.ID
FROM vpxv_entity
WHERE TYPE_ID=3)
ORDER BY en.Name
--Cluster Total CPU with Memory:
==========================
select en.NAME as ClusterName,count(hs.HOSTID)as #_Blades, SUM(hs.CPU_CORE_COUNT) as Total_CPU, ROUND(sum(CAST(MEM_SIZE AS float))/1024/1024/1024,0) as Total_Memory from VPXV_HOSTS as hs full join VPXV_ENTITY as en
on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.NAME
--VCPU/Mem Allocation:
=====================
select en.name as Cluster_Name, count(vm.VMID)as #_VM, SUM(vm.NUM_VCPU) as Total_VCPU, SUM(vm.MEM_SIZE_MB)/1024 as Total_Allocated_Memory from
dbo.VPXV_ENTITY as en
full join
VPXV_VMS as vm
full join
VPXV_HOSTS as hs
on vm.HOSTID = hs.HOSTID on en.id = hs.farmid
where
vm.POWER_STATE = 'on'
and
en.TYPE_ID = 3
group by en.NAME
VM Report:
=========
Select
T1.ClusterName, T1.#_Blades, T2.#_VM,
T1.Total_CPU, T2.Total_VCPU, ROUND(CAST(T2.Total_VCPU as FLOAT)/CAST(T1.Total_CPU as FLOAT),2) as VCPU_Ratio,
T1.Total_Memory, T2.Total_Allocated_Memory, ROUND(T2.Total_Allocated_Memory/T1.Total_Memory*100,2)as Memory_Allocation_Percentage
From
--Cluster Total CPU with Memory:
---------------------------------
(select en.NAME as ClusterName,count(hs.HOSTID)as #_Blades, SUM(hs.CPU_CORE_COUNT) as Total_CPU, ROUND(sum(CAST(MEM_SIZE AS float))/1024/1024/1024,0) as Total_Memory
from
VPXV_HOSTS as hs full join VPXV_ENTITY as en
on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.NAME)T1,
--VCPU/Mem Allocation:
-----------------------
(select en.name as Cluster_Name, count(vm.VMID)as #_VM, SUM(vm.NUM_VCPU) as Total_VCPU, SUM(vm.MEM_SIZE_MB)/1024 as Total_Allocated_Memory from
dbo.VPXV_ENTITY as en
full join
VPXV_VMS as vm
full join
VPXV_HOSTS as hs
on vm.HOSTID = hs.HOSTID on en.id = hs.farmid
where
vm.POWER_STATE = 'on'
and
en.TYPE_ID = 3
group by en.NAME)T2
where T1.ClusterName = T2.Cluster_Name
Utilization:
========
CPU:
-----
select en.name as Cluster_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as CPU_Used_Percentage from
dbo.VPXV_ENTITY as en
full join
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
on en.id = hs.farmid
where
en.TYPE_ID = 3 and monstat.stat_group ='cpu' and monstat.stat_name = 'usage'
group by en.NAME
order by Cluster_Name
Mem:
------
select en.name as Cluster_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as Mem_Used_Percentage from
dbo.VPXV_ENTITY as en
full join
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
on en.id = hs.farmid
where
en.TYPE_ID = 3 and monstat.stat_group ='mem' and monstat.stat_name = 'usage'
group by en.NAME
order by Cluster_Name
Summary of my entire VM Environment :
================================
Select
T1.ClusterName, T1.#_Blades, T2.#_VM,
T1.Total_CPU, T2.Total_VCPU, ROUND(CAST(T2.Total_VCPU as FLOAT)/CAST(T1.Total_CPU as FLOAT),2) as VCPU_Ratio,
T1.Total_Memory, T2.Total_Allocated_Memory, ROUND(T2.Total_Allocated_Memory/T1.Total_Memory*100,2)as Memory_Allocation_Percentage,
T3.CPU_Used_Percentage, T4.Mem_Used_Percentage
From
--Cluster Total CPU with Memory:
---------------------------------
(select en.NAME as ClusterName,count(hs.HOSTID)as #_Blades, SUM(hs.CPU_CORE_COUNT) as Total_CPU, ROUND(sum(CAST(MEM_SIZE AS float))/1024/1024/1024,0) as Total_Memory
from
VPXV_HOSTS as hs full join VPXV_ENTITY as en
on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.NAME)T1,
--VCPU/Mem Allocation:
-----------------------
(select en.name as Cluster_Name, count(vm.VMID)as #_VM, SUM(vm.NUM_VCPU) as Total_VCPU, SUM(vm.MEM_SIZE_MB)/1024 as Total_Allocated_Memory from
dbo.VPXV_ENTITY as en
full join
VPXV_VMS as vm
full join
VPXV_HOSTS as hs
on vm.HOSTID = hs.HOSTID on en.id = hs.farmid
where
vm.POWER_STATE = 'on'
and
en.TYPE_ID = 3
group by en.NAME)T2,
--CPU Utilization:
--------------------
(select en.name as Cluster_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as CPU_Used_Percentage from
dbo.VPXV_ENTITY as en
full join
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
on en.id = hs.farmid
where
en.TYPE_ID = 3 and monstat.stat_group ='cpu' and monstat.stat_name = 'usage'
group by en.NAME
--order by Cluster_Name
)T3,
--Memory Utilization:
---------------------
(select en.name as Cluster_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as Mem_Used_Percentage from
dbo.VPXV_ENTITY as en
full join
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
on en.id = hs.farmid
where
en.TYPE_ID = 3 and monstat.stat_group ='mem' and monstat.stat_name = 'usage'
group by en.NAME
--order by Cluster_Name
)T4
where T1.ClusterName = T2.Cluster_Name
and T3.Cluster_Name = T4.Cluster_Name
and T2.Cluster_Name = T3.Cluster_Name
Blade CPU & Memory Utilization:
==========================
SELECT T1.Blade_Name,T1.CPU_Used_Percentage, T2.Mem_Used_Percentage from
--CPU Utilization:
--------------------
(select hs.NAME as Blade_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as CPU_Used_Percentage from
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
where
monstat.stat_group ='cpu' and monstat.stat_name = 'usage'
group by hs.name
)T1,
--Memory Utilization:
---------------------
(select hs.NAME as Blade_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as Mem_Used_Percentage from
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
where
monstat.stat_group ='mem' and monstat.stat_name = 'usage'
group by hs.name
)T2
where t1.Blade_Name = t2.Blade_Name
Blade CPU & Memory Utilization with Cluster Name:
=========================================
SELECT T3.ClusterName,T1.Blade_Name,T1.CPU_Used_Percentage, T2.Mem_Used_Percentage from
--CPU Utilization:
--------------------
(select hs.NAME as Blade_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as CPU_Used_Percentage from
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
where
monstat.stat_group ='cpu' and monstat.stat_name = 'usage'
group by hs.name
)T1,
--Memory Utilization:
---------------------
(select hs.NAME as Blade_Name, ROUND(AVG(CAST(STAT_VALUE as FLOAT)/100),2) as Mem_Used_Percentage from
VPXV_HIST_STAT_MONTHLY as monstat
full join
VPXV_HOSTS as hs
on monstat.ENTITY = 'host-'+ cast(hs.HOSTID as varchar)
where
monstat.stat_group ='mem' and monstat.stat_name = 'usage'
group by hs.name
)T2,
--Cluster Name:
----------------
(select en.NAME as ClusterName, hs.name as HostName, hs.HOST_MODEL, hs.CPU_MODEL,
hs.CPU_COUNT, hs.CPU_CORE_COUNT, hs.CPU_HZ, hs.MEM_SIZE
from vpxv_entity en, VPXV_HOSTS hs
where hs.FARMID in (SELECT en.ID
FROM vpxv_entity
WHERE TYPE_ID=3))
T3
where t1.Blade_Name = t2.Blade_Name and
t2.Blade_Name = t3.HostName
order by T3.ClusterName
Storage Allocated/Free Space @ Cluster Level:
=========================================
select T1.ClusterName, #_Blades,
T2.Total_Allocated_TB/T1.#_Blades as Total_Allocated_TB,
T2.Free_Space_TB /T1.#_Blades as Total_Free_TB
from
(select en.NAME as ClusterName,count(hs.HOSTID)as #_Blades, SUM(hs.CPU_CORE_COUNT) as Total_CPU, ROUND(sum(CAST(MEM_SIZE AS float))/1024/1024/1024,0) as Total_Memory
from
VPXV_HOSTS as hs full join VPXV_ENTITY as en
on hs.FARMID = en.id
where en.TYPE_ID = 3
group by en.NAME)T1,
(select en.NAME as ClusterName,
ROUND(SUM(CAST(ds.capacity as float))/1024/1024/1024/1024,2) as Total_Allocated_TB,
ROUND(SUM(CAST(ds.FREE_SPACE as float))/1024/1024/1024/1024,2)as Free_Space_TB
from dbo.VPXV_DATASTORE DS, dbo.VPXV_HOST_DATASTORE HSDS, VPXV_ENTITY en, VPXV_HOSTS hs
where
HSDS.DS_ID = DS.ID and
hs.HOSTID = hsds.HOST_ID and
en.id = hs.FARMID
group by en.name)T2
where T1.ClusterName = T2.ClusterName
Storage Pools @ Cluster Level:(Must remove duplicates)
==================================================
select en.NAME as ClusterName, ds.NAME , ds.STORAGE_URL,
ROUND((CAST(ds.capacity as float))/1024/1024/1024,2) as Total_Allocated_GB,
ROUND((CAST(ds.FREE_SPACE as float))/1024/1024/1024,2)as Free_Space_GB
from dbo.VPXV_DATASTORE DS, dbo.VPXV_HOST_DATASTORE HSDS, VPXV_ENTITY en, VPXV_HOSTS hs
where
HSDS.DS_ID = DS.ID and
hs.HOSTID = hsds.HOST_ID and
en.id = hs.FARMID
