Is it possible to return the number of objects that are in a group, as I would like to create a text box to display the number of computers that a contained in the SCOM group. Then display this value in a text box. Basically the z value that is displayed at the bottom when you have x of z when you limit the number of objects to display.
You should be aware of, this suggested queries are not accurate, since, the group may contain different types of entities, for ex, group my contain computer type entity and sql engine type entity and if you will use those queries you result will be number of all types of entities and not only comuters.
So, you need to count only computers use this query
DECLARE@ComputerEntitiesIds table(BaseManagedEntityId UNIQUEIDENTIFIER); DECLARE@GroupId UNIQUEIDENTIFIER –Return all computers entities INSERT INTO@ComputerEntitiesIds (BaseManagedEntityId) SELECT bme.BaseManagedEntityId FROM dbo.[DerivedManagedTypes] JOIN ManagedType mt ON mt.ManagedTypeId = DerivedTypeId JOIN BaseManagedEntity bme ON bme.BaseManagedTypeId = mt.ManagedTypeId — return only ‘System.Computer’ DerivedManagedTypes WHERE BaseTypeId = ‘B4A14FFD-52C8-064F-C936-67616C245B35’ –GroupID SET@GroupId = (SELECT bme.BaseManagedEntityId FROM dbo.[DerivedManagedTypes] JOIN ManagedType mt ON mt.ManagedTypeId = DerivedTypeId JOIN BaseManagedEntity bme ON bme.BaseManagedTypeId = mt.ManagedTypeId WHERE BaseTypeId = ‘D0B32736-5344-2FCC-74B3-F72DC64EF572’ AND bme.DisplayName = ‘All Windows Computers’) — The recursive way if you that inside the group you have anther group of servers –SELECT — count(*) –FROM dbo.[RecursiveMembership] RM –WHERE RM.ContainerEntityId =@GroupId –AND RM.ContainedEntityId IN (SELECT — BaseManagedEntityId — FROM@ComputerEntitiesIds ) –The fast way fast if you don’t have nested groups. SELECT COUNT(*) FROM RelationshipView WHERE SourceEntityId =@GroupId AND TargetEntityId IN (SELECT BaseManagedEntityId FROM@ComputerEntitiesIds )
This will give you the count of members in the group:
select count(SourceObjectDisplayName) AS 'Number of members' from RelationshipGenericView where isDeleted=0 AND SourceObjectDisplayName like 'Replace with Group Name'
One caveat though… If there is subgroups in the group they will count as a member of the group. And you wont get the count on the members of those subgroups.
It should be – use the SQL tile for this. Check Kevin’s blog here for a query that may do it
I saw one snippet on his page that you may be able to modify to count the results (not near my system so I can’t test sorry) :
--To find all members of a given group (change the group name below): select TargetObjectDisplayName as 'Group Members' from RelationshipGenericView where isDeleted=0 AND SourceObjectDisplayName = 'All Windows Computers' ORDER BY TargetObjectDisplayName