This Configuration Manager 2007 report will display a Operating System count per collection, including Service Pack level as displayed below. It is very useful to get an overview of how many computers with a specific OS and Service Pack there is in the environment.
When implementing it, create a prompt for the report called “varcollection” which is the prompt for a CollectionID.
Here is the query:
SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', Count(Caption0) AS 'Number',@varcollection as 'CollectionID'
join v_FullCollectionMembership FCM on FCM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
Where FCM.CollectionID = @varcollection
Group by CSDVersion0,Caption0
Order by CSDVersion0,Caption0
Prompt query for the CollectionID prompt:
if (@__filterwildcard = '')
select distinct CollectionID, Name from v_Collection order by Name
select distinct CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name