Getting VM property from SCOM 2007 R2 DW database

I've been looking into OM DW database to get VMM information.

1. vMangedEntity View
All servers, hard disks, clusters is exists on vMangedEntity.
This is starting point of what I'd like to look for. all discovered objects' information is managed in the view -  discovered objects are called as entity in OM.
select * from dbo.vManagedEntity a

2. vManagedEntityType View.
All entities have at least a certain type. that is vManagedEntityType. 
EntityType is kind of group information. all VM are grouped in the type like the following:
select *
from dbo.vManagedEntity a
inner join dbo.vManagedEntityType b on (a.ManagedEntityTypeRowId = b.ManagedEntityTypeRowId)
where ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.2008.VritualMachine'

These kinds of types was defined in Management Pack. After OM installed, I imported SCVMM 2008 MP and also enabled PRO beween OM and VMM.

Let's find other types related to VMM using LIKE keyword.
select * from vManagedEntityType
where ManagedEntityTypeSystemName like 'Microsoft.SystemCenter.VirtualMachineManager%'

This results have two categories.
First is that ManagedEntityTypeSystemName begins with 'Microsoft.SystemCenter.VirtualMachineManager.2008' which contains into basic VMM MP. The other is that the name begins with 'Microsoft.SystemCenter.VirtualMachineManager.PrO' which be awared after enabled PRO.

In my exprience, VMM R2 MP was automatically imported while PRO setting. It's good to set PRO if you don't want to use PRO. VMM R2 MP is the latest one.

3. How can I get properties of a certain Entity.
Let's show an example of a virtual machine (VM). a VM has a lot of properties like name, VM guid, how many virtual processor are allocated and so on.

there are two table about this. those are vManagedEntityTypeProperty and vManagedEntityPropertySet. description of these are simple - it is easy to guess from each name. the former has property definitions and the latter has properties' values.

Here's my query.

select a.ManagedEntityRowId,
           PropertyValue ComputerName
from dbo.vManagedEntityPropertySet a
inner join dbo.vManagedEntity b on b.ManagedEntityRowId=a.ManagedEntityRowId
inner join dbo.vManagedEntityTypeProperty c on c.PropertyGuid=a.PropertyGuid
inner join dbo.vManagedEntityType d on d.ManagedEntityTypeRowId=b.ManagedEntityTypeRowId
where ManagedEntityTypeSystemName like 'Microsoft.SystemCenter.VirtualMachineManager.2008.VirtualMachine%'
and PropertyDefaultName = 'Computer Name'
and a.ManagedEntityPropertyRowId = (
                                                                   select MAX(ManagedEntityPropertyRowId)
                                                                   from dbo.vManagedEntityPropertySet e
                                                                  where a.ManagedEntityPropertyRowId =          
                                                                             e.ManagedEntityPropertyRowId
                                                                   )
and     b.ManagedEntityRowId = 'Entity RowID What you want to get'
group by a.ManagedEntityRowId,  PropertyValue

Keeps in mind that vManagedEntityPropertySet table has property's change history. so I had to select a record by using MAX keyword.

4. EXCEPT and how to remove duplicated data bewteen 'Hyper-V host' and 'Windows OS'
I used a EXCEPT keyword in my query for the first time. this is used that when I run two queries which I would eliminate duplicated record if I have the same record in the second query. If you're familiar with Oracle, it's same with the keyword MINUS. See online help from MSDN

I wanted to make the query that:

• Processor Perf counter for Hyper-V Host: "Hyper-V Hypervisor Logical Processor(_Total)\% Total Run Time"
• Percessor Perf counter for Windows OS : % Processor Time
• Don't want use NOT IN or NOT for bad query performance.
• If a Hyper-V is not managed host on VMM, get Perf data from % Processor Time

In OM DW,
- All Windows servers are included in 'Microsoft.Windows.OperatingSystem'  (even Hyper-V host)
- Only Hyper-V hosts are in 'VirtualMachineManager.2008.ManagedHost'

-- Perf counter for % Processor Time
select a.Path
from dbo.vManagedEntity a
inner join dbo.vManagedEntityType b on (a.ManagedEntityTypeRowId = b.ManagedEntityTypeRowId)
where ManagedEntityTypeSystemName = 'Microsoft.Windows.OperatingSystem'
EXCEPT
-- Per count for Hyper-V Logical Processor
select a.Path
from dbo.vManagedEntity a
inner join dbo.vManagedEntityType b on (a.ManagedEntityTypeRowId = b.ManagedEntityTypeRowId)
where ManagedEntityTypeSystemName = 'Microsoft.SystemCenter.VirtualMachineManager.2008.ManagedHost'

This result is the records which are not duplicated.