Quick blog post on how to get client count with active obsolete and missing status for collections in a nice tabular column.
I used 3 SQL views in this query V_r_system ,v_FullCollectionMembership_valid and v_Collection with sum and case statements.
You can use this SQL code in report creation with collection prompt and also create linked reports.
select coll.Name [Collection Name],fcm.CollectionID,count(sys.name0) [Total clients],
SUM (CASE WHEN sys.Active0 = 1 THEN 1 ELSE 0 END) AS 'Active Clients',
SUM (CASE WHEN sys.Obsolete0 = 1 THEN 1 ELSE 0 END) AS 'Obsolete Clients',
SUM(CASE WHEN sys.Client0 is NULL THEN 1 ELSE 0 END) AS 'Client Missing'
from v_r_system sys
inner join v_FullCollectionMembership_Valid fcm on fcm.ResourceID=sys.ResourceID
inner join v_Collection coll on coll.CollectionID=fcm.CollectionID
where fcm.CollectionID in ('PS1000DE','PS1000DF')
Group by fcm.CollectionID,coll.Name
SQL output:
For more information about SCCM client health dashboard ,refer https://gallery.technet.microsoft.com/SCCM-Configmgr-2012-SSRS-2863c240
3 Comments
Thanks Eswar- that could be a useful one. Just one small thing - you have a 'c' missing from the 'Obsolete Clients' column title.
updated the query but not the screenshot 🙂
Regards,
Eswar
Hi - Can in handy when, I was about to run a report to get missing clients before Windows 10 in place upgrade. Thanks for sharing the query.