Report: Count Operating System versions per collection

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.

OSCount

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

4 Comments

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.