COS
Expert
Expert

Querying the vCenter Database....

Jump to solution

This might be a little out of the box but, does anyone know how to query the vCenter DB and return the "Attributes" from within the "Annotations"?

There were custom fields added in the annotations but they do not come up when I query the DB. The one field in specific is called "Customer;". If I put something in the "Notes" portion then the data comes over from the SQL query, but nothing from our added fields.

Here's a basic query against the view if you want to try...

Select * From VPXV_VMS

Any ideas?

Thanks

0 Kudos
1 Solution

Accepted Solutions
DCSpooner
Enthusiast
Enthusiast

try a left join. if I remember correctly that should give you all VM from table v and then only the info form table f that are equal to table v

View solution in original post

0 Kudos
13 Replies
DCSpooner
Enthusiast
Enthusiast

check out these two tables VPX_FIELD_DEF and VPX_FIELD_VAL

these two table has the information on the custom annotations you are looking for.

peetz
Leadership
Leadership

PLease, please never query the vCenter database directly, but use the officially supported API interfaces to vCenter to query data like this.

For an easy example on how to query the VMs' notes field with PowerCLI see e.g. this post:

Backup and Restore the Notes Field in VMware | HiperLogic

- Andreas

Twitter: @VFrontDe, @ESXiPatches | https://esxi-patches.v-front.de | https://vibsdepot.v-front.de
0 Kudos
COS
Expert
Expert

I totally understand your concern but these queries are going to be run from a 2012 SQL Reporting server and I don't know if a SQL 2012 Report server can interface to powercli. I'm a SQL DBA and it's faster for me to run select queries (Note it's a NON DML query) and return results.

Thanks

0 Kudos
DCSpooner
Enthusiast
Enthusiast

i was just about to say the you might be using the query for SQL reporting or crystal reporting.

i just got back to my office.

0 Kudos
DCSpooner
Enthusiast
Enthusiast

did those two tables work?

0 Kudos
COS
Expert
Expert

yes......sort of. It gave the the values but the tables don't really have a relationship.

I want to get this all on one query and without using a #temp table.

Here's what I have so far but it's not accurate because not all the VM's are returned....

SELECT *

FROM         VPXV_VMS v

JOIN dbo.VPXV_VM_FIELDS f

ON v.VMID = f.VMID

WHERE [DESCRIPTION] NOT LIKE '%Template%'

AND FIELDID = 142

ORDER BY NAME ASC

If anyone has a working query please post. :smileysilly:

0 Kudos
DCSpooner
Enthusiast
Enthusiast

try a left join. if I remember correctly that should give you all VM from table v and then only the info form table f that are equal to table v

View solution in original post

0 Kudos
COS
Expert
Expert

That got most of the VM's but my filter for '%Template%' also filtered more that is was supposed to. Not because of anything in the where clause or the value '%Template%' but because we didn't remove the descriptions out of the VM's after spinning up from a template......lol

OOOPS!

:smileyblush:

I'll post the final query. The one above was simplified after some logic was applied to export the tables to a test DB.

0 Kudos
DCSpooner
Enthusiast
Enthusiast

good look forward seeing your final query

Smiley Happy

0 Kudos
COS
Expert
Expert

OK, here's my RAW query. I pulled out my additional logic. You can add your columns if you want but the ones in my query are specific to what I am looking for.

NOTE:

The more specific field or value I was looking for is labeled in the table "VPXV_VM_FIELDS" and the field is called "FIELDVALUE" but it's indexed value is "FIELDID" and that value is 142. That specific field has customer names.

SELECTv.NAME, v.DNS_NAME, v.IP_ADDRESS, v.MEM_SIZE_MB, v.NUM_VCPU, v.GUEST_OS, v.DESCRIPTION, v.ANNOTATION, v.VMID, f.FIELDVALUE
FROM    VPXV_VMS AS v LEFT OUTER JOIN
VPXV_VM_FIELDS AS f ON v.VMID = f.VMID AND f.FIELDID = 142

ORDER BY f.FIELDVALUE, v.NAME ASC

Next I want to extract the "Provisioned Storage" and include that and i'm done!

0 Kudos
odohertyd
Contributor
Contributor

Have you figures a way of extracting the storage provisioned?

Thanks

0 Kudos
Karthik26
Contributor
Contributor

This might help you...

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

0 Kudos
DBA_ONE
Contributor
Contributor

The VMDK section will show you the files related to OS volumes and how they are provisions and space used. The other section is meant to show the other files related to a VM like swap, etc.

with CTE_DriveDetail as (SELECT 'VMDK' as GroupName, 'XXXX' as Datacenter, VM.NAME as VMName, VM.VMID as VM_id

                        ,round(cast((cast(t4.file_size as bigint)/1048576) as float)/1024,0) as MinProvisionedSizeGB

                        ,(select sum(round(cast((cast(t4.file_size as bigint)/1048576) as float)/1024,0))

                            from [lisa].[VCDB].[dbo].[VPX_VDEVICE_FILE_BACKING_X] AS FBX

                            INNER JOIN [lisa].[VCDB].[dbo].[VPX_VDEVICE_FILE_BACKING] AS FB

                                ON FB.BACKING_ID = FBX.BACKING_ID

                            inner join [lisa].[VCDB].[dbo].[VPX_VM_FLE_FILE_INFO] t4

                                on FBX.VM_id=t4.VM_ID

                                and t4.vm_id=VM.VMID

                                and FB.hard_device_backing_file_name=replace(t4.name,'-flat','')

                                and t4.type<>'diskDescriptor')

                        as MaxProvisionedSizeGB

                        ,CASE WHEN FBX.DEV_BAC_THIN_PROVISIONED_FLG = 1 THEN 'Thin' ELSE 'Thick' END +'/'+ CASE WHEN FBX.EAGERLY_SCRUB = 1 THEN 'Eager' ELSE 'Lazy' END AS ProvisionType

                        ,VVD.DEVICE_INFO_LABEL AS device_info_label

                        ,replace(substring(substring(FB.hard_device_backing_file_name,20,100),charindex('/',substring(FB.hard_device_backing_file_name,20,100)),100),'/'+VM.NAME+'/','') as hard_device_backing_file_name

                        ,DS.NAME AS DSName

                        ,cast(cast(cast([FREE_SPACE] as float)/1048576/1024 as float) as decimal(10,2)) as DS_FreeSpace_GB

                        ,cast(cast([CAPACITY] as float)/1048576/1024 as float) as DS_Capacity_GB

                        ,t4.name as FileName

                        FROM [lisa].[VCDB].[dbo].[VPX_VDEVICE_FILE_BACKING_X] AS FBX

                            INNER JOIN [lisa].[VCDB].[dbo].[VPX_VDEVICE_FILE_BACKING] AS FB

                                ON FB.BACKING_ID = FBX.BACKING_ID

                            INNER JOIN [lisa].[VCDB].[dbo].[VPXV_VMS] AS VM

                                ON VM.VMID = FBX.VM_ID

                            INNER JOIN [lisa].[VCDB].[dbo].[VPXV_DATASTORE] AS DS

                                ON DS.ID = FB.DATASTORE_ID

                            INNER JOIN [lisa].[VCDB].[dbo].[VPX_VIRTUAL_DISK] AS VD

                                ON FB.VM_ID = VD.VM_ID

                                AND FB.UPDATE_KEY = VD.UPDATE_KEY

                            INNER JOIN [lisa].[VCDB].[dbo].[VPX_VIRTUAL_DEVICE] AS VVD

                                ON VVD.VDEVICE_ID = VD.VDEVICE_ID

                            inner join [lisa].[VCDB].[dbo].[VPX_VM_FLE_FILE_INFO] t4

                                on FBX.VM_id=t4.VM_ID

                                and FB.hard_device_backing_file_name=replace(t4.name,'-flat','')

                                and t4.type<>'diskDescriptor'

                        union

                        Select distinct 'Others' as GroupName, 'XXXX' as Datacenter, VM.NAME as VMName, VM.VMID as VM_id

                            ,round(cast((cast(t4.file_size as bigint)/1024) as float),0) as MinProvisionedSizeGB

                            ,(select sum(round(cast((cast(t4.file_size as bigint)/1048576/1024) as float),0))

                                from [lisa].[VCDB].[dbo].[VPXV_VMS] AS VM2 inner join [lisa].[VCDB].[dbo].[VPX_VM_FLE_FILE_INFO] t4

                                    on VM2.VMid=t4.VM_ID

                                    and VM2.VMid=VM.VMID

                                inner join [lisa].[VCDB].[dbo].[VPX_DS_ASSIGNMENT] VDA

                                    on VM2.VMid=VDA.entity_id

                                inner join[lisa].[VCDB].[dbo].[VPX_DATASTORE_INFO] VDI   

                                        on VDI.[ID]= VDA.ds_ID   

                                and t4.name not like '%flat.vmdk%'

                                and t4.type<>'diskDescriptor' and VDI.NAME not like '%BK-NR%'

                                ) as MaxProvisionedSizeGB

                            ,'N/A' as ProvisionType

                            ,t4.type as device_info_label

                            ,replace(substring(substring(t4.name,20,100),charindex('/',substring(t4.name,20,100)),100),'/'+VM.NAME+'/','') as hard_device_backing_file_name

                            ,VDI.NAME AS DSName

                            ,cast(cast(cast([FREE_SPACE] as float)/1048576/1024 as float) as decimal(10,2)) as DS_FreeSpace_GB

                            ,cast(cast([VMFS_CAPACITY] as float)/1048576/1024 as float) as DS_Capacity_GB

                            ,t4.name as FileName

                        from [lisa].[VCDB].[dbo].[VPXV_VMS] AS VM inner join [lisa].[VCDB].[dbo].[VPX_VM_FLE_FILE_INFO] t4

                            on VM.VMid=t4.VM_ID

                        inner join [lisa].[VCDB].[dbo].[VPX_DS_ASSIGNMENT] VDA

                            on VM.VMid=VDA.entity_id

                        inner join[lisa].[VCDB].[dbo].[VPX_DATASTORE_INFO] VDI   

                                on VDI.[ID]= VDA.ds_ID   

                        and t4.name not like '%flat.vmdk%'

                        and t4.type<>'diskDescriptor' and VDI.NAME not like '%BK-NR%')

select * from CTE_DriveDetail

0 Kudos