This SQL Query will count the total number of client machine resources by their Network Operating System (NOS) name that are in a specified collection.
Note: If you would rather not type in the SMS consoles collection name you can change the line that reads: Where Col.Name = 'All Windows NT Systems' to: Where COL.CollectionId = 'SMS000CS'.
SQL Query:
Select
SD.Operating_System_Name_and0 NOS,
Count (SD.Operating_System_Name_and0) 'Total',
COL.CollectionId,
COL.Name
From v_R_System SD
Join v_FullCollectionMembership FCM on SD.ResourceID = FCM.ResourceID
Join v_Collection COL on FCM.CollectionID = COL.CollectionID
Where Col.Name = 'All Windows NT Systems'
Group By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name
Order By SD.Operating_System_Name_and0, COL.CollectionId, COL.Name
No Comments