/*************************************************************************************/
/*     Query of the SCOM OperationsManager database for Total Number of Servers per OS   */
/*     Author: Stefan Stranger, Microsoft Services                                   */ 
/*                                                                                   */
/*************************************************************************************/
Select 
Count(vme2.DisplayName)As "Total Servers", vme2.DisplayName 
from vManagedEntity 
join vRelationship on vRelationship.TargetManagedEntityRowId=vManagedEntity.ManagedEntityRowId 
join vManagedEntity vme2 on vme2.ManagedEntityRowId=vRelationship.SourceManagedEntityRowId 
where vme2.DisplayName='Windows Server 2000 Computer Group' 
or vme2.DisplayName='Windows Server 2003 Computer Group' 
or vme2.DisplayName='Windows Server 2008 Computer Group' 
Group by vme2.DisplayName