SQL query to get client count with status active obsolete missing for collections in tabular column

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