OK, I need to get a list of all VM's in all Datacenters in vCenter and the folder they are in....
Here's my query
WITH x AS
(
SELECT DISTINCT name, id
FROM VPX_ENTITY
)
Select x.Name AS ParentFolderName, ve.NAME
From VPX_ENTITY ve
JOIN x ON x.id = ve.Parent_Id
WHERE TYPE_ID = 0 AND PARENT_ID <> 1144 AND x.ID <> 196
ORDER By ParentFolderName, ve.NAME ASC
That gives me a good list of ALL VM's but in their Parent Folder. Parent Folder is really the final folder it is in and doesn't show the folder the "Parent Folder" is in and what folder that is in and ultimately doesn't show what "Datacenter" they are in.
Here whet the folder tree looks like in vcenter...
Datacenter Earth
SQL
SQ2005
SQ2008
Yomama
SQ2012
Oracle
ORA9i
ORA10g
Datacenter Mars
IIS
Websphere
Sharepoint
SP2007
SP2010
Jomama
So, the output I am looking for, for the SQL VM called Yomama should look like this as the sql query output...
"Datacenter Earth" \ SQL \ SQL2008 \ Yomama
The output for the Sharepoint VM called Jomama will look like this....
"Datacenter Mars" \ Sharepoint \ SP2010 \ Jomama
Anyone have any idea on how to get the absolute path of a VM like this?
Thanks
Hi,
Why do you do not use powercli to do it ???
Get-VM Function
Check powercli link vSphere PowerCLI Documentation
Discussion moved from VMware ESXi 5 to VMware vCenter™
Because I want to put it in a SQL Report with a Subscription. There are other features I want to include in the query after I get the absolute path.
Hello,
maybe using powercli to get the information:
Re: Trying Get Folder Path for VM in VC...
Powershell: List all VMs with Datastore | TechColumnist
and then import the information inside the ms sql server:
http://www.virtu-al.net/2009/11/23/powercli-to-sql-databases/
Hope this helps you
Best regards,
Pablo
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
Someone asked why we didnt' use powershell, in my case and i need the same or similar info is that the Vcenter will not run properly but the DB is still intact. so i do a recover of the DB to a new name and then run queries on it to get details of the environment to put it back together since the DB was not one i could reconnect to after the host failure... but i want to know the 10 datacenters that our users had created on it, and which physical hosts were populated to each datacenter...