Report: Count Operating System versions per collection - CCMEXEC.COM

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'


FROM v_GS_OPERATING_SYSTEM
join v_FullCollectionMembership FCM on FCM.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
Where FCM.CollectionID = @varcollection
Group by CSDVersion0,Caption0
Order by CSDVersion0,Caption0
SELECT Caption0 as 'Operating System',CSDVersion0 as 'Service Pack', Count(Caption0) AS 'Number',@varcollection as 'CollectionID'
FROM v_GS_OPERATING_SYSTEM
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:

begin

if (@__filterwildcard = '')

select distinct CollectionID, Name from v_Collection order by Name

else

select distinct CollectionID, Name from v_Collection

WHERE CollectionID like @__filterwildcard

order by Name

end