Display the number of objects in group

Dashboard Server
1

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.

1 Like
2

It should be - use the SQL tile for this. Check Kevin’s blog here for a query that may do it

https://blogs.technet.microsoft.com/kevinholman/2016/11/11/scom-sql-queries/

 

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
2 Likes
3

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.

2 Likes
4

Hi,

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 )
2 Likes