SCCM Patch Management Enterprise Compliancy Report

SCCM Patch Management Enterprise Compliancy Report ---originally available in http://myitforum.com/cs2/blogs/cstauffer/archive/2008/10/17/sccm-patch-management-enterprise-compliancy-report.aspx

As most of you know if you have read any of the reports that I have posted in the past, here at the Commonwealth we use collections to track agencies. This report will show you an overall status and then a breakdown of each agency. This is done by grabbing the parent collection and listing each agency.

**********************************************************

Note: You will need to change your ScopeID to match your location and the Collection ID in the last Select statement to your parent Collection.

**********************************************************

--AuthListID=ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B
--CollID=SMS00001

declare @CI_ID int; select @CI_ID=CI_ID from v_ConfigurationItems where CIType_ID=9 and CI_UniqueID='ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B'

declare @CollCount int, @NumClients int; select @CollCount = count(*), @NumClients=isnull(sum(cast(IsClient as int)), 0) from v_ClientCollectionMembers ccm where ccm.CollectionID='SMS00001'

Select
    CollectionName=vc.Name,
    NumberInCollection=@CollCount,
    NonClients=@CollCount-@NumClients, 
    PComputers=convert(numeric(5,2), (@CollCount-@NumClients)*100.00 / isnull(nullif(@CollCount, 0), 1))
from v_Collection vc
where vc.CollectionID='SMS00001'

SELECT   v_Collection.Name
, sn.StateName AS Status, COUNT(*) AS NumberOfComputers
, CONVERT(numeric(5, 2)
, ISNULL(COUNT(*), 0)* 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers
, 'ScopeId_8BF42CAA-F2A7-4063-A86D-C427EAB89450/AuthList_DC329234-6F0F-4256-879B-FBA1E43A2F0B' AS AuthListID

FROM         v_ClientCollectionMembers AS cm INNER JOIN
                      v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN
                      v_Collection ON cm.CollectionID = v_Collection.CollectionID LEFT OUTER JOIN
                      v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0)
WHERE     (cm.CollectionID = 'SMS00001')
GROUP BY sn.StateName, v_Collection.Name
ORDER BY NumberOfComputers DESC

SELECT     v_Collection.Name, sn.StateName AS Status, COUNT(*) AS NumberOfComputers, CONVERT(numeric(5, 2), ISNULL(COUNT(*), 0)
                      * 100.00 / ISNULL(NULLIF (@CollCount, 0), 1)) AS PComputers
FROM         v_ClientCollectionMembers AS cm INNER JOIN
                      v_UpdateListStatus_Live AS cs ON cs.CI_ID = @CI_ID AND cs.ResourceID = cm.ResourceID INNER JOIN
                      v_Collection ON cm.CollectionID = v_Collection.CollectionID INNER JOIN
                      v_StateNames AS sn ON sn.TopicType = 300 AND sn.StateID = ISNULL(cs.Status, 0) AND cm.CollectionID IN
                          (SELECT     subCollectionID
                            FROM          v_CollectToSubCollect
                            WHERE      (parentCollectionID = 'PA100043'))
GROUP BY sn.StateName, v_Collection.Name
ORDER BY v_Collection.Name DESC

-------------------
Thanks,
http://paddymaddy.blogspot.com/