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
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
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.
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
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
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.
did those two tables work?
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:
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
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.
good look forward seeing your final query
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.
SELECT | v.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!
Have you figures a way of extracting the storage provisioned?
Thanks
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
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