Display the number of objects in group

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

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

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

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

[email protected] table(BaseManagedEntityId UNIQUEIDENTIFIER);
[email protected] UNIQUEIDENTIFIER

–Return all computers entities
INSERT [email protected] (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
[email protected] = (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 [email protected]
–AND RM.ContainedEntityId IN (SELECT
— BaseManagedEntityId
— [email protected] )

–The fast way fast if you don’t have nested groups.
SELECT
COUNT(*)
FROM RelationshipView
WHERE SourceEntityId [email protected]
AND TargetEntityId IN (SELECT
BaseManagedEntityId
[email protected] )
2 Likes