Getting VM property from SCOM 2007 R2 DW database - 2

Following by the last post, I'm going to continue to get VM propertes on SCOM DW.

1. vManagedEntityTypeProperty in depth.
New Fact which I discovered today is that VM property info is not associatd only the "Microsoft.SystemCenter.VirtualMachineManager.2008.VritualMachine" EntityType in vManagedEntityTypeProperty view.












Its properties come from various EntityType.

As you can above result windows in SQL 2008 management studio, this comes from "All Entities, All Windows Computers", "Microsoft.SystemCenter.VirtualMachineManager.2008.Base.VirtualMachine",

"Mcrosoft.SystemCenter.VirtualMachineManager.2008.VirtualMachine" and "Microsoft.Virtualization.2008.Base.VirtualMachine".

Therefore, We can't find all VM properties from Microsoft.SystemCenter.VirtualMachineManager.2008.VritualMachine type. the related column beween vManagedEntityTypeProperty and vManagedEntityPropertySet is ManagedEntityTypeGuid.

In Conclusion,
(1) Find the ManagedEntity and Its Entitytype you want.
(2) Join  ManagedEntityTypeProperty to (1) with column ManagedEntityRowId.
(3) Join  ManagedEntityPropertySet to (2) with colum ManagedEntityTypeGuid.
      Don't forget using MAX keywork to the latest data.

2. Pick the property from vManagedEntityProperty using XML
The diffence between vManagedEntityProperty and vManagedEntityPropertySet is return propertes as XML or ResultSet.
If we're using vManagedEntityProperty, it can select its data as follwing:


"AppVMManagement" is returned for the above XML query.