COS
Expert
Expert

querying the vCenter DB....

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

0 Kudos
5 Replies
MauroBonder
Leadership
Leadership

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™

*Please, don't forget the awarding points for "helpful" and/or "correct" answers. *Por favor, não esqueça de atribuir os pontos se a resposta foi útil ou resolveu o problema.* Thank you/Obrigado
0 Kudos
COS
Expert
Expert

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.

0 Kudos
Borja_Mari
Virtuoso
Virtuoso

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 Smiley Happy

Best regards,

Pablo

------------------------------------------------------------------------------------------------- PLEASE CONSIDER AWARDING any HELPFUL or CORRECT reply. Thanks!! Por favor CONSIDERA PREMIAR cualquier respuesta ÚTIL o CORRECTA . ¡¡Muchas gracias!! VCP3, VCP4, VCP5-DCV (VCP550), vExpert 2010, 2014 BLOG: http://communities.vmware.com/blogs/VirtuallyAnITNoob
0 Kudos
Karthik26
Contributor
Contributor

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

0 Kudos
vmwaredownload
Enthusiast
Enthusiast

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...

0 Kudos