List collection name and number of resources by OS

For full details see: https://social.technet.microsoft.com/Forums/en-US/configmgrreporting/thread/370ceff1-0f54-46f3-8e8e-5459069125e4

SELECT 

  C.Name,

  OS.Caption0 as 'Operating System',

  OS.CSDVersion0 as 'Service Pack', 

  COUNT(*) AS 'Count'

FROM

  dbo.v_R_System R

  join dbo.v_GS_OPERATING_SYSTEM OS on OS.Resourceid = R.Resourceid 

  join dbo.v_FullCollectionMembership FCM on FCM.Resourceid = R.Resourceid 

  join dbo.v_Collection C on C.CollectionID = FCM.CollectionID

GROUP BY 

  C.Name,

  OS.Caption0,

  OS.CSDVersion0 

ORDER BY 

  C.Name,

  OS.Caption0,

  OS.CSDVersion0